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


SQL Server Denali: Which Features Work on Windows Server Core?

I cannot tell you how many times that DBAs have asked me when SQL Server will support Windows Server Core version of the OS. With Server Core, you essentially interact with the OS similar to Linux or Unix with a shell prompt as opposed to the UI-centric model that is familiar to Windows users. This greatly minimizes the server attack surface and also can greatly reduce required patching. These are HUGE advantages for SQL Server DBAs.

SQL Server 2008 R2 does not support installing on Windows Server Core. However, the next version (Denali) of SQL Server will. I just want to point you to a very helpful posting on BOL that gives you guidance on what features of SQL Server will NOT function when installed on Server Core. The link is here. Note that most GUI-centric tools are not available. But the services outside of the core SQL Server engine that most operations require ARE available like Integration Services and Analysis Services.

SQL Server Denali Deprecated Features List

I’ve fielded a few questions this week about the updated list of features that will be removed from SQL Server with the Denali release and that will be deprecated as well. So I thought I’d just put up a post with the MSDN link so that you can bookmark this and check back for updates as we move closer to the GA release: I would imagine that this list will evolve as the next CTPs are made available.

SQL Server Maximum Availability: Denali & Private Cloud ?

As I am working my way through the latest updates in the betas of SQL Server Denali, I’ve been thinking about different architectures to maximize database and application availability with the new SQL Server platform. It is important to note that one the most hyped, popular and intriguing architectures for database and applications, both from my interactions with customers and Redmond, is utilizing what we call “Private Cloud”.

I’ve written about a SQL Server private cloud before in this blog and I often refer to it as “optimized data center” for customers. The reason for this is that for some, it can be confusing when software vendors are talking about public clouds like Azure, Amazon, RightScale, etc. or virutalized local environments in your data center. Private cloud is taking the concepts of Public Cloud like elastic scale, metered billing, self-provisioning, consolidated servers, maximum utilization of infrastructure to maximize, ROI, etc. and making those concepts viable in your own data centers.

SQL Server Denali introduces an update to database mirroring that combines the Microsoft technologies of mirroring, clustering and replication into a very simple easy-to-configure and maintain set of “availability groups”. With Denali, you can take databases that are part of an application, put them into this availability group and even have multiple replicas (aka database mirroring with multiple read-only partners) that you can report from or back-up from.

My thinking is that combining these 2 technologies will give the SQL Server DBA community 2 key things that were either not available before in SQL Server or are better now with this architecture:

  1. Combining applications into availability groups that allow me to move reporting and backups completely off the primary production database and to the replicas.
  2. Create a SQL Server farm where I can use the Microsoft System Center suite for server load balance, full server density, Live Migration for zero downtime patching and managing my instances as SQL Server virtual machines.

The slide below is from a presentation that you can download from the Microsoft SQL Server private cloud link that I included below. This architecture works with SQL Server Denali since I would configure availability groups for my application databases using AlwaysOn database replicas, which require Windows Cluster Server as shown below. The virtualization command and control software (Hyper-V and VMM in this case) will allow me generate a SQL Server farm where I can move the instances around from server to server, using Live Migration. So while HADR provides the tools for protection against failures and guest-level patching, Live Migration and VMM give me the ability to load balance, utilize full server resources and move running instances from server to server with no downtime. The assumption in this architecture, BTW, is that I am running a single SQL Server instance per VM, so that when I say “move an instance” in this grid architecture, I am moving a full guest OS VM.

Go here for more details on SQL Server Denali. And here is the Microsoft SQL Server private cloud home page.

Your thoughts and suggestions are very much welcome in the comments. Thanks! Mark

UPDATE: The SQLOS Team blog on SQLBlog added an excellent post that you should bookmark for SQL Server Private Cloud background info:

June 8 Philly SQL Server User Group Meeting

The June Philly SQL Server User Group meeting will be back here in the Malvern Microsoft office at 45 Liberty Blvd. Click here to sign-up to register and attend. You can also go to the Philly SQL Server User Group PASS home page here to learn more about the local chapter of the Professional Association of  SQL Server and become a member.

Attending the meeting next Wednesday is free and you’ll learn about SQL Azure, SQL Server Denali, XML Query Plans and Violin’s flash storage technology.

See ya there! Thanks, 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

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.