BI Future Blog: Some features of SQL Server Data Tools

Introduction

Today a post about SSDT (SQL Server Data Tools) and this tool is the replacement of BIDS and Database Developer (or Data Dude). There are two tastes of SSDT: SSDT-BI and SSDT-DB. Both have to be installed seperately and that's very confusing. But once you know what's going on there are no problems anymore. SSDT-DB is the evolution of "Visual Studio Team System Edition for Database Professionals" and this project was the baby of Gert E. Drapers that gave it its more common name, "Data Dude".

There seems multiple versions of SSDT around and I've found these versions of SSDT of the last years.

2014 January update.
2014 July update.
2015 February update.

There are two versions available:

Visual Studio 2012.
Visual Studio 2013.

The ultimate goal of SSDT is to create a more integrated development environment. Traditionally, database development has often been done outside of Visual Studio, for instance in SQL Server Management Studio. With SSDT, database development is instead done inside Visual Studio. Databases are included as projects in the Visual Studio solutions.

What is SSDT?

This tool is the single environment for writing DB code as well as BI development. There is no need to switch between SSMS and SSDT. You can write all the code in Visual Studio 2012 or 2013. There is now a clear distinction between a developer environment and a DBA environment. SSMS is more about managing SQL Server and databases. There are two modes possible:

Disconneted mode.
Connected mode

On premise.
Off premise.

There is a local database for developers and this comes in handy because developers don't need a connection to a live database. so you write code in a declarative manner and you don't need to worry(?!) about implementing these chnges on a database.

Normally, until you had SSDT, you would write code with SSMS in a development environment. When you needed to deploy the code to another environment you had to remember or compare the versions with TFS and then create change scripts for the other environment. With SSDT you don't need that anymore. SSDT does that for you.

Options

There are multiple ways to start a project with SSDT:

Start from scratch with a project in SSDT
Import the code from a current database into SSDT project.
Import from a .dacpac file
Import a from scriptfiles.

In the following tutorial I will show some simple steps importing the code from an existing database into a project in SSDT. This is the DB part of SSDT.

Import the code from a current database into SSDT project

1. First start SSDT and start a new project and you'll see the following window. Here you can see the distinction between BI and DB.

2. Export the Data-tier Application from the database by right clicking on the specific database, press Tasks and Export Data-tier Application.

3. Press Next.

4. Save the file to a local disk by clicking on Browse

5. Choose the location and press Save.

6. Press Next.

7. Choose the database objects and press Next

8. Now we have the summary window. Press Finish.

9. Errors appears and why is that?

There seems a lot of errors in export of the database to the bacpac file, unfortunately. The errors are static and you can do anything about it. Below, I copied the most common errors that apeared in the error list

The element Extended Property: [dbo].[ufnGetCustomerInformation].[@CustomerID].[MS_Description]

The element Extended Property: [SalesLT].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID].[MS_Description] is not supported

The element XML Schema Collection: [SalesLT].[ProductDescriptionSchemaCollection] is not supported

Element Column: [SalesLT].[Address].[AddressID] has an unsupported property IdentityIsNotForReplication

 Element Column: [SalesLT].[Address].[rowguid] has an unsupported property IsRowGuidColumn set 

Table Table: [dbo].[BuildVersion] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.

Element User: [NT AUTHORITY\NETWORK SERVICE] has an unsupported property AuthenticationType set and is not supported when used as part of a data package.

When searching the internet I found this helpful article on SQL Server Data Tools Team Blog. It seems that there is another way of importing the database into SSDT-DB. And that is with SSDT-DB itself.

10. Select the specific database and right click on Create New Project

11. Set the Target name and click on Start

12. The export is running

13. And, the project is created based on the AdventureWorks2012LT database.

Refactor the database

Refactoring the database is one of the things that needs to be done. Objects needs to be changed when the world around us changes and database needs to evolve with that. Let's try to refactor a field in table in the AdventureWorksLT2012 database.

14. Let's try to change the CompanyName in the Customer script to Company and try to publish this to the database. We do this by right clicking on the CompanyName and press Refactor. Change the name to Company.

15. Here you can see the changes and the press Apply

16. The next thing is to publish the changes to the database. You do this by right clicking on the Project and press Publish.

17. After some time of processing the changes are processed and the database is in the new state.

Snapshot the project

It's also possible to snapshot your project in dacpac files. These files contains your scripts saved at a certain point in time. This will help you save the projects at important points in time.

18. Right click on the project and click on Snapshot Project.

19. And the snapshot is saved under Snapshots.

Using References

If your database references other databases, you'll need a dacpac file for each external database referenced in your code. References can be resolved for objects in one of the following locations:

In the referencing database.
In a database other than the referencing database, but on the same server.
In a database other than the referencing database, on a different server.

In this blogpost, I've choosed to use "In a database other than the referencing database, but on the same server".

Suppose you have a stored procedure in a project that references a table in an other database (e.g. TestDB). Then the following error is shown in SSDT. You need to create a Database reference to the project.

22. Add a Database Reference to the project by right clicking References and Add Database Reference.

23. First extact a dacpac from the database in SSMS and store that on a central place and press Next.

24. Select that dacpac file from the central place in your project.

25. Replace the database name by the Database Reference.

25. Now if you want to publish this to the database, the values of the Database Reference can be set.

Here you can set the value for the Database reference.

Conclusion

SSDT is logical evolution in Database and SSIS, SSRS and SSAS design. I'm very happy with the development environment for SQL Server where you have an integrated approach. There is a clear distinction between DBA and Developer work.

Other references

The following links I've found so far about SSDT:

BI Future Blog

dinsdag 14 april 2015

Some features of SQL Server Data Tools