{"id":725,"date":"2016-08-16T09:00:47","date_gmt":"2016-08-16T16:00:47","guid":{"rendered":"https:\/\/bornsql.ca\/?p=725"},"modified":"2016-08-15T17:50:04","modified_gmt":"2016-08-16T00:50:04","slug":"moving-database-azure-sql-database","status":"publish","type":"post","link":"https:\/\/bornsql.ca\/blog\/moving-database-azure-sql-database\/","title":{"rendered":"Moving a database to Azure SQL Database"},"content":{"rendered":"

This week we will actually move a database into Azure SQL Database, using the first of two of Microsoft’s recommended methods.<\/p>\n

The main thing to keep in mind is that SQL Server (on-premises, or “earthed”) and Azure SQL Database (“cloud”) are not the same product. They support the same schemas and data, and allow the same scripts and applications to run, but they are not the same thing.<\/p>\n

This is important, because when we migrate to Azure SQL Database from an on-premises SQL Server environment, we can’t simply do a backup and restore of our database.<\/p>\n

The simple reason is that there is no file system access in Azure SQL Database. We won’t be able to see a backup file to restore it.<\/p>\n

The more complicated reason is that we don’t have access to any instance-level features, including server configuration, tempdb access, and so on, and therefore our databases must be self-contained.<\/p>\n

The recommended method to migrate a database to Azure SQL Database is the Migration Wizard<\/em> in SQL Server Management Studio.<\/p>\n

For larger databases, we\u00a0can create an extract first, which can be imported later using the necessary tools.<\/p>\n

Under the covers, the migration is using SQL Server Data Tools to extract a Data-tier Application<\/em> (DAC). This acronym is not to be confused with Dedicated Administrator Connection.<\/p>\n

According to MSDN<\/a> (emphasis added):<\/p>\n

A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects including logins – associated with a user’s database. A DAC is a self-contained unit<\/strong> of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.<\/p>\n

A BACPAC is a related artifact that encapsulates the database schema as well as the data stored in the database.<\/p><\/blockquote>\n

From this, we can establish that we are creating a BACPAC file, containing the schema in XML format, a few metadata files with version information, and of course the data itself, in BCP (Bulk Copy<\/a>) format. This is then\u00a0compressed (using standard PK-ZIP format) in order to save space.<\/p>\n

Anyone who has\u00a0used other products that create database dump files will be familiar with this process.<\/p>\n

The BCP file format is not human-readable, unlike, say, MySQL’s dump files, and if we\u00a0look at the MSDN article on format files, it will make our heads spin.<\/p>\n

That said, I was able to pick out string values from the BCP file in my test that corresponded with row values in the source database table.<\/p>\n

To be on the safe side, I like\u00a0to break up the process into two stages, so that if the second part fails, we\u00a0can retry it at our convenience:<\/p>\n