Tag: Database

SQL2016 Stretch database. Yes, it’s another linked server

Your manager always wants to keep all the data…now with bigdata being a thing and algoritmes are used more efficient (IOT), people want to hoard data even more, but it needs to be online to be valuable. If you have to restore the data first, your data is not very valuable. But all this extra data also slows down your database performance…and gives the dba’ers extra challenges….but not anymore.! Well, at least when your boss got a big wallet and don’t mind spending it on a SQL buget.

In SQL2016 Microsoft introduced the stretch database. which give you live access to ‘archived’ data and makes it feel like it’s on premise. And  it’s a secretly just a linked server! A linked server between mssql and Azure.LS+azure

How does this work, and is it safe?

Yes! since it works on secure linked server technology and it stores an encryption key on the on-premises sqlserver, it’s suitable for all your cold data, not hot, because it does slow down the query speed, but it will not clogg your network and your data will be available at all times, even when the data is being stretched over to Azure the data still is fully query-able.

First ask yourself, Do you really need to keep this data? If yes, How do you create a stretchdatabase? Well, this is easy!

Hesitations? use this link for info on the MS Stretch database advisor.

I use a simple test database, the adventureworks2016 ctp3.

Before we can start, check if the data archive function is enabled, if not (0) enable it.

####Check status of Remote data archive####
SELECT @@VERSION AS 'SQL Server Version'; 
EXEC sp_configure 'remote data archive'; 
####Activate Remote data archive####
EXEC sp_configure 'remote data archive' , '1'; 

Next we can create a master key to encrypt all your data locally, this way, the data can’t be read in Azure.
Note: You can also create a masterkey from the stretchdb wizard, it’s up to you. I’ll show both options.

USE AdventureWorks2016CTP3; 


Next I will create a simple table  and populate it with some data, to keep the demo quick and simple :

CREATE TABLE dbo.Stretchtable
FirstName VARCHAR (50),
LastName VARCHAR (50)

Use [AdventureWorks2016CTP3]
INSERT INTO dbo.Stretchtable (FirstName, lastName)
VALUES ('Reed', 'Richards'), ('Benjamin', 'Grimm'), ('Sue', 'Storm'), 
 ('Johnny', 'Storm'), ('Victor', 'Von Doom'), ('Willie','Lumpkin');

Keep your Azure account ready! I use the SQL logical server resource, you need to create a resource before you can stretch the database.

Azure resource


Select the table you will be stretching to Azure. Now enable the table for stretching and the wizard will open up.

Enable stretch database

It’s a Hybride archive – You can stretch the whole table or filter out the (c)old data from a massive table.


Next thing you create a master key, if you didn’t make it before, if you made it already, It will ask you to fill in the master password.Masterkey_StretchTable

Fill in the IP range for a firewall rule.IP_Azure


Hey Ho, let’s go, that was easy!

Now the table will start to stretch, if you want to see if it stretched yet, try a select query and check the executionplan.

Want your data back? No problem, You can bring back the data to your on-premise server, if you don’t want to use Azure. Because, when you delete your azure account, the data will be gone too!



Note: Data storage can be expensive on Azure, but this function gives us a positive view on the ever growing databases and it’s possibilities.

SubSonic will be your friend

Subsonic claims to have a solution when you have better things to do than worry about Data Access. I, for one, am not very fond of the datalayer in my apps. I rather spend my time on the code instead of stored procedures and SQL queries. So, let’s go ahead and see if SubSonic lives up to its promise.

Let’s open the FrotMachine project from a few posts earlier.
Dowload SubSonic 3.0.0 and add a reference to the SubSonic.Core.dll in the Binaries folder:


Next, create a database. I usually fire up SQL Studio Management Express and create a database by typing:


Oh well, that’s almost too simple to mention.

Anyway, after that I go back to Visual Studio and add a new App.Config item and make sure it looks like this:


Let’s create an instance of a Frot right now and see if a table and a record in the database are being made. But wait, we need to add the using statement for Subsonic first:

using SubSonic.Repository;

Now we can add to our Main method:

var f = new Frot();
            f.FrotID = 1;
            f.FrotName = "GUI Goodness";
            f.FrotDescription = "Create a nice GUI with whatever tool";

//this is the SubSonic part:
            var repo = new SimpleRepository("FrotMachine", SimpleRepositoryOptions.RunMigrations);

Let’s check out the database now:


Isn’t that nice.. it worked. SubSonic created a table and a record in my database. Quite good, isn’t it? It sure is a lot easier than NHibernate. And twice as cool at that.

This may be a trivial example. I’m sure I’ve got my point accross though.