{"id":175,"date":"2017-06-27T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition"},"modified":"2026-03-18T21:52:58","modified_gmt":"2026-03-18T21:52:58","slug":"launch-your-database-into-azure-sql-database-bacpac-edition","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2017\/06\/27\/launch-your-database-into-azure-sql-database-bacpac-edition\/","title":{"rendered":"Launch your database into Azure SQL Database \u2013 bacpac edition &#8211; SQL Hammer"},"content":{"rendered":"<p>You have decided to migrate our on-premise SQL Server database into <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/sql-database\/?WT.srch=1&#038;WT.mc_id=AID559462__SEM_8oC1Up3k&#038;utm_source=Google&#038;utm_medium=CPC&#038;utm_term=sql%20azure%20database&#038;utm_campaign=Data_Management&#038;gclid=CjwKEAjwppPKBRCGwrSpqK7Y5jcSJACHYbWYL047j7y1-zmf2Ks3JzHaaK1jinxAVrTgCpRpRtj_WxoC4oPw_wcB\">Azure SQL Database<\/a>. How do you do that? The first thought that comes to mind is a simple backup from on-premise and restore to Azure SQL Database. Unfortunately, that is not supported by Microsoft. Instead, there are three supported methods of migrating into Microsoft\u2019s Platform as a Service (PaaS) offering.<\/p>\n<ul>\n<li>Export and restore from a bacpac file (data and schema).<\/li>\n<\/ul>\n<ul>\n<li>Export and deploy a dacpac file (schema only) and then ETL your data.<\/li>\n<\/ul>\n<ul>\n<li>Transactional Replication<\/li>\n<\/ul>\n<p>This post will cover method #1, exporting and restoring from a bacpac file.<\/p>\n<h2>Migrating to Azure SQL Database<\/h2>\n<p>The result of this method is that a <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/data-tier-applications#bacpac\">bacpac<\/a> file will live on <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/storage\/blobs\/\">Azure Blob Storage<\/a> and be imported into a logical server which will house your new Azure SQL Database. There are three paths to follow, however. All three will be covered in this post. I have sorted them from most GUI to least GUI.<\/p>\n<h2>Tools and versions<\/h2>\n<p>This method focuses on a feature of <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms\">SQL Server Management Studio<\/a> (SSMS) and the <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=53013\">DAC framework<\/a>. For the demonstration, SSMS v17.0 and the DAC framework packaged with SQL Server vNext (2017) will be used. Also, it is a SQL Server vNext database being exported. These versions, however are not the minimum requirements.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/wwi2016_skinny\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=360%2C203&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/wwi2016_skinny.jpg?resize=360%2C203&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>The DAC framework versions prior to 2016 can export databases going back to SQL Server 2000. The 2016+ versions of the DAC framework support SQL Server 2005+. I expect this type of rolling deprecation of support to continue as the framework continues into the future.<\/p>\n<p>The first version of SSMS which supported exporting bacpacs was packaged with SQL Server 2008 R2. While that version technically supports some of these methods, there are few reasons not to use the latest version of SSMS. Of the reasons that do exist, my recommendation is to use the latest version for all functions possible, including these functions, and only use the legacy version for your specific need.<\/p>\n<h2>Deploy Database to Microsoft Azure SQL Database<\/h2>\n<p>If only a few databases need to migrate to Azure SQL Database, this method is the most convenient. It is the, \u201cdo it all in one go,\u201d method.<\/p>\n<h3>Building the SQL Server<\/h3>\n<p>With Azure SQL Database, you do not have a server, like you do with Infrastructure as a Service (IaaS). However, you do need a <em>logical<\/em> server for you to connect to and for your database(s) to be attached to.<\/p>\n<p>Log into the <a href=\"https:\/\/portal.azure.com\/\">Azure portal<\/a>. Click the <em>New<\/em> button on the left navigation bar. Search <em>SQL Server<\/em> and select <em>SQL server (logical server)<\/em>. Click <em>Create<\/em> one last time to bring up the creation blade.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/sql-server-logical\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C208&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/sql-server-logical.jpg?resize=645%2C208&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>The create server blade will now be open. Fill out the sections as follows.<\/p>\n<ul>\n<li>The server name must be unique and is the name that you will specify in all Azure SQL Database connections.<\/li>\n<\/ul>\n<ul>\n<li>A SQL authentication, sysadmin, user with a strong password is required for all Azure SQL Databases. Windows authentication only is not an option.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-aad-authentication\">Azure Active Directory<\/a> access can be configured but that is out of scope for this post.<\/p>\n<ul>\n<li>Select the necessary subscription. Typically, organizations will have more than one and you may have access to more than one. Make sure to pick the correct one because it will likely affect how your organization deals with accounting.<\/li>\n<\/ul>\n<ul>\n<li>A resource group is a container to group together related Azure resources. <em>Create new<\/em> works, if you do not have an existing group to assign this server to. I had already created a resource group, though, so I selected <em>Use existing<\/em>.<\/li>\n<\/ul>\n<ul>\n<li>Pay careful attention to the <em>Location.<\/em> There is a number of ways that Microsoft makes money from Azure. One of them is data egress (data leaving a particular Azure data center). If you end up with multiple Azure resources in different Azure regions, you might end up paying more than necessary because all network traffic internal to Azure\u2019s data center is free.<\/li>\n<\/ul>\n<ul>\n<li>Click <em>Create<\/em>.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/create-sql-server-logical\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=468%2C890&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/create-sql-server-logical.jpg?resize=468%2C890&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>It takes about five minutes for the server to build. Next the client IP address will need to be whitelisted to connect to it.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/firewall-1\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C337&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i2.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/firewall-1.jpg?resize=645%2C337&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<ul>\n<li>Select the firewall page when scoped to your SQL Server.<\/li>\n<\/ul>\n<ul>\n<li>I typically set <em>allow access to Azure services<\/em> to <em>OFF<\/em> by default and then only turn it back on, if needed.<\/li>\n<\/ul>\n<ul>\n<li>This shows the public IP address of the network you are using.<\/li>\n<\/ul>\n<ul>\n<li>Create a firewall rule which allows access to the IP address or range that you need.<\/li>\n<\/ul>\n<ul>\n<li>Do not forget to click <em>Save<\/em>.<\/li>\n<\/ul>\n<h3>Exporting the bacpac via SSMS wizard<\/h3>\n<p>Right-click on the database you want to export and then select <em>Tasks<\/em>, followed by <em>Deploy Database to Microsoft Azure SQL Database<\/em>.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/deploy-to-sql-db\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C486&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i2.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/deploy-to-sql-db.jpg?resize=645%2C486&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>The wizard has three major areas to populate.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/deploy-wizard-1\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/deploy-wizard-1.jpg?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>3. Specify a temporary location to write the bacpac file. Make sure there is enough disk space to hold the entire database uncompressed.<\/p>\n<p>2. Select your pricing tier. What tier to choose is out of scope for this post but it is important to know that the pricing tier will impact the speed of the bacpac import. If you have a large database, it would be wise to choose a high pricing tier for the import and then reduce the tier after it is complete to meet the throughput needs.<\/p>\n<p>1. The connection string for the logical SQL Server is <servername>.database.windows.net using port 1433. This can also be found in the properties page of the logical SQL Server in the Azure portal, as well.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/server-name-1\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C563&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/server-name-1.png?resize=645%2C563&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/deploy-wizard-2\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=596%2C394&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/deploy-wizard-2.jpg?resize=596%2C394&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>Completing the process just requires clicking <em>Next<\/em>, <em>Finish<\/em>, and waiting.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/deploy-wizard-3\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/deploy-wizard-3.jpg?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<h2>Export Data-tier Application<\/h2>\n<p>It is time to move on to the second method of exporting bacpac files. This method is similar to the first method. A bacpac file is exported to a temporary local directory. Then it is uploaded directly to Azure.<\/p>\n<p>It is more work than the first method but it is useful for organizations with separation of duties within their Azure subscriptions. For example, a non-privileged user might have access to export and upload a bacpac file but need a DBA to import it into the correct SQL Server and set the pricing tier.<\/p>\n<h3>Create a storage account<\/h3>\n<p>Similar to creating a SQL Server, use the <em>New<\/em> button to open the new resource blade and then search for <em>storage<\/em>. Select <em>Storage account \u2013 blob, file, table, queue<\/em> and then click <em>Create<\/em>.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/storage-account\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C398&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i2.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/storage-account.jpg?resize=645%2C398&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>The create storage account blade will now be open. Fill out the sections as follows.<\/p>\n<ul>\n<li>The name of the storage account is globally unique and will be part of your URLs later in this post.<\/li>\n<\/ul>\n<ul>\n<li>Always select <em>Resource manager<\/em>. Microsoft is working away from the classic portal and resource manager should always be preferred.<\/li>\n<\/ul>\n<ul>\n<li>Select <em>General purpose<\/em> storage account type. It is the only supported option for this process.<\/li>\n<\/ul>\n<ul>\n<li>Select <em>Standard<\/em> Premium storage is not supported for this method.<\/li>\n<\/ul>\n<ul>\n<li>For a bacpac export, you should not need any disaster recovery options, so I recommend <em>LRS<\/em>.<\/li>\n<\/ul>\n<ul>\n<li>Encrypting the company\u2019s data is always good. <em>Enable<\/em> both.<\/li>\n<\/ul>\n<ul>\n<li>Chose the same subscription, resource group, and Azure region that was selected for the SQL Server.<\/li>\n<\/ul>\n<ul>\n<li>Click <em>Create<\/em>.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/create-storage-account\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C544&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/create-storage-account.jpg?resize=645%2C544&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>After a few minutes, the storage account will be ready for use.<\/p>\n<p>The storage account is analogous to a SAN or storage array. A container is analogous to a LUN or drive and then folders can be created within a container. A container must be created to accept the bacpac export.<\/p>\n<p>Navigate to the storage account. This can be accomplished in a number of ways. I prefer to search for its name in the top menu bar or navigate to it via the resource groups button on the left navigation bar. Once there, go to the container page and click <em>+Container<\/em>.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/create-container\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C296&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/create-container.jpg?resize=645%2C296&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>Name your container, it must be unique within the storage account. Set <em>access type<\/em> to <em>private<\/em> so that only users with the proper access key will be able to read and\/or write. Then, click <em>OK<\/em>.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/create-container-2\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=630%2C299&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/create-container-2.jpg?resize=630%2C299&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<h3>Access key<\/h3>\n<p>Before jumping into the export data-tier application wizard, copy down the storage account key. In the Azure portal, navigate to your storage account and view the <em>Account Keys<\/em> page. Copy down either of the two keys.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/access-keys\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C281&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/access-keys.jpg?resize=645%2C281&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<h3>SSMS wizard<\/h3>\n<p>Swap back to SSMS to export a data-tier application. Verify that there is enough disk space to copy the entire uncompressed database and then right-click on the database and select <em>Tasks > Export Data-Tier Application<\/em>.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/export-data-tier-app\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C509&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/export-data-tier-app.jpg?resize=645%2C509&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>Populate the <em>Settings<\/em> tab of the wizard as follows.<\/p>\n<ul>\n<li>Connect to the storage account.<\/li>\n<\/ul>\n<p>Paste in the key from above here.<\/p>\n<ul>\n<li>Select the container from the drop-down box and choose a name for the bacpac file.<\/li>\n<\/ul>\n<ul>\n<li>Choose the local directory for the temporary file.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/export-wizard-1\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/export-wizard-1.jpg?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>It is possible to export the bacpac file locally without transmitting it to Azure. It would then have to be copied to an Azure storage account by the user. Copying to Azure storage won\u2019t be covered in this section but will be covered later in the command line interface method.<\/p>\n<p>If the database is to be uploaded only in part, the <em>advanced<\/em> tab allows for schemas and\/or objects to be deselected.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/export-wizard-2\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/export-wizard-2.jpg?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>After clicking <em>next > finish<\/em>, the bacpac file will be transmitted into Azure.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/export-wizard-3\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/export-wizard-3.jpg?resize=645%2C587&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<h3>Import bacpac into Azure SQL logical server<\/h3>\n<p>In the Azure portal, navigate to the logical SQL Server and select <em>Import database<\/em> from the <em>Overview<\/em> page.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/import-sqldb-1\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C195&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/import-sqldb-1.jpg?resize=645%2C195&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<ul>\n<li>Select the uploaded bacpac from the storage account and container where it resides.<\/li>\n<\/ul>\n<ul>\n<li>Choose the necessary pricing tier.<\/li>\n<\/ul>\n<ul>\n<li>Name the database.<\/li>\n<\/ul>\n<ul>\n<li>Populate the logical SQL Server admin user and password.<\/li>\n<\/ul>\n<ul>\n<li>Click <em>OK<\/em>.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/import-sqldb-2\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C309&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/import-sqldb-2.jpg?resize=645%2C309&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<p>When the import is complete, the database will show up in the SQL Server\u2019s database list.<\/p>\n<p><a href=\"https:\/\/sqlhammer.com\/launch-your-database-into-azure-sql-database-bacpac-edition\/import-sqldb-3\/\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=645%2C415&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/06\/import-sqldb-3.jpg?resize=645%2C415&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n<h2>Command line interface (CLI)<\/h2>\n<p>The recommended method for working with Azure is always PowerShell. The Azure portal and SSMS are tools there for your convenience but they do not scale well. If you have multiple databases to migrate, potentially from multiple servers, using PowerShell will be much more efficient. Scripting your Azure work makes it repeatable and works towards the <a href=\"https:\/\/en.wikipedia.org\/wiki\/Infrastructure_as_Code\">Infrastructure as Code<\/a> concept.<\/p>\n<p>In this demonstration, the below steps will be used.<\/p>\n<ul>\n<li>Export the bacpac file to a local directory with sqlpackage.exe.<\/li>\n<\/ul>\n<ul>\n<li>Copy the bacpac to Azure Blob Storage with AzCopy.exe<\/li>\n<\/ul>\n<ul>\n<li>Use the PowerShell AzureRM module and cmdlets to create an Azure SQL Database from the bacpac file.<\/li>\n<\/ul>\n<h3>SqlPackage.exe<\/h3>\n<p>Exporting your bacpac is quite simple. With the DAC framework installed, the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/hh550080(v=vs.103\">sqlpackage.exe<\/a>.aspx) utility can be called with the <em>Export<\/em> action type. The below script will export the bacpac file to a local directory. The console output is fairly verbose and shows the order of operations between exporting the schema and then the data.<\/p>\n<pre><code class=\"language-\"># Export bacpac file to local directory\n\n<p>$DACversion = \"140\"\n$DACpath = \"C:\\Program Files (x86)\\Microsoft SQL Server\\$DACversion\\DAC\\bin\"\n$ServerInstance = \"SQLHAMMERLAPTOP\\SQL2017CTP20\"\n$Database = \"AdventureWorks2014\"\n$OutputFileName = \"AdventureWorks2014.bacpac\"\n$OutputDir = \"C:\\Backups\\\"\n$OutputPath = Join-Path $OutputDir $OutputFileName<\/p>\n\n<p>Set-Location $DACpath\n.\\sqlpackage.exe \/Action:Export \/SourceDatabaseName:$Database \/SourceServerName:$ServerInstance \/TargetFile:$OutputPath \/OverwriteFiles:True<\/code><\/pre>\n<\/p>\n<p><em>NOTE: I will be reusing the variables in my next couple of scripts without re-instantiating them. The full script is available for download at the end of the article.<\/em><\/p>\n<h3>AzCopy.exe<\/h3>\n<p><a href=\"http:\/\/aka.ms\/downloadazcopy\">AzCopy.exe<\/a> is utility designed to copy objects to, from, and between Azure Storage Accounts. The syntax is straight forward and the <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/storage-use-azcopy\">documentation<\/a> is full of examples for various use cases. The below script uses this utility to copy the bacpac file from the local directory to Azure Blob Storage.<\/p>\n<pre><code class=\"language-\"># Copy bacpac file to Azure storage account\n\n<h1>Download and install Microsoft Azure Storage Tools<\/h1>\n<h1>&lt;a href=\"http:\/\/aka.ms\/downloadazcopy\"&gt;http:\/\/aka.ms\/downloadazcopy&lt;\/a&gt;<\/h1>\n$AzCopyDir = \"C:\\Program Files (x86)\\Microsoft SDKs\\Azure\\AzCopy\"\n\n<p>$resourcegroupname = \"LaunchDB-bacpac-RG\"\n$StorageAccount = \"bacpacrepository\"\n$ContainerName = \"bacpacs\"\n$StorageURI = \"https:\/\/$StorageAccount.blob.core.windows.net\/$ContainerName\/\"<\/p>\n\n<p>$StorageKey = \"mykey\"<\/p>\n\n<p>Set-Location $AzCopyDir\n.\\AzCopy.exe \/Source:$OutputDir \/Dest:$StorageURI \/DestKey:$StorageKey \/Pattern:$OutputFileName<\/code><\/pre>\n<\/p>\n<h3>AzureRM<\/h3>\n<p>The last step in this process is to import the bacpac into the logical SQL Server which was previously created. Azure is designed for PowerShell management and the AzureRM PowerShell module has a lot of convenient cmdlets to use.<\/p>\n<p>The below script will:<\/p>\n<ul>\n<li>Import the bacpac file to a Standard S3 Azure SQL Database.<\/li>\n<\/ul>\n<p>Standard S3 is being used to improve the performance of the import process.<\/p>\n<ul>\n<li>Monitor the progress in a loop.<\/li>\n<\/ul>\n<ul>\n<li>Downgrade the price tier to Basic.<\/li>\n<\/ul>\n<p>Basic is the long-term intended price tier for this particular application.<\/p>\n<p><em>NOTE: In this part of the script the Get-AzureRmStorageAccountKey cmdlet is used so that the real account key does not need to be stored in the script in plain text. This same method can be used above with the AzCopy step but the Login-AzureRmAccount cmdlet will need to precede the AzCopy step then. This method is shown in the full script download linked below.<\/em><\/p>\n<pre><code class=\"language-\"># Import bacpac into an Azure SQL Database\n\n<h1>Requires the AzureRM module<\/h1>\n<h1>&lt;a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/azure\/install-azurerm-ps?view=azurermps-4.1.0\"&gt;https:\/\/docs.microsoft.com\/en-us\/powershell\/azure\/install-azurerm-ps?view=azurermps-4.1.0&lt;\/a&gt;<\/h1>\n\n<p>Import-Module AzureRM<\/p>\n\n<h1>This pops open a GUI for login.<\/h1>\n<h1>Making the login process silent and unattended is out of scope for this article.<\/h1>\nLogin-AzureRmAccount\n\n<p>$location = \"East US\"\n$adminlogin = \"SQLHammer\"\n$password = \"superSECRETpassword\"\n$servername = \"launchdb-bacpac\"<\/p>\n\n<h1>Import bacpac to database with an S3 performance level<\/h1>\n$importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $resourcegroupname <code>\n-ServerName $servername <\/code>\n-DatabaseName $Database <code>\n-DatabaseMaxSizeBytes \"262144000\" <\/code>\n-StorageKeyType \"StorageAccessKey\" <code>\n-StorageKey $(Get-AzureRmStorageAccountKey -ResourceGroupName $resourcegroupname -StorageAccountName $StorageAccount).Value[0] <\/code>\n-StorageUri \"$StorageURI$OutputFileName\" <code>\n-Edition \"Standard\" <\/code>\n-ServiceObjectiveName \"S3\" <code>\n-AdministratorLogin $adminlogin <\/code>\n-AdministratorLoginPassword $(ConvertTo-SecureString -String $password -AsPlainText -Force)\n\n<h1>Check import status and wait for the import to complete<\/h1>\n$importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink\nWrite-Host \"Importing\" -NoNewline\nwhile ($importStatus.Status -eq \"InProgress\")\n{\n$importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink\nWrite-Host \".\" -NoNewline\nStart-Sleep -s 10\n}\nWrite-Host \"\"\n$importStatus\n\n<h1>Scale down to Basic after import is complete<\/h1>\nSet-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname <code>\n-ServerName $servername <\/code>\n-DatabaseName $Database\u00a0 <code>\n-Edition \"Basic\" <\/code>\n-RequestedServiceObjectiveName \"Basic\"<\/code><\/pre>\n<p><strong><em>Download full script <a href=\"\/wp-content\/uploads\/Upload-bacpacs.txt\">here<\/a>.<\/strong><\/em><\/p>\n<h2>Take aways<\/h2>\n<p>Covering the spectrum from command line interface to graphical user interface, there are multiple methods of exporting and importing bacpac files into an Azure SQL Database. The bacpac file method is simple and convenient but assumes that the database can be exported to an on-premises disk before being imported. Also, it assumes that the database can be quiesced or else you will have data loss when cutting over to the Azure SQL Database.<\/p>\n<p>The dacpac method with ETL solves the problem of exporting large quantities of data to on-premises shares and the transactional replication method can solve the downtime issue. Look into those methods next to understand all the options available.<\/p>\n<ul>\n<li>Export and deploy a dacpac file (schema only) and then ETL your data.<\/li>\n<\/ul>\n<ul>\n<li>Transactional Replication.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>You have decided to migrate our on-premise SQL Server database into Azure SQL Database. How do you do that? The first thought that comes to mind is a simple backup from on-premise and restore to Azure SQL Database. Unfortunately, that is not supported by Microsoft. Instead, there are three supported methods of migrating into Microsoft\u2019s [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-175","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=175"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/175\/revisions"}],"predecessor-version":[{"id":437,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/175\/revisions\/437"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}