dotnetco.de

In my asp.net applications I use MS SQL Databases for data storage. As I’m currently in the Free Trial period, I want to migrate / copy / sync / import / export (whatever you name it) my existing MS SQL Database to Microsoft Azure SQL Database.

Prerequisites

Microsofts MS SQL Server Management Studio has feature included to deploy a database to Azure. So I downloaded the latest version of MS SQL Management Studio which was 2015 July preview. It installs nice to my MS SQL Management Studio 2012 SP 2 so you could use both in parallel.

As seen on Azure Pricing Details, SQL Database is not available in Free Plan, only in Basic, Standard and Premium editions. So you will have to pay for it. Anyway, I’m in the free trial, so let’s start creating the database.

Create Azure SQL Server

If you are new to Azure and have not create a Azure SQL Server yet, go to your Azure Portal and click on ‘New’ -> ‘Data + Storage’ -> ‘SQL Database’. Open option ‘Server’ and click ‘Create a new server’. There you could enter the Servername you like to use. It’s always in ‘.database.windows.net’, so you might end up with something like ‘MySQLServer.database.windows.net’. Additionally you also have to create a new Server Admin Login and select the location. With ‘OK’ your first Azure SQL Server is created.

Now you could go back to your portal page. You should not create a database now, because otherwise you will later on get the error message ‘The database already exists on the target instance of SQL Server. Specify a database name that does not already exist.’

Firewall settings

By default, your new Azure SQL Server is not accessible from outside so you would not be able to connect with your local machine to it. Therefore we have to maintain the firewall settings first. So in Azure Portal, click ‘Browse all’ and find your new SQL Server and open the details. On the ‘Essentials’ tab you will find an option ‘Show firewall settings’. When you click on it you will see that there are currently no firewall rules configured but you will also see your Client IP Adress. And a button ‘Add Client IP’. How convenient! Click the button and save the new settings. Keep in mind that you have to redo this step every time your local IP changes (if you have a dynamic IP address).

Start deploying

So I started MS SQL Server Management Studio and connected to the database on my local MS SQL Server. With a right-click on the database you have a menu option ‘Deploy Database to Microsoft Azure SQL Database’ within menu ‘Tasks’. After reading the info page you have to establish the connect to the target Azure SQL Server you just created. Enter the full database name (e.g. ‘MySQLServer.database.windows.net’) and the server admin login with password. Next to it, enter the name of your new database. Click ‘Next’ to get a summary of your settings and click ‘Finish’ to start the deployment.

You will see the different steps, like ‘Extracting Schema’, ‘Validating Schema Model’ etc. If all went well: Fine, you’re finished! Unfortunately I had lots of errors. Errors like

• Error SQL71561: Error validating element [dbo].[myview]: View: [dbo].[myview] has an unresolved reference to object [mydb].[dbo].[mytable]. External references are not supported when creating a package from this platform.
• Error SQL71501: Error validating element [dbo].[myview]: View: [dbo].[myview] has an unresolved reference to an object. Either the object does not exist or the reference is ambigous because it could refer to any of the following objects:
• Error SQL71562: Error validating element [dbo].[myprocedure]: Procedure: [dbo].[myprocedure] has an unresolved reference to object [mydb].[dbo].[mytable]. External references are not supported when creating a package from this platform.

Wow. Some dozens errors.

Fortunately they were all very easy to fix: In the above mentioned view I included the database name in some queries, and the deployment process does not like that. So I opened MS SQL Server Management Studio 2012 and checked all the procedures and views mentioned in the error messages. They contain queries like ‘SELECT [ID] FROM [MyDatabase].[dbo].[MyTable]‘ even though the data is retrieved from the current database. So I removed the Database name from all these queries, resulting in e.g. ‘SELECT [ID] FROM [dbo].[MyTable]‘.

That was very easy to fix, and now the deployment ran fine. The new Azure SQL Database is now accessible in MS SQL Server Management Studio like all other databases from any other server.