Whenever you see Microsoft demonstrate any of its nifty new technologies, you would think they are so easy that a lightly-trained hamster could fire them all up in five minutes. It never turns out this way in real life. Microsoft purposefully builds demos to give the easiest path to a showy result. Then you get into the real world, and often find yourself plodding through “instructions” that appear as if Bletchley Park cryptologists wrote them as some sort of cruel joke. As you plod through roadblock after roadblock, you invent whole new classes of profanity. You desperately hope to devise a solution that works and is usable in production.
I just lived through this with Azure Stretch Database. It was actually not as bad as other “nifty new” Microsoft inventions, but please be aware of some things. Here’s a start to finish tour of Azure Table Stretching in two parts. In Part One, we deal with the essentials of setting up Stretch Database. Part Two deals with real-world challenges for the DBA to operate with Azure Stretch Database.
Forget the Wizard!
The Enable Database for Stretching wizard looks as if it holds out some real promise to make your life easier. However, the Wizard has some real drawbacks.
First, the Wizard doesn’t generate scripts. So, while it can accomplish the result, it cannot give you a script that would enable you to follow any cogent process for deploying it into an enterprise. It’s a black box. If you want to follow any kind of reasonable deployment process, you need scripts. These scripts could also show you how the process works and reduce the learning curve. How this could be omitted is quite beyond me. The wizard is essentially a toy for the junior DBA to experiment without actually learning how to implement the technology. Please use T-SQL as shown below. You won’t regret it.
Second, the Wizard will tell you (after a long delay) that table X is not suitable for stretching, and give a somewhat useful message that explains why. The usual reason is that the table uses a check or default constraint; both are not allowed in stretched tables. Another reason is that the table is the parent of (referenced by) a foreign key relationship. There are more constraints that I don’t list here but which you can find through Internet research. However, you can get all of the same data more rapidly from T-SQL calls.
Setting Up Stretching
There are 8 steps, each explained below.
Enable Remote Data Archive
You first need to enable stretching. This is a simple configuration option. Like this:
sp_configure 'remote data archive',1
The remote data archive configuration isn't even an advanced option, so you can simply run this if you have the permissions.
Create an Azure SQL Database Server
Now, create an Azure SQL Database to accomplish this; it is the easiest way to do so. This is best done in the Azure portal.
With exciting products like SQL Clone making their debut, the DBA will need to think through the approach to implementing such powerful tools. Done properly, these tools will provide a massive benefit to both the DBA and developer.