SQL Server 2012 will have Distributed Replay

What I have experienced as a Microsoft Data Platform TSP in the past several weeks since the 2011 PASS Summit is that most of the discussion, testing, excitement and energy around the new SQL Server version (now SQL Server 2012, previously SQL Server Code-Named Denali) has centered around the big-ticket items:

  1. Always On
  2. Power View (formerly Project Crescent)
  3. Column-based Indexes
  4. SQL Appliances
  5. SQL Server Developer Tools (Visual Studio Integration formerly Project Juneau)
  6. Pricing Changes

I’ve written in this blog (you can search this from the WordPress search bar at top) about other “2nd-tier” updates to the SQL Server platform that will ship with the GA of SQL Server 2012 in early 2012 such as Sequence Objects.

I want to introduce you to another nice new feature in SQL Server that is something that I have heard customers ask about for a while now: Distributed Replay. This is the ability to capture workload from multiple SQL Servers from an application perspectives and be able to replay those for development & testing purposes. It makes sense that this is a feature that took some time to make its way natively into the product. In the past, I have seen some very clever and effective solutions that customers have wired together using downloaded 3rd party tools, open source generators and replaying SQL Profiler traces.

Those work well enough. But now you have a tool that can capture workload from more than 1 SQL Server at a time and to have a tool that can play those transactions back with different load targets. This will be very helpful for regression testing, load testing and staging among other areas.

Jonathan Kehayias at SQL Skills put up a blog on how to get in installed and configured (there is a server & a client component) and I like James Serra’s write up as an overview to learn a little bit more.

Add SSDT to Your SQL Server Vernacular

SQL Server professionals know all the acronyms and abbreviations in SQL Server land: SSIS, SSRS, SSAS, SSMS, TSQL, BIDS, so on and so on … In the next version of SQL Server, codename “Denali”, there is a new term to be familiar with, particularly for SQL developers and SQL Server database schema developers: SSDT or SQL Server Developer Tools. This is not a replacement for BI Development Studio (BIDS). But you should note that in Denali, all Visual Studio development (BI & database) uses Visual Studio 2010 (VS2010) now, not Visual Studio 2008. You won’t need to separately license VS2010 unless you wish to use some of the more advanced features in Visual Studio and the development team features. But a new set of database development templates are made available with SSDT in Denali and they are closely associated with the preferred database access techniques in .NET development circles, known as Entity Framework, or EF together with LINQ:

When you use Visual Studio to design your SQL Server database schema, you get many of the features available in Visual Studio that .NET developers have also enjoyed such as refactoring and renaming all with Intellisense coding for T-SQL:

Now, when you code your application, you can build the schema directly with Entity Framework and also have the option of keeping the EF model synched up with the database model should you modify the schema directly from Visual Studio instead:

This is all available to you now as part of CTP3 of Denali, which you can download and try out here. This tight integration with EF and Visual Studio 2010 for database projects is what was called “Project Juneau”. I suggest that you go here to learn much, much more about SSDT and Project Juneau. Best, Mark


Identity Gap with SQL Sever Denali Sequence Objects

With my DBA & developer lineage being in both Oracle PL/SQL as well as SQL Server T-SQL, I have been a big fan of Microsoft adding the Sequence object to SQL Server. You can find this new feature in the current public beta of SQL Server v-next, code named “Denali”. Download it here from Microsoft.com.

You can search through my MSSQLDUDE archives to find a few things that I’ve written about Sequences and how they can act as a good replacement for Identity columns. In those other postings, I touch on some of the things to thing about and re-architect since Sequence objects are detached from specific tables and do not have the Identity and Scope_Identity issues.

One thing that I tested which seems to still exist in  Sequence objects, at least in CTP1 of Denali, is the “identity gap” that currently occurs in SQL Server Identity columns when you insert into a table with a column that has the identity property, but the insert fails. This condition is explain very well, very concisely and (what I like about it) in under 2 mins at SQL Share!

I tried the same technique with a Sequence object in Denali and got the same thing (screenshot below). The sequence object is incremented by 1 and then the insert fails because of a unique constraint on the table. But because the sequence was incremented first, before the actual insert, I have a gap in my sequence. This is not necessarily a big problem, just something to watch out for, as you most likely already are aware of with Identity columns.

This happens even if I moved the ID field in the table definition above to the end of the table row definition.

Where SQL Server & SQL Azure Converge

Throughout 2011, with each new Microsoft CTP release of SQL Server Denali, updates to SQL Azure and service packs for SQL Server 2008 R2, you are going to see more & more convergence between the traditional on-premises database and the cloud database world, SQL Azure. Add to this mix, the advancements coming from Redmond this year with AppFabric and Windows Azure and you are starting to see these worlds blend together into the goal of optimized and effective IT data centers and workgroups where you seamlessly move data and applications between bare metal to private cloud to public cloud.

Ok, let’s slow down for a second and be realistic about this. We’re not there yet. In fact, each of those Microsoft iniatives mentioned above has separate product teams, release cycles and release mechanisms. But I did want to point out a few areas where you can get started to be ahead of the curve during this very exciting time in Cloud computing. We are nearing an inflexion point where traditional on-premises practices are moving over to Cloud. In our SQL Server land, we have 3 primary ways to begin this transition:

  1. SQL Azure Data Migration Wizard
  2. Data Sync Framework
  3. Data-Tier Applications

