“Stretching” my SQL Skills

Philosophy has always intrigued me and René Descartes is one of my personal favorite philosophers.  Cogito ergo sum “I think, therefore I am”.  The moment we stop thinking is the moment we stop existing. So, today I decided to learn something new.  I wanted to setup a Stretch Database in SQL Server 2016.

Just about everything in SQL Server can be accomplished by either the SSMS GUI or Transact SQL commands.  Anytime, I attempt something for the first time I always use the GUI, then learn the T-SQL commands.

I began by right clicking on my [StretchTest] database and selecting TASKS >> STRETCH >> ENABLE.Image1

This will launch the “Enable Stretch Wizard.  First step in the wizard is the the Introduction.  First thing I noticed was the the statement: “Once the data is migrated to Azure, unique and primary keys will not be enforced on Azure”  Really?  I wonder what else will be “not enforced” or not compatible with Azure.  Time to look up my friendly neighborhood BOL, Limitations for Stretch Database. Wow, that’s a good number of limitations; however this is a simple test so I am not worried.

Next we have to connect to an Azure Subscription. The wizard step allows you to configure your SQL Azure database environment, select the Azure region (always pick one closest to you) and you get to choose between a preconfigured Azure server or allow the wizard to create one. I chose to allow SSMS to create a new Azure server. Next you have to create a database master key (DMK) to protect your Azure data.  And finally you need to provide your IP Address (remember your external IP, not your machine IP) so SSMS can setup firewall rules for you.



Then you get the customary SQL Summary page before you execute the commands. Now that my database has been stretched, I added a table and data using this schema:

CREATE TABLE [dbo].[tblUsers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Email] [varchar](100) NULL,
	[DateEntered] smalldatetime null,

I not only wanted to test the Primary Key limitation; but I also wanted to test the Filtered Stretch on the Date Entered Column. I populated the table with 11,000 records using the website http://mockaroo.com.

After the tblUsers table was populated, I enabled the Stretch Table wizard by right clicking on the table and selecting STRETCH >> ENABLE.  After the Introduction screen you get to select which tables you would like to stretch to Azure.  The GUI screen shows you all the tables and if there will be any “warnings” about stretching, the option to filter, number of rows in the table and the size of the table. 


The first time I executed this I did not use the Stretch Filter and stretched the entire table.  It worked perfectly.  I could use SSMS to connect to my remote SQL Server and query the Azure table directly and could see there were 11,000 rows.

Executing a simple SELECT query against the local database produced this Query Execution plan: RemoteQuery

As you can see, we now have a new path in our query plan with an operator called “Remote Query”.  Basically the local server queries the remote query then using the local Primary key Concatenates them back together to produce the desired result. So can we update the data?


Nope, sure can’t.  Once the data lives in Azure, the data is READ ONLY.

Next I wanted to add the filtering on Table3the DateEntered column so I can edit more recent data.  I disabled Stretch and re-enabled it using the filters this time.  I received error after error after error.  According to BOL this query is not a valid filtering query.  According to BOL, the date parameter needs to include the format value.  At least that was the case with mine.

CREATE FUNCTION dbo.fn_example5(@column1 datetime)
RETURN  SELECT 1 AS is_eligible  
        WHERE @column1 < CONVERT(datetime, '1/1/2014')  

/*  CORRECT  */
CREATE FUNCTION dbo.fn_example5(@column1 datetime)
RETURN  SELECT 1 AS is_eligible  
        WHERE @column1 < CONVERT(datetime, '1/1/2014',101)  

After initiating the filter, I queried the remote server directly and noticed there wereRemoteQuery_AzureRows

6713 rows found in Azure


and 4287 rows found in the local database.  Exactly what I expected.  Again, the query execution plan has to use “Remote Query”  and Concatenate the results to produce the desired results.


Final Thoughts on Stretch Database

The SSMS has designed the Stretch Wizards to be very effective and very easy to use.  However, when you “Disable” a stretch database it does not delete the table/database in Azure which means you will still incur charges for storage. And if you re-enable Stretch on a database it creates a 2nd database in Azure. 

With the READ-ONLY limitation from the Azure side of Stretch database, you better make 100% sure you don’t need to edit anything in Azure. The only way to make any changes to the Azure side of the data is to Disable and “bring the data back” to the local machine and this incurs charges on the Azure side. Then you make your changes to the data, and re-enable it for Stretch.  Which again, created another database in your Azure server.

The pricing for Azure SQL Stretch database seems to be a little steep. At the lowest performance level offered, it is ~$1860 per month!  Microsoft calculates what they call DSU (Database Stretch Units) which “represents thee power of the query and is quantified by workload objectives: how fast rows are written, read and computed against.”  https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/

All in all, Stretch Database is a very cool feature for archiving older data that will not change.  But only if  you can afford it.  I would think using file groups with partitioning of data on separate drives could be a more cost effective solution for archiving than Azure stretch database. A very good learning experience. 

My first time, lessons learned!

This past weekend, I had the privilege of doing a presentation at SQL Saturday #514 in Houston Texas.  I had been to other SQL Saturdays before as both a participant and a “volunteer”; but I had never have spoken before.

