Hello everybody!

I have been working with one of my favorite clients lately on migrating their SQL databases to a DBaaS (Database as a Service) solution in Microsoft Azure. This has been such a fun project that I thought I would share with you the steps taken to hopefully help some of you along the way.

With all of that said, let’s start at the beginning and give you an overview of their existing environment.  My client started with me when they already made 2 huge decisions: 1) They were migrating from Oracle to SQL Server,  and 2) They were going to Azure IaaS (Infrastructure as a Service) for their first cloud approach.  This presented numerous challenges from inception.  One is the migration from Oracle to SQL (luckily they had a great company they contracted with to handle that).  Second, going to IaaS from their traditional on-premises hardware. The first thing I was asked to do was to assist in the architecture of their environment.

The decision for the OS was Windows Server 2016 mainly to take advantage of the Cloud Witness that was added to that OS. We wanted a three node Always On availability group that consisted of the following: 2 in Central US, and one in East Us.  Since SQL 2017 was just released when the project started, the decision for SQL Server 2016 Enterprise Edition was made. With those three things, we got to work on building everything needed for the SQL Server environment to host the databases in a highly available environment.  The reason the decision to use IaaS VS. DBaaS at this time was due to their discounts received as an Educational provider.  The discounts would apply only towards an IaaS solution, not towards DBaaS (stay tuned).

Live on IaaS

After a week or so the environment was built and tested.  It was time to put the databases onto the instance and let it rip!  The process started with me uploading the databases to Azure Blob Storage, and then restoring to the new IaaS instance.  Once the databases were on the instance, I began noticing performance issues right away.  Working alongside several members inside of RDX, we began diving into the performance problems.  Several tweaks to the SQL Server settings were made: MAXDOP, Cost Threshold for Parallelism, Memory allocation, TEMPDB placement, etc.  There was some noticeable gain, but I knew it was going to come down to the database code. Having seen several Oracle to SQL migrations in my life to know that the code from Oracle was probably not up to snuff with SQL Server.

Since tuning the code needed to happen, I began working closely with Bill Wolf (B/T) on reviewing their code and tuning it so that it would execute in the most efficient way possible (this meant actual tuning, not just giving it as many indexes as I could).  It took close to almost 2 months before majority of the code was tuned to the point where the system was stable enough to begin servicing connections from students.  One of the biggest challenges we faced at this point was their PowerBI connections occasionally hitting the Primary instance.  When that would happen, blocked sessions would skyrocket!  Working with the client I eventually was able to configure the PowerBI Gateway’s and connections to ONLY connect to the East US node. This helped out tremendously! Now the other two nodes were able to shoulder the rest of the workload.

A Demo That Lit the Spark

I was asked to do an Azure DBaaS Demo with my Marketing group.  The webinar would consist of me doing a 30-40 minute demo of Azure DBaaS offerings.  Jumping at the opportunity, I was still pretty new to the company at that point and wanted to help out as much as I could.  You can see that webinar here: Webinar Link.  Once that webinar was over, my client wanted to begin discussing what it would take to get them to be able to run on DBaaS.  The first thing was cost.  They needed to work out those details internally and with their Azure contact.  Once they had some of the groundwork there laid out, they were given a sandbox instance with a pretty big credit amount for me to play around with.

To start the work, I chose the smallest of their databases and attempted the first of many “Deploy Database to Microsoft Azure SQL Database…” in SSMS.  Several errors were generated that I glanced over quickly.  One of the errors was concerning a Windows login being used to access the database.  Well, traditional AD(Active Directory) is not supported/compatible with Azure SQL DBaaS so I would have to drop that user from the database.  Once that was completed, I reviewed the other errors.  They were all for the same type of issue:  [Database Name].[DBO].[Table Name] is not supported.  Some of these were referencing the database I was trying to deploy, the other errors were for the other application databases on the same instance.  This is when the fun began. I had to start looking at each of the errors presented and begin a migration plan for the client.

Code Review

The process of fixing the errors began by looking through the error messages and documenting the database objects that caused errors to be thrown.  While mapping out this plan, I started thinking back to my days when I worked for a software company and decided I really wanted to make this as simple as possible if my client decided to use this in production after doing their internal tests.  I wanted to document the changes made to the objects that generated an error,  and save the code after the error was resolved.  The idea behind this being to create one deployment script for ALL objects that needed updated.

One of the things I remembered from a SQLSaturday was Grant Fritchey(B/T) talking about doing Cross Database querying in Azure SQL Database, so I found his blog post on it.  Here it is: Cross Database Queries by Grant Fritchey.  When reading this post, I loved how simple this was shown by Grant and decided to take this approach when migrating the databases.  Since I never got to thank Grant in person (yet) for this wonderful post, allow me to do it here:  Grant, you wonderful DBA you!  Thank you for the awesome post!

Summary

That is all for this post folks.  This is the roadmap for migrating my client to Azure DBaaS. Stay tuned for the next post where I show the code review process that I took and some of the errors I ran into along the way!  As always, thank you for reading and please let me know if you have any questions!