SQL Azure Data Migration Wizard

This is a free download tool from Codeplex (http://sqlazuremw.codeplex.com) and it is a very simple, easy-to-use and intuitive utility. It is a tool for engineers, so don’t look for fancy GUIs and Silverlight. But it does an awesome job of migrating your on-premises SQL Server database objects and data to SQL Azure. It handles a lot of the migration steps that you would need to do manually such as identifying unsupported SQL Azure objects and code as well as things like adding clustered indexes on every table (a current SQL Azure requirement). This is a great stop-gap tool to use until Data Sync is ready for prime time …

SQL Azure Data Sync

This is the Microsoft-sanctioned go-forward way to replicate data between on-prem SQL Server and SQL Azure, as well as scheduling data synchronization and replication across Azure data centers and SQL Azure databases. The problem is that the current publicly available version (https://datasync.sqlazurelabs.com) does not sync to or from SQL Server on-premises yet and the new version is still in CTP (beta) which you can sign-up for here.

SQL Server Data-Tier Application

This is functionality that is built into SQL Server 2008 R2 that allows developers and administrators to move units of code built in SQL Server databases, around instances and through a development lifecycle (i.e. dev, test, stage, prod). This is available today and you can deploy SQL Server database schemas that you’ve developed from Visual Studio 2010 to SQL Server 2008 R2 on-prem or to the cloud in SQL Azure.

Joe D’Antoni (Philly SQL Server UG VP) and I are presenting on all of these techniques at this Saturday’s SQL Saturday Philadelphia in Ft. Washington, PA (http://www.sqlsaturday.com). Our presentation material is available for you to view here.

Think About Replacing Uniqueidentifier with Sequence in Denali

As I’ve finally been finding some time recently to play with CTP 1 of SQL Server Denali, I’ve focused a lot of my energy around the Sequence object. This is because I have an Oracle PL/SQL background and used Sequences quite a bit in the old days there.

When talking to SQL Server DBAs and developers about Sequences, the conversation often starts with “this is pretty cool” and something that seems interesting on a forward-looking basis. I mean, in reality, who wants to go back into code and data models and make changes to tables, right? So is there a good case to use the Sequence to replace a table-based ID field like an Identity column or uniqueidentifier?

So far, I’m thinking that uniqueidentifier will be something that you’ll want to look at. The 2 most common constructs in T-SQL for creating ID fields in tables is to use either a uniqueidentifier as the column data type or use an INT with the identity property. The identity property in current SQL Server versions is probably the closest correlation to the new Denali sequence object. But the sequence object nicely encapsulates the IDs away from being tied to any single table or object. I describe this usage futher here.

Certainly you can go back and replace your IDENTITY columns easily with sequence objects because the INT data type is a natural fit for sequence #s. However, removing the identity property is not easy. Threre is no ALTER statement to do it nice & easy for you. And even though the SSMS GUI supports removing the identity property from a table column specification, the T-SQL that is generated from that action is actually the creation of a COPY of your table and data, drop the original table and re-create a new table without the identity definition! That’s not fun and not good, especially if you have a very large table.

Replacing the GUID field in a UNIQUEIDENTIFIER is not easy, either. You will want to remove that field and replace it with an INT using sequence. But this can only be done if you do not have that field as a foreign key in other tables. However, if you can replace it, do so, especially if you are using the GUID for these purposes: because it has guaranteed uniqueness and you are using it as a clustered index key.

Sequence will be a better option in those instances. The Sequence object has its own T-SQL to manage the #s being fed into your INT field to help with uniqueness when moving the data between instances and the sequential INT value of sequence is a MUCH better clustered key than a GUID. The control that you can have over the sequence separate from the table definition should help eliminate some of the limitations that may have led you to use the uniqueidentifier.

What Makes SQL Azure Compelling? SQL Developer Edition …

In part 1 of “What Makes SQL Azure Complelling?”, I focused on the DBA role and what is good & what is different with SQL Azure from SQL Server.

Now let’s talk about the SQL developer role.

The DBA role is only partially transparent from SQL Server to Azure and in some ways simpler, in other ways limiting and just plain different. For developers, the change will be less intrusive, but have a number of limitations.

One of the unmistakeable benefits of the Platform as a Service (PaaS) approach of Windows Azure & SQL Azure is that you can airlift your applications & databases to the Cloud with minimal impact and changes to code. In fact, with an application that you have written that connects to SQL Server via ODBC, all you have to do is change the connection string to the Azure connection string and everything will work just fine. The Windows Azure management screen even gives you a place to copy the connection string:

There are a few steps you need to follow first. You need to get your database from your traditional on-premises SQL Server database into SQL Azure. To do this, I typically use the SQL Azure Data Migration Wizard from Codeplex which you can download free here. It’s a great tool, very effective, simple and straight-forward. Microsoft is completing a CTP 2 of Data Sync for SQL Azure that will allow you to automate moving data around from SQL Azure to different data centers and also on-prem SQL Server that will operate similar to SQL Server replication, which is currently not supported in SQL Azure.

Next, you will need to make changes to your applications that may be required due to unsupported features from SQL Server in SQL Azure. Here is a complete list for application developers. And here is my list of common gotchas when converting applications from SQL Server to SQL Azure:

  1. Replication is not supported
  2. No support for CLR
  3. Partitioning is not support
  4. No Service Broker
  6. No Fulltext Search
  7. No Sparse Columns