Speaking in front of people is usually no big deal to me.  I have been designing, presenting, hosting and administrating all types of training for the Boy Scouts for many, many years.  I have taught Scouting newbies how to tie a knot to presenting on topics like “Project Planning and SMART goals” (email me if you want to know what SMART goals are).  I have even coordinated an all day training event very similar to SQL Saturday for my Council called University of Scouting.

But what was different about this experience was the topic.  I have been active in the Boy Scouts since I was eleven.  I know Scouting information, concepts, and topics backwards and forwards. I have been facilitating trainings (presenting) since I was 15. So Scouting is 2nd nature to me.  But presenting on SQL Server topics was somewhat nerve wracking.  For me it was a test of not my presenting abilities; but my SQL DBA abilities.  As an “Accidental DBA” I have always questioned my abilities with SQL Server.  Everything I have learned about computers I have learned my self, so I am always 2nd guessing myself. 

As a “First timer”, I thought it would be best to cover Beginning Level topics.  My thought was 1) I knew the material and 2) after reading Tim Ford’s #EntryLevelChallenge it motivated me to remember my fellow newbies.

I won’t go into the topic of my presentation today as this is more about the experience.  The audience, which I had 14 in attendance, was very attentive and I don’t think I put anyone to sleep!

I do need to work on my demos.  They all worked as expected; but switching between laptop and projector, throwing SSMS on the projector screen, changing screen settings from duplicate to extend was all too much.  There has to be a better way to switch between PowerPoint in presentation mode and SSMS to do demo. So I will practice that.

I thought I did best on time management.  The time allotted was 60 minutes, I said “Thank you for your time” at 59 minutes and 30 seconds. 

Over all the experience was well worth it. By the responses I received from the speaker evaluations, I think I did an OK job.  I had no major criticisms except to slow down, I assure you that was nerves at the beginning. 

SQL Saturday

This Saturday is SQL Saturday #514 in Houston, TX. 

And this Saturday, I will be doing my first presentation at a SQL Saturday.

Luckily my presentation is in the morning so I can get it over with and enjoy the rest of the day. Otherwise, I probably would be stressing about it all day!

Hostile Takeover is my presentation on what do you do when handed the keys to a SQL server you have never seen.  What information would you want to know and more importantly HOW do you get that information!

I will show some easy to use tools and scripts developed by people way smarter than I am.  Heck, I will even show you how to hack into a SQL Server!  As far as my website goes, I will update my slide deck and scripts repository after SQL Saturday!  And hopefully I will find the time to upload the information to the SQL Saturday website!

The End of an Era

A Legend is dead.

SQL Server 2005 is no longer supported.  Today is the first day that DBAs and companies cannot call on Microsoft to help with or support a SQL Server 2005 Instance. (of course I am sure M$FT will gladly accept some money for additional support).

But many companies will still run with this stalwart and continue to use it until it dies.  SQL 2005 has proven over time to be a stable system capable of doing a yeoman’s job.

Nine years ago, SQL Server 2005 revolutionized the way DBAs interacted with SQL Server.  SQL Server Management Studio (SSMS) brought a new method of administering and programming SQL server.  The Graphic User Interface (GUI) based program was miles ahead of the old Microsoft Management Console (MMC) plugin, SQL Enterprise Manager.

SQL Server 2005 was the first major overhaul of the SQL Engine, it introduced among other things XML data type, Common Language Runtime integration, and most importantly Dynamic Management Views (DMVs). 

While some say “death to the dreaded GUI, long live T-SQL”, I for one, as a visual person, love the GUI.  Yes, I always find myself using the “script” feature to learn the actual T-SQL and to execute the T-SQL for faster execution, but some tasks are made simpler using the GUI instead of T-SQL.

It definitely seems the development of SQL Server 2005 was directed by actual SQL developers, administrators, and users. 

Over the years and versions, many features have been added to SSMS to even make it a better application: detailed graphical execution plans, intellisense, Central Management Server, the list could go on.  I for one am excited that the Server Group has handed off SSMS as a separate tool because hopefully it can be placed on a more aggressive update schedule.  Hopefully the development of SSMS will keep with pace of our needs.

So here’s to SSMS and it continued improvements and features.Drinking-Cheers

So to recap, here is a list of SQL Server version still under main stream support:

  • SQL Server 2012 – support ends 7/11/2017
  • SQL Server 2014 – support ends 7/9/2019

That’s it, two versions!!  (I did not include SQL Server 2016 on this list, because technically it has not been released for general use.)

Extended support, which only includes security updates is a little better:

  • SQL Server 2008 & 2008 R2 – extended support ends 7/9/2019
  • SQL Server 2012 – extended support ends 7/22/2016
  • SQL Server 2014 – extended support ends 7/9/2024

Knowing how long it takes to get capital expenditures approved and how expensive SQL Server can be, you better start budgeting  to replace SQL Server 2008 & 2008 R2 machines now! 

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code


To Catch A Falling Cactus


Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance