I didn’t complete it
In my previous attempt to get my local SQL Server MediaLibrary into Azure, I left it incomplete. I truly didn’t realize that until I tried to connect SQL Server Management Studio (SSMS) to my database in Azure. It connected, but there was no data in any of the tables. My bad.
So let’s get this right. First, I deleted the database in Azure SQL. I don’t know if it is possible to do it any other way, but since it’s a small enough database I thought that would be all right. That and when I tried to upload it straight from SSMS, I got error messages about incompatibility. So in order to resolve this I decided to delete the previous instance from the Azure portal, to start over again. To do this I returned to the MSDN article SQL Server database migration to SQL Database in the cloud.
Starting Over Again
There are three steps you must take in order to migrate your local SQL database into Azure, they are:
- Test for compatibility
- Fix compatibility issues
- Perform the migration
I decided to use SQL Server Data Tools for Visual Studio (SSDT). This is for testing for compatibility. That led me to a link titled Migrate a SQL Server Database to Azure SQL Database using SQL Server Data Tools for Visual Studio.
WOW, in going through this I’ve discovered what step I left out. Its towards the end under “Fixing Compatibility Issues Using SQL Server Data Tools for Visual Studio”. I stopped short of doing step #4 which is updating the local target V12 compatible database and actually publishing the original data to the V12 compatible database.
(Someone, I can’t remember who nor where, asked me for my feedback on where in the Microsoft Azure documentation, did I leave off accidentally. I’m sorry, I can’t remember who you were, but it is here, in this Migrate a SQL Server Database to Azure SQL Database using SQL Server Data Tools for Visual Studio. I think it was because this article, to me, seemed to suggest that if I followed this article, I’d be done. I was mistaken in my interpretation, that is clear. And it was before this point where I stopped previously.)
Step 5 is a link back to Migrate a compatible SQL Server database to SQL Database. So I’m returning to the that article to select a migration method.
Migrating my SQL Server compatible database to Azure SQL
Because of my local video library database is small and I can control activity to it, I’m going to use the SSMS Migration Wizard. Selecting the link to migrate my compatible database I went to a page titled Migrate SQL Server database to SQL Database using Deploy Database to Microsoft Azure Database Wizard.
After getting back in SSMS I right mouse button clicked on the new V12 compatible database I created locally, and selected “Deploy Database to Microsoft Azure SQL Database” option under Tasks. That brought up a wizard to walk me through deploying the database.
At this point I started entering my Azure credentials, this time selecting a different name for the target database in Azure (just in case). I noticed that for the temporary file name the wizard is creating a .bacpac file. Interesting. So one way or other a BACPAC is used to perform the migration.
I’d like to point out that at this step the dialog box that I got for entering my initial credentials, didn’t look like what was in the article. Here’s what mine looked like:
The instructions at step 6 talk about naming the database, setting the edition of Microsoft Azure SQL database, max database size, etc. Because this is an experimental database that I alone will be hitting against (maybe some of my family members will use it later on when I get to the point of writing an app to hit against it – I’ll blog about that as well), I am choosing Basic Edition, 2 GB of database size (that’s the max for basic anyway) and Service Objective of Basic.
Clicking the Next button takes you to a summary. And here I can click on the Finish button which actually performs the migration of the database and data, into Azure SQL.
And here’s what the Deployment Settings window looked like:
Verifying it worked
While still in SSMS I used Object Explorer to connect to my Azure SQL database to just make sure it worked.