Hi All. This is a quick post to introduce you to SQL Server Data Tools – support and tools for database developers.
Introduction
Recently, I started a new solution in Visual Studio 2010. There is a need to build and maintain a database schema (for SQL Server 2008 R2), so I decided to add what was once formerly known as “DataDude” – the Database Project for Visual Studio.
This was in a copy of Visual Studio Professional 2010 with Service Pack 1 – and the out-of-the-box solution only supported SQL Server 2008 and prior. A bit surprised, I did some digging. You’ll recall there was a ‘Database Edition GDR’ which came out a few years back.. well there’s now an even better flavour of support.
Introducing SQL Server Data Tools
It’s called ‘Microsoft SQL Server Data Tools’ and you can get a copy from the following link on MSDN. There are quite a number of new bits and pieces included, and it works with both Visual Studio 2010 (alert: apply Service Pack 1 beforehand) or Visual Studio 2012 (RC – although with some known issues if upgrading from, the beta).
Although the installation takes a little while (depending on your connection speed), the wait is worthwhile.
I’ll borrow some text from the MSDN site in order to explain the purpose of SSDT:
Who is SSDT for, and what does it provide them?
SSDT is for SQL Server database developers, who often develop database schemas, views, stored procedures, and other database objects while developing their application logic.
- Tooling for both SQL Server and SQL Azure Development: SSDT offers new capabilities in a single cohesive environment to compile, refactor, and deploy databases to specific editions of SQL Server and SQL Azure. The toolset makes it easy, for example, to migrate on-premise SQL Server schemas to the cloud on SQL Azure, and develop and maintain databases across both on premise and cloud deployments. SSDT can target SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure databases, including all editions of these database servers.
- For SQL Server DBAs: SSDT provides a central and unified toolset targeted to the specific needs of DBAs to develop and maintain databases, with visual tools for developing tables, schema compare, and rich T-SQL support for refactoring databases, building views, stored procedures, functions and triggers. The toolset provides both a live development mode, and an offline project mode that tracks and manages all artifacts associated with a database. This mode optionally fully integrates with Visual Studio 2010 for team development, source control and change tracking. All change operations are automatically transformed into optimized T-SQL alter scripts, and can optionally be applied immediately to the online database or saved for later execution.
It’s actually very easy to use.
Real World Applications
For example, if you open a solution containing legacy database projects the tools will automatically prompt you as to whether you wish to upgrade your existing database projects to the newer edition.
The basic benefit is targeting SQL Server 2008 (and R2) as well as SQL Server 2012 and SQL Azure. That last part might get your attention! That’s right – SQL Azure. We’ll be checking this out soon and reporting back in a bit more detail.
How Do I get SSDT?
You can download the “pre-installer” here.
For more information, check out the ‘Getting Started with SQL Data Tools’ located here.. or stay tuned at this location for more!
But wait.. there’s a bug?
Today I got into full swing with the SSDT and a real world project. During my work I came across a fairly horrible bug which has been documented on the MSDN Forums.
Basically, if you initialize (i.e. use) any of the SSDT tools you can’t use the Entity Framework tools and vice-versa. The interim workaround is to load two instances of Visual Studio (perhaps even the same solution?) and use the SSDT tools in the first instance, and the Entity Framework tools in the other.
A symptom which might lead you to this article? When you have the SQL Server Data Tools installed, and try to update or create an Entity Framework data model – you may receive errors such as ‘Object not set’ etc. In some cases it might crash the VS IDE. In my experience, I received the ‘object not set’ errors, and the Model Explorer was greyed/did not render. I was also not able to refresh my EDMX properly.
Whilst being a right royal pain in the butt, believe it or not this approach does work – even if it does make for a very disjointed development experience. According to the thread, a fix is in the works – but no word on when it will be released.
