[Developer's community]

SQL Server Database Export/Import operation in MS Azure

 

10 August 2017
The beauty of Azure is that it has multiple ways to do the same thing. Imagine the scenario;  y

The beauty of Azure is that it has multiple ways to do the same thing. Imagine the scenario;  you need to copy the database between subscriptions. Considering SQL Server Import/Export procedure, you can do that either from the UI, or use command line tools (like SQLPackage).

The way it works with Azure UI, you go to the SQL Server database you want to export, and you have an option available (circled in red below). It will export the database to the selected Blob storage (you need to have storage account created prior this operation in the same subscription).

In the case of the same subscription but different SQL Server instances (or if your Azure subscriptions belong to the same tenant), you can Import this database (the actual *.bacpac file) using the same storage account, as it will be visible for all of them. If these are two completely different subscriptions, but you still need to Export/Import this database, you may need to create a storage account in the second subscription and import it from there. For the purpose, go to the SQL Server instance, and you will see the “Import database” option as highlighted below:

To import database, you need to select the file (*.bacpac) from the storage account and pricing tier (along with collation, admin name, and password).

TIP: When you download the database backup file (*.bacpac) from the storage account, it will appear as a zipped archive. No worries about that, as the bacpac file is a zip archive in reality.

There is nothing wrong with this approach, but in this case, the import procedure takes quite long time (as for me) for small databases and much longer for larger ones. The import process is hidden from the user, and the only message you will receive is that “Request is submitted to import database”:

You can control it to some extent by clicking “Operations” tile on the same page below (that is called “Import/Export history”) but it doesn’t update in real time. The error messages (if you would have any) are not that informative as well:

If you need to perform the same task quicker, automate it in the script (using PS or Azure CLI), or add more visibility into the process, the right way to go is to use SQLPackage command-line utility.

As stated in the documentation, to Export/Import using SQLPackage utility, you can use the relevant parameters and properties. The utility should be installed on your machine if you used SQL Server Management Studio (the latest one) or Visual Studio Tools for SQL Server. You can also install it directly from the MS website here.

The command to Export database I have used is quite simple:

Code:

SqlPackage.exe /a:Export /tf:<filename>.bacpac /scs:"Data Source=<server name>.database.windows.net;Initial Catalog=<database name to export>; User id=<user id>; Password=<password>"

TIP: You can find the SQLPackage util using the following path: “C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin”. If you will be using the util from there, the exported file will be dropped into the same directory. The better way is to add SQLPackage.exe to Path in the Environment variables, so you can call anywhere using the command line.

Import operation is as simple as export. Please find the code below:

Code:

SqlPackage.exe /a:import /tcs:"Data Source=<destination server name>.database.windows.net;Initial Catalog=<db name>;User Id=<server admin>;Password=<admin password>" /sf:<file name>.bacpac /p:DatabaseEdition=Standard /p:DatabaseServiceObjective=S1

It is worth mentioning that the *.bacpac will be picked from the same directory where SQLPackage.exe lies (unless you used the tip above). “User Id” and “Password” should be SQL Server admin’s credentials in Azure. Database edition corresponds to SQL Server editions (the full list of possible values: Basic|Standard|Premium|Default), the same parameters can be found in the documentation mentioned above. Database Service Objective corresponds to SQL Server pricing tiers. Possible values are: Basic, S0-S3 (Standard tier), P1/P2/P4/P6/P11/P15 (that belong to Premium tier), PRS1-2/4/6 (IO and compute-intensive instances, still in preview).

Like it was mentioned before, this way is quicker and gives you more control and visibility over the process. The error messages are more informative in this case as well:

So, using these two simple commands, you can Export/Import database for the same or different Azure subscription without the overhead and automate the process if necessary, or use SQLPackage as part of your script for some other tasks.

 

Creating a Service Principal for VSTS endpoint

 

29 June 2017
Imagine a situation where you have to manage multiple Azure subscriptions (that belong to different

Imagine a situation where you have to manage multiple Azure subscriptions (that belong to different tenants) not only for your organization but also for your company’s clients. In software development process, you can host VSTS (Visual Studio Team Services) on your organization’s Azure account and deploy to customers’ subscription (which is quite a wide-spread use case). Subscription management in Azure designed in such a way you cannot have two similar products assigned to you. For instance, you cannot have two Pay-as-you-go subscriptions assigned to your account (or two BizSpark or Enterprise subscriptions, etc.) The resources from different subscriptions can be assigned to you, but you can’t manage them directly from your account, as they belong to different tenants. What if you need to build a delivery pipeline in VSTS and don’t see a target subscription (to deploy your application to) on the list? Well, it could be a problem, but before calling Microsoft’s support, let’s see what we can do with it.

Fortunately, you can create custom service connection, by clicking ‘Manage’ next to Azur subscriptions drop-down (see the screenshot above). When you go to ‘New Service Endpoint’ -> ‘Azure Resource Manager,' you will see a familiar list of subscriptions assigned to your account:

The dialog suggests: ‘If your subscription is not listed above, or your account is not backed by Azure Active Directory or to specify an existing Service Principal, use the full version of the endpoint dialog.’ See the screenshot below:

The dialogue seems complicated (at a glance) with many fields to fill-out. Let’s see what we can do:

Let's start filling this form out :)

  • Connection name is the simplest – put any suitable name in here
  • Environment – leave a default value (unless you’re in China, Germany or US Gov)
  • Subscription ID – the id of the target subscription (you deploy to). Go to Azure -> Subscriptions, select and copy subscription ID from there. Or, you can use Azure CLI and enter:
azure login
azure account list

Copy the ID:

  • Subscription Name – opposite to Id (see the previous step)
  • Tenant ID – Go to Azure -> Active Directory -> Properties blade -> Copy ‘Directory ID’ from there. Alternatively, as long as you’re already logged in in the Azure CLI, you can use the following command:
azure account show
  • Service Principal Client ID – is a bit more complicated. You won’t find this value anywhere. The only way is to create a Service Principal that will be assigned to this endpoint. To do so, we need to use Azure CLI (or PowerShell):
azure ad sp create -n <app> -p <password>

Where <app> is random app name (doesn’t really matter) and <password> - is a password for the Service Principal (and Service Principal Key at the same time). This command will create an SP:

The value you need is ‘Service Principal Names.' The last step is to assign Service Principal to a Role. Using Service Principal Name, execute the following the CLI command to assign SP to a Contributor role:

azure role assignment create --spn 990ffeff-0016-4535-809c-79db18336db4 -o Contributor

As long as done with this, click ‘Verify Connection’ link on the form and once it verified, you will be able to use this Endpoint to connect to the client’s subscription.