SSDT

SSDT: Unable to connect to master or target server.

  • Posted on: 12 June 2017
  • By: Ed Elliott

error in sssdt: Unable to connect to master or target server. the server displayed isn't the server but the database

Every now and then I come across this error in SSDT, normally when trying to publish and the odd thing is that the description never matches the actual cause (if you desperate for the cause it is because you can't connect). The thing I like about the description is the way it tries to tell you what is wrong and what server you are connecting to but it fails at both and instead tells you about an unrelated error and a database name instead of a server name.

What we have is:

"Unable to connect to master or target server '{0}'. You must have a user with the same password in master or target server '{0}'.\"

Twice it tries to tell you the server name but both time it actually tells you the database name. I thought I would dig into it a little with reflector to try and see where the error is coming from and whether or not it would ever show the servername. So in reflector I found the error and found where it was used. If we look in, what is surely to be everyone's favourite ssdt dll Microsoft.Data.Tools.Schema.Sql.dll, we can see that the error message is used in SqlDeploymentEndpointServer.OnInit and we have something like:


catch (ExtensibilityException exception)
{
Tracer.TraceException(TraceEventType.Verbose, TraceId.CoreServices, exception, "Error loading DSP families");
throw new DeploymentFailedException(string.Format(CultureInfo.CurrentCulture, DeploymentResources.InvalidServerEndpoint, new object[] { targetDBName }), exception);
}

they are indeed passing targetDBName into the InvalidServerEndpoint error message so yes indeed the error message will only ever show the database name.

I had a quick look at what can cause this and it is something to do with opening the SqlConnection which is wrapped in lots of retry logic that is different for Azure compared to other types of SQL - lots of interesting stuff maybe for another post but basically SSDT wasn't able to open a connection - check the server name, ports, database etc (i.e. maybe your default database is not available to that user), connect via ssms then when it works there come back to ssdt.

Footnote, I did think about raising a connect but then couldn't be bothered, if anyone does i'll be happy to vote for it!

SqlPackage Deploy Performance - IgnoreXX are not your friend!

  • Posted on: 2 March 2017
  • By: Ed Elliott

Following on from yesterdays blog I was wondering about the comparison of objects that were the same and how the IgnoreWhitespace, IgnoreComments, IgnoreKeywordCasing and IgnoreSemiColonsBetweenStatements flags affected the comparison. To be fair I was only interested in IgnoreWhitespace but actually it turns out that those four are very closely related.

When the deploy happens, where a script in the source and target are compared the process is:

  • 1. Loads of things we will skip
  • 2. Any cmd variables in the scripts are replaced with their appropriate values
  • 3. If both the scripts are null - the comparison returns true. This has to be the best for performance but the worse for functionality ;)
  • 4. If one script is null but not the other then the comparison returns false. This actually has to be the best for comparison performance but worse for deploy performance!
  • 5. We then get a good old fashioned String.Equals, the standard .net compare goes: if both strings are not null and the lengths are the same do a check on each byte in the strings
  • 6. If the strings are equal we have a match, happy days no more action required

It is what happens if the strings do not match that it starts to get a bit more interesting, if the strings are not equal and any of those four ignore options are True then we then fall down into doing a further comparison but after the scripts have been normalized using the script dom and antlr which is an expensive operation in itself (this also happens to be my next topic!).

Once the normalization has been done we end up in the actual compare which goes like this:

  • 1. Turn the script into a stream of tokens
  • 2. If the token is a comment and ignore comments is set, skip it
  • 3. If the token is whitespace and ignore whitespace is set, skip it
  • 4. If the token is a semi-colon and ignore semi-colon's is set, skip it
  • 5. Then compare the tokens which itself does things like use IgnoreKeywordCasing and removes quotes around quoted identifiers - it isn't a straightforward String.Equals
  • 6. If any of the tokens don't match then it is a failure and the script needs to be changed

So what?

So blunt. Anyway, basically what this means is that the default options in the sqlpackage.exe are set to allow things like different cased keywords and whitespace and to allow that we end up taking longer to do deployments where we actually make use of the default features.

huh?

If you have a database with lots of code and you have the code in SSDT but you do things like change the comments when you deploy and rely on IgnoreComments (this is a real life secenario I have seen, someone adding a custom comment header) then you will have slower deployments and as slower deployments are the opposite of what we want you should:

  • Have the same code in your database as you have in your project
  • Have the same code, including the same case of keywords, comments and whitespace in your database that you have in your project
  • Disable the defaults and set IgnoreWhitespace, IgnoreComments, IgnoreKeywordCasing and IgnoreSemiColonsBetweenStatements all to false

What effect does it have?

If your database a project code are exactly the same, then no effect you neither gain nor lose anything.

If your database and code are different by comments, case, semi-colons etc and you have lots of files that are different then you will gain quite a bit. On my machine here I created a database with 1,000 stored procedures like "select '----'" (I used replicate to make it large) I then imported the procs into SSDT and added a space between the select and the text and did a deploy using sqlpackage (in fact I did a few to get an average time), with the default IgnoreWhitespace=true the deploy took about 24 seconds (remember this is on a machine following yesterdays recommendations. lots of memory, fast CPU and SSD) - when I removed the defaults and set them to false - firstly the deploy took 34 seconds because naturally it had to deploy the procs then re-running it took around 17 seconds - about 7 seconds from a 24 second deploy which i'll take.

The thing that you will really gain is that your project code and database will be the same which should really be the end goal, if you can honestly say that you have to have:

  • Different whitespace
  • Different keyword casing
  • Different semi-colons
  • Different comments

I would be inclined to find out why as it sounds like an interesting project :)

SSDT Deploy / Publish Performance

  • Posted on: 1 March 2017
  • By: Ed Elliott

Publishing dacpac's is a little bit of a pain when you have multiple databases, it can easily start to take minutes to hours to deploy changes depending on how many databases and the size of those databases. I wanted to understand more about the publish process and what we can do to speed it up as much as possible so I did some digging and this is a randomish post about how it all works and what we can do to make it faster.

Process Overview

Roughly speaking the process for deploying dacpacs is:

  • 1. Open dacpac (zip file)
  • 2. Open database
  • 3. Compare dacpac to database
  • 4. Come up with a list of changes
  • 5. Generate the T-SQL for the changes
  • 6. Run the deploy script

1. Open the dacpac (zip file)

The dacpac contains the xml representation of the model of the database and the original source code. To be able to deploy changes we need the original source code - the model contains the definitions of the objects such as which columns the table has and the parameters a stored procedure has but not the body of the stored procedure including comments.

The dacpac is a zip file, this means to read from it the deployment must create a file handle to the dacpac and uncompress anything it wants to read (or compress anything it wants to write), the larger the dacpac logically the longer it will take to decompress and also read from disk so there are our first two important things to note, the deploy will need:

  • CPU to decompress the contents of the dacpac
  • Fast disk as it has to read from disk

When you run the publish, by default the model is read from disk into memory, however, for very large dacpac's you might find that you don't have enough memory for it - a windows system with low memory is bad, two things happen:

  • The paging file is used
  • Applications are told to free memory

This is painful for an application and when you get paging, especially on non-ssd drives, you are going to find everything about the whole system slow.

If you have a large model in your dacpac and you don't have much memory then you will probably need to load the model from disk rather than into memory, but you best make sure you have an ssd to run it from!

For more details on how to do that see: https://redphoenix.me/2013/06/14/why-is-sqlpackage-using-all-the-build-s... (Richie Lee FTW!)

2. Open database

The database contains all sorts of goodies like the version that will be deployed to, the database settings and the definitions that will be compared to the dacpac to see what changes need to be made. If your SQL Server responds slowly then the publish will be affected by that so make sure your SQL Server box is up to scratch and expect slower deploy times on resource limited instances.

3. Compare dacpac to database

I had always wondered how they compared the two different objects so I bust out reflector and used it to have a poke about. What happens is a model is build of the source and target - a model is a representation of each object, it has its name, its properties and relationships to other objects for example a table has a relationship to its columns and each column has a relationship to its type (not a property of the type).

If you think about this it means, for each deploy we actually need to store the model of both the database and the dacpac so doubling the size requirements - this isn't to say an exact double of the size of the source code becauuse what it compares is things like the object properties and relationships but also the tokens that make up the body of the object, so there are a lot more objects that are created around the code. In short if your source code is 100 mb you will need some multiple of 2 * 100mb to upgrade an existing database - I am not sure if there are any figures to show what that multiple is but if you do a publish and you run low on memory or you get lots of garbage collection in your deploy then consider either storing the model on disk or throwing in some more memory.

There are a couple of interesting extra things that happen, in particular to do with the size of code in objects and how much to store in memory, to store in memory compressed or to write to disk. If for example you had a stored proc that was less that 500 characters long, the publish will store it in memory.

If the string representation of a procedure was over 500 characters but less than 16 thousand characters the it will be stored in memory but compressed first.

Finally if the stored proc (I am using procs as a example but I think it is any code unit minus the create + name so everything after "as") is over 16 thousand characters then a temp file is generated in your user temp folder and the contents are written to that.

So three different behaviours depending on the size of your objects:

  • Under 500 chars - you need memory
  • Over 500 chars, under 16,000 you need memory and CPU
  • Over 16,000 chars you need memory and fast disk for your temp folder

I created three demo databases and deployed them over and over, the first contains a couple of hundred procedures with slightly under 500 characters, the second with slightly over 500 characters and the last with slightly over 16,000 characters.

When I ran the publish this is what they looked like:

Under 500 char procs:


under 500 chars procs, fastest to deploy

Slightly over 500 char procs:

just over 500 chars procs, similar to under 500 chars but includes some time to compress some strings

What we can see here is that the under 500 char procs, takes the least amount of time but the interesting thing is the database with slightly over 500 chars procs has a little bit of extra umpf from the CPU - I have showed this by using the pink circle - both deploys follow the same pattern for sqlpackage.exe CPU usage - it goes from around 90% cpu up to 100% and then drops down sharply to around 80% and then back up to 90% but the second project with the slightly larger procs - also has an extra splurge of CPU which I am putting down to the extra compression that those strings are getting - it certainly tallies up, even if it is not scientific :)

Aside from the extra CPU usage they are pretty similar, the black highlighted line is garbage collection and the way this counter works is every time it changes, it is doing some garbage collection - the first version does I think 3 collections and the seconds does 4 which sort of makes sense as once the strings has been compressed the original strings can be thrown away.

It is when we start to look at the database with lots of objects over 16,000 characters long we see some interesting things and some other forces come into play:

over 1600 chars procs, lots of disk activity

The first thing to note is the garbage collection, there are like 8 changes to the line so 8 sets of garbage collection that happen which is expensive for a .net app. We get much more processor usage for a more prolonged time and we really start to see some disk activity, write activity just has one peak while the files are written but reading stays quite high throughout the whole process. It could be that it is sql reading from disk (my demo was from a single ssd with a local sql instance) but we can see after the sql cpu settles down the green disk read line stays high so it is unlikely to be pure sqlserver.exe.

What does this tell us?

Well if you have large objects, multi-thousand line stored procs then you better get yourself some serious ssd's to deploy from. If you have smaller objects then you'll need CPU and memory - don't scrimp here!

4. Come up with a list of changes

So when the properties and tokens in the source / destination object have been compared they are added to a list of modifications - this is fairly straight forward but that is not the final list of modifications because there are lots of options to customize the deployment such as ignoring certain things - what happens next is that there is some post-processing done on the list to remove anything that the options say should be removed.

We then get the opportunity to interrupt the deploy process with a contributor and remove steps as we want - if we do that anything we do is overhead so be warned, be fast in what you do or slow down the whole process!

There are some things like default constraints, these are stored differently in SQL Server to our project - so when a difference is found, as part of the post-processing each constraint is "Normalized" using the script dom and a visitor to find out if the changes are actually different or just appear different - this means that we don't keep getting constraints deployed over and over (a relatively new feature as this was something that used to happen a lot - a great new feature added by the SSDT BTW!) but it does mean for every default constraint we need to go through this normalization using the script dom which isn't exactly lightening fast.

Takeaways from this are that the deploy does a lot of things to help us - if it was a straight string comparison then it would be faster but the deploy options would not be as configurable. The more objects you have, the longer the deploy will take!

5. Generate the T-SQL for the changes

6. Run the deploy script

I'm going to stop this post here - I think these are a bit more straight forward, the more things you have to change, the more time it will take :)

Summary

If the deploy is slow, use perfmon measure the cpu, disk and garbage collection of sqlpackage.exe (or whatever is doing the deploy) - have a think about whether streaming the model from disk would be better and if you are CPU bound add a more powerful CPU, if you are low on memory, add more memory and if you are on an old slow disk - put it on an ssd.

If you are low on CPU you might be tempted to add more CPU's - understand that a single deploy happens in serial so adding more CPU's won't make a deploy quicker if this is the only thing running on a box - if it is fighting for resources then more CPUs may help.

SSDT and Friends - .net meetup video

  • Posted on: 10 February 2017
  • By: Ed Elliott

I did a talk at the london .net meetup if you want to get an overview of what SSDT is and how to get started then I would recommend it:

https://skillsmatter.com/skillscasts/9274-londondot-net-january-meetup

This was aimed at .net developers rather than DBA's so there isn't much talk about "why you should use source control" etc as everyone in the room used source control already :)

Refactoring in SQL Server Data Tools - SSDT

  • Posted on: 27 September 2016
  • By: Ed Elliott

In this post I will talk about the in-built refactoring support in SSDT – the language is slightly different from my normal style as originally it was going to be published else but rest assured it is written by myself

What is refactoring?

In programming , the term ‘refactoring’ essentially means taking some code and improving it without adding features and without breaking the code. When we refactor code we ideally want to make small improvements over time, using an IDE that automates as many of the tasks as possible for us.

While SQL Server Data Tools (SSDT) cannot guarantee that we do not break any code it helps us make small improvements and, as an IDE, it offer us some refactoring abilities that do not exist in either SQL Server Management Studio (SSMS) or Notepad.

Just so we don’t get distracted by some of the third-party add-ins that give more comprehensive support for refactoring in SSDT or SSMS, this article will talk only about what is out-of-the-box with SSDT.

What refactoring support does SSDT have?

SSDT helps us to refactor code by automating the actions of:

  • Expanding wildcards
  • Fully qualifying object names
  • Moving objects to a different schema
  • Renaming objects

Aside from this list SSDT also, of course, helps us to refactor code manually with its general editing facilities.

Expand Wildcards

SSDT allows you to highlight a “*" from a SELECT statement and have it replace the “*" with a comma-delimited list of the column names in the table. As an example if we take these table definitions:


CREATE SCHEMA hr
GO
CREATE TABLE dbo.person
( person_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
first_nmae VARCHAR(25) NOT NULL, --typo is on purpose!
last_name VARCHAR(25) NOT NULL
)

CREATE TABLE hr.departments
( id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow VARCHAR(25) NOT NULL
)

CREATE TABLE hr.department
( person_id INT,
department_id INT
)

And the following stored procedures:


CREATE PROCEDURE hr.get_employee ( @employee_id INT )
AS
SELECT *
FROM person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;
GO
CREATE PROCEDURE prod_test
AS
EXEC hr.get_employee 1480;
GO

To improve this code the first thing we will do is to change the “SELECT *" in the hr.get_employee procedure to specify just those columns that we need. If we open the stored procedure in SSDT and right click the “*" we can choose ‘refactor’ and then ‘expand wildcards’:

We are then given a preview of what will change, and we can either cancel or accept this:

Now that we have the actual columns, we can remove the ones we do not need, save and check-in our changes. The procedure should look like:


CREATE PROCEDURE hr.get_employee ( @employee_id INT )
AS
SELECT [p].[first_nmae], [p].[last_name], [deps].[funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow]
FROM person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;
GO

Fully qualify object names

We need to make sure that our SQL Code uses fully qualified object names. This is because …. In this example, the get_employee stored procedure references the person table without a schema which means that the user must have dbo as their default schema. To fix this we right click anywhere in the stored procedure and choose Refactor and then ‘Fully-qualify names’, we could also use the default shortcut of ctrl+r and then q. Again get a preview window:

If we accept the preview, then we end up with the following code:


CREATE PROCEDURE hr.get_employee ( @employee_id INT )
AS
SELECT select [p].[first_nmae], [p].[last_name], [deps].[funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow]
FROM [dbo].person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;
GO

This doesn’t seem like a massive deal as we could just have written ‘dbo.’ but if we had a more than one to update or a number of different tables then it would have saved more work for us.
The ‘fully qualify object names’ goes further than just table names, it will fill in tables in join statements and also columns where it is needed.

For example if I had the following query:


SELECT first_nmae
FROM [dbo].[person] p
JOIN hr.department d ON department_id = p.person_id

When using the refactoring we are offered the chance to fully qualify first_nmae and the department_id in the join:

If we decided we did not want to apply the refactoring to one or the other we could uncheck them in the dialog and only apply the ones that we actually required.

If we apply both of the ‘refactorings’, we end up with:

SELECT [p].first_nmae
FROM [dbo].[person] p
JOIN hr.department d ON [d].department_id = p.person_id

Move objects to a different schema

We can refactor the schema that an object belongs to. This is a three-stage process that:

  • 1. Changes the schema that the object belongs to
  • 2. Changes all references to the original object to specify the new schema
  • 3. Adds an entry to the refactorlog.refactorlog to help the deployment process

If we want to move the person table from the dbo schema into the hr schema, we can simply right-click the table and then choose ‘refactor’ and then ‘move schema’:

If we look at the preview we can see that, as well as changing the schema on the object itself, it is also going to change the references to the table everywhere else in the database:

This refactoring also adds a new file to our SSDT project, the Refactor.refactorlog file in order to assist in a correct deployment of the change that preserves the data in the table. Inside the refactorlog is some xml:

<?xml version="1.0" encoding="utf-8"?>
<Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
<Operation Name="Move Schema" Key="5df06a6f-936a-4111-a488-efa0c7f66576" ChangeDateTime="11/10/2015 07:10:39">
<Property Name="ElementName" Value="[dbo].[person]" />
<Property Name="ElementType" Value="SqlTable" />
<Property Name="NewSchema" Value="hr" />
<Property Name="IsNewSchemaExternal" Value="False" />
</Operation>
</Operations>
</code>

What this does is to save the fact that an object has changed from one schema to another. SSDT reads the refactorlog when generating a deployment script. Without the refactorlog, SSDT would look at the source dacpac and the target database and drop the table ‘dbo.person’, deleting all its’ data, and create a new empty ‘hr.person’ as they are different objects. Because of the refactorlog, SSDT generates a schema transfer rather than a drop / create:

SSDT stops the change happening again by recording, in the target database, that the refactor key has been run so you could create a new table called hr.people and it would not get transferred as well:

Renaming Objects

The final type of built-in refactoring is to rename objects, this works similar to the move schema object but it allows us to rename any object such as a procedure, table, view or column. SSDT renames all the references for us:

SSDT also adds an entry into the refactorlog:

And finally generates a “sp_rename" as part of the deployment rather than a drop/create:

Renaming objects really becomes pretty simple and safe so you can go though and correct small mistakes like spelling mistakes or consistency mistakes. Without SSDT or another IDE to do it for you it is really difficult to rename objects as part of a quick refactoring session.

Other ways SSDT helps to refactor

Aside from the in-built refactors that SSDT has it helps us to refactor because it allows us to find where we have references to an object. For example if you wanted to add a column to a table but did not know whether there were stored procedures that did a select * from the table and then did something that would be broken by adding a new table you could right click on the table name and do “Find All References":

We can also do the same thing for column names and so we can really easily get a picture of how and where objects are used before we change them.

General advice on refactoring SQL Server databases

Refactoring SQL Server databases is really helped by using SSDT but there are two things that you can do which really give you the freedom to refactor your code as you go along:

  • Use stored procedures / views rather than access tables directly
  • Have a suite of unit/integration tests
  • Use stored procedures / views rather than access tables directly

If you access your data from your application using the base tables it means you cannot change anything in your tables without also changing the application. It also makes it really hard to find references to your objects to know where they are used. Instead you should use stored procedures as an API to expose your data. If you do this then instead of having to manually find references you simply need to find the places where the stored procedure is called.

Have a suite of unit/integration tests

If you have both unit and integration tests then, as well has having a tool that helps you to refactor your code, you also get the confidence of knowing that you can make changes without breaking anything else. Without these test suites it is hard to known whether you have broken that year-end critical task that is so easily forgotten about.

Conclusion

SQL Server Data Tools have the basics of refactoring tools, but it isn’t really what one comes to expect from a SQL IDE. What about a tool to automatically insert a semi-colon after every statement, if there is none? Why is there nothing that changes the case of keywords according to SQL Conventions? One looks in vain for a way of reformatting code correctly. One could be more ambitious and ask for ways of finding variables that aren’t used, splitting tables, encapsulating code within a stored procedure, or checking for invalid objects. Fortunately, there a number of add-ins that fill the gap, and in the next article we take a look at SQL Prompt, which is the leading

tSQLt Visual Studio Test Adapter

  • Posted on: 17 August 2016
  • By: Ed Elliott

tSQLt Visual Studio Test Adapter

What is this?

This lets you use Visual Studio to run tSQLt tests easily. Visual Studio has a built in framework for finding and executing tests so that if you have tSQLt tests in an SSDT project for example, although this just requires you have the .sql files in source control and does not require ssdt - you can easily see and execute your tests via the Visual Studio Test Explorer window. It is also available for use in your favorite build server via the vstest.console.exe tool or if you have vsts then you can use the default test task.

What does it look like?

Two screen shots, the first is the tSQLt sample tests in Visual Studio:

tSQLt tests are shown in the Visual Studio SSDT project

A couple of things to mention, firstly if you double click on the test in the Test Explorer window it will jump you to the test in the code (whoop). Secondly if you run a test and it fails you get a failure messages (it gives you a red cross) and clicking the test shows the tSQLt error message below.

This screen shot is the test running on vsts:

Output of tSQLt tests on vsts

Oooh that looks shiny! You see the step failed because the tests failed (I hadn't actually deployed them whoopsie). You get some nice graphs and to get this all you need to do is add the adapter to the project and configure the "Test Assemblies" task.

How do I use it?

To run tests you will need a .runsettings file and select it using "Test --> Test Settings --> Select Test Settings File" in the runsettings file you will neeed at a minimum the connection string to connect to:

<?xml version="1.0" encoding="utf-8"?>
<RunSettings>
<TestRunParameters>
<Parameter name="TestDatabaseConnectionString" value="server=.;initial catalog=tSQLt_Example;integrated security=sspi" />
<Parameter name="IncludePath" value="AcceleratorTests" />
</TestRunParameters>
</RunSettings>

If you run your tests and get an error with the connection string not being set make sure you actually have the runsettings file attached.

If you have a large project you really will want to limit the processing of tests to just test files so you can add a filter which tells the adapter to only parse files under paths that match a specific regex. To discover tests we need both the test procedure and also the schema that defines the test class so if you use a filter ensure that both are included.

Once you have your run settings, install the Visual Studio extension:

https://visualstudiogallery.msdn.microsoft.com/cba70255-ed97-449c-8c82-e...

If you want to run this on VSTS then you can either download the VSIX, extract all the files and check them into a folder in your solution or use nuget to add the package AgileSQLClub.tSQLtTestAdapter (aka https://www.nuget.org/packages/AgileSQLClub.tSQLtTestAdapter/0.59.0) to your solution. Nuget doesn't support SSDT projects so you will need at least one .net dll which can do nothing at all except reference this package. Once you have the test adapter in your project then configure a test task like:

Configuring the test task on vsts

The things to note here are you can either add a filter to the runsettings file or you can filter which .sql files are passed to the test adapter, you will need to make sure both the schemas and tests are passed in otherwise we can't work out what is a test and what is a stored procedure.

An Oddity

Because of the way the test adapter framwork runs and discovers tests and the way that us sql developers like to seperate our schema's and tests into different files it means I need to add a fake test with the name of the test class if you try to run it you will get a message "Not Run" and it won't do anything but all of the individual tests will work. I tried to make this generic so you don't need SSDT to run and ironically if I had relied on dacpac's it wouldn't have been a problem!

What else?

It isn't the full expression of what I would like to do with this, there are a couple of things I will add in the future but this is a start, these are:

  • Ability to display the result sets rather than just the outcomes
  • In SSDT I would like to deploy any changed objects that are referenced by a test so you make your changes, then run the test and the code is dpeloyed and then run - cool hey!

Minimum Requirements

VS 2015, Update 3 - if you use an earlier version you will get slots of "0 test cases found" messages - if you are desperate for an older version of vs let me know and I will take a look but I am not planning on supporting older versions unless there is a good reason.

Open Source

Of course this is open source, it will be on;

https://github.com/GoEddie/tSQLt-TestAdapter

Any issues shout!

ed

Post Deploy Scripts In Composite Dacpac's not deploying

  • Posted on: 3 March 2016
  • By: Ed Elliott

Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like:

  • Main.dbproj - actual production code and reference data
  • Main.UnitTests.dbproj - unit test code + references to tSQLt etc

The unit tests have a "Same Database" reference pointing to the main project so it is as if my tests were in the same project.

To deploy the unit tests I deploy the unit test project and use option to include composite projects, this then also deploys the production database. When I go to other environments I can deploy the production code without the unit tests. This works really well except when you deploy like this, only the pre/post deploy scripts from the dacpac explicitly being deployed are used. In this case, the scripts in the unit test project are run but not the ones in the main project but when the main project is deployed by itself the scripts are deployed.

To fix this issue what I do is to have an msbuild copy task in the unit test project:

<Target Name="BeforeBuild">
<Message Text="Copying Post Deploy Script" Importance="high"/>
<Copy SourceFiles="$(SolutionDir)\DeployScripts\Deploy\PostDeploy.sql"
DestinationFiles="$(SolutionDir)\DeployScripts.UnitTests\Deploy\PostDeploy.sql" OverwriteReadOnlyFiles="true"/>
</Target>

What this means is that the script from the main project is always copied into the test project and so I know that it is always run.

If you do this you need to ensure that people don't use the script in the test project as the changes are overwritten every build, people don't normally make that mistake more than once!

It is a little clumsy but a pretty simple solution.

There are cases where this doesn't work and more complicated things you can do include:

  • Before you deploy, iterate through all the dacpacs and use the .net packaging api to pull out the pre/post deploy scripts then order them as you want and write them to your main dacpac.
  • Write a deployment contributor to do it but there are challenges

I have put a sample on github:

https://github.com/GoEddie/PostDeployCopy

Hopefully it helps someone else.

Ed

What is SSDT? Part 3 - an API for me, an API for you, an API for everyone!

  • Posted on: 7 January 2016
  • By: Ed Elliott

In the final part of this 3 part series on what SSDT actually is I am going to talk about the documented API. What I mean by documented is that Microsoft have published the specification to it so that it is available to use rather than the documentation is particularly good - I warn you it isn't great but there are some places to get some help and I will point them out to you.

The first parts are available:

https://the.agilesql.club/blogs/Ed-Elliott/2016-01-05/What-Is-SSDT-Why-S...

and

https://the.agilesql.club/blog/Ed-Elliott/2015-01-06/What-Is-SSDT-Part-2...

Same as before, i'll give an overview and some links to more info if there are any :)


what is SSDT? It is a lovely API for winners

Documented API

There are a number of different API's broadly split into two categories the DacFx and the ScriptDom. The DacFx consists of everything in the diagram around the API's circle except the ScriptDom which is separate.

ScriptDom

For me SSDT really sets SQL Server apart from any other RDBMS and makes development so more professional. The main reason is the declarative approach (I realise this can be replicated to some extent) but also because of the API support - name me one other RDBMS or even NoSql system where you get an API to query and modify the language itself, go on think about it for a minute, still thinking?

The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.

The second way it can be used is to take objects and create T-SQL.

I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn't guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table's deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.

What I ended up doing was using the ScriptDom to parse the file and for each statement, (some were merge statements, some straight inserts, some inserts using outer joins back to the original table and an in-memory table) retrieved the name of the table affected and then generating an if exists and begin / end around the table, I also produced a nice little excel document that showed what tables where there and what method was used to setup the data so we could prioritise splitting the statements up and moving them towards merge statements when we had more time.

Doing this manually would have technically been possible but there are so many things to consider when writing a parser it really is not a very reliable thing to do, just consider these different ways to do the same thing:


select 1 a;
select 1 a
select /*hi there*/ 1 a
select * from (select 1 one) a
select 1 as a;
select 1 as a
select /*hi there*/ 1 as a
select 1 as [a];
select 1 as [a]
select /*hi there*/ 1 as a


select * from (select 1 one) a
;with a as (select 1 a) select * from a
;with a as (select 1 as a) select * from a
;with a as (select 1 a) select * from a
;with a(a) as (select 1) select * from a
;with a(a) as (select 1 a) select * from a


select 1 a into #t; select a from #t; drop table #t;
select 1 a into #t; select a a from #t; drop table #t;
select 1 a into #t; select a as a from #t; drop table #t;

I literally got bored thinking of more variations but I am pretty sure I could think of at least 100 ways to get a result set with a single column called a and a single row with a value of 1. If you think that parsing T-SQL is something that is simple then you should give it a go as you will learn a lot (mostly that you should use an API to do it).

One thing that causes some confusion when using the ScriptDom is that to parse any T-SQL unless you just want a stream of tokens you need to use the visitor pattern and implement a class that inherits from TSqlFragmentVisitor - it is really simple to do and you can retrieve all the types of object that you like (CreateProcedure, AlterProcedure etc etc).

So if you have a need to parse T-SQL then use the ScriptDom, it is really simple what is not so simple is the other side of the coin, creating and modifying objects to create T-SQL.

If you need to do this then it is quite hard to work out the exact type of objects you need at the right point, for example if you take this query:


;with a as (select 1 a) select * from a

What you end up with is:

  • SelectStatement that has...
  • a list of CommonTableExpression that has...
  • an ExpressionName which is of type Identitfier with a value of "a"
  • an empty list of Identitiers which are the columns
  • a QueryExpression that is of type QuerySpecification which has...
  • a single LiteralInteger as the expression on a SelectScalarExpression as the only element in a list of SelectElement's
  • the CommonTableExpression has no other specific properties
  • the SelectStatement also has...
  • a QueryExpression that is a QuerySpecification which contains....
  • a list of SelectElement's with one item, a SelectStarExpression
  • a FromClause that has a list of 1 TableReference's which is a NamedTableReference that is...
  • a SchemaObjectName that just has an Object name

If you think that it sounds confusing you would be right, but I do have some help for you in the ScriptDomVisualizer - if you give it a SQL statement it will parse it and show a tree of the objects you will get. If you do anything with the ScriptDom then use this as it will help a lot.

ScriptDom Visualizer V2

https://the.agilesql.club/blog/Ed-Elliott/2015-11-06/Tidying-ScriptDom-V...

Using the TransactSql.ScriptDOM parser to get statement counts

http://blogs.msdn.com/b/arvindsh/archive/2013/04/04/using-the-transactsq...

MSDN forum post and a great demo of how to parse T-SQL from Gert Drapers

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/24fd8fa5-b1af-4...

TSql Model

The TSql Model is a query-able model of all the objects in an SSDT project, their properties and relationships. That sounds like a mouthful but consider this:


create view a_room_with_a_view
as
select column_name from table_name;

If you just have this script without the model you can use the ScriptDom to find that there is a select statement and a table reference and you could probably also work out that there is a column name but how do you know that there is actually a table called table_name or a column on the table called column_name and also that there isn't already a view or other object called a_room_with_a_view? The TSql Model is how you know!

The TSql Model is actually not that easy to parse (there is help so fear not, I will tell you the hard way to do it then show you the easy way). What you do is to load a model from a dacpac (or you can create a brand new empty one if you like) and then query it for objects of specific types or with a specific name or even just all objects.

So imagine you open a dacpac and want to find the a_room_with_a_view view you could do something like:


var model = new TSqlModel(@"C:\path\to\dacpac.dacpac", DacSchemaModelStorageType.File);
var view = model.GetObject(ModelSchema.View, new ObjectIdentifier("a_room_with_a_view"), DacQueryScopes.UserDefined);

If you then wanted to find all the tables that the view referenced then you could examine the properties and relationships to find what you want. It is confusing to get your head around but really useful because if you know what type of object you are interested in then you can tailor your calls to that but if you just what to find all objects that reference a table (i.e. views, other tables via constraints, functions, procedures etc) it means you can really easily do that without having to say "get me all tables that reference this table, get me all functions that reference this table etc etc".

The TSql Model API returns loosely typed objects so everything is a TSqlObject - this is good and bad but I will leave it as an exercise for you to find out why!

DacFx Public Model Tutorial

This is what first allowed me to get into the DacFx, it is the only real documentation I have seen from Microsoft and invaluable to get started

http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutor...

Dacpac Explorer

https://sqlserverfunctions.wordpress.com/2014/09/26/dacpac-explorer/

I wrote DacPac Explorer to help teach myself about the DacFx and it turns out it is quite useful and has even been used within Microsoft as a training tool so there you go!

Querying the DacFx API – Getting Column Type Information

https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-A...

DacExtensions

If you have tried to do something like get the data type of a column you will appreciate how much work there is to do, well as a special treat there is a github project called Microsoft/DacExtensions and it was written by members of the SSDT team in Microsoft but is open source (I love that!) what it does is take the loosely typed TSqlModel objects and creates strongly typed wrappers so if you want to see what columns are on a table, you query the model for objects of type TSqlTable (or a version specific one if you want) and you get a list of columns as a property rather than having to traverse the relationships etc.

If you do any serious querying of the TSqlModel then look at this as it really will help!

Microsoft/DacExtensions

https://github.com/Microsoft/DACExtensions/tree/master/DacFxStronglyType...

Build Contributors

The last three items, the contributors all let you inject your own code into something that SSDT does and change it - this really is huge, normally with tools you get the resulting output and that is it your stuck with it but with SSDT you can completely control certain aspects of how it works.

When you build a project in SSDT a build contributor gets full access to the validated TSqlModel and any properties of the build task so if you wanted to do some validation or change the model when it had been built then you can use this.

Customize Database Build and Deployment by Using Build and Deployment Contributors

https://msdn.microsoft.com/en-us/library/ee461505.aspx

Deployment Plan Modifiers

When the DacServices have compared your dacpac to a database, deployment plan modifiers are called and can add or remove steps in the plan before the final deployment script is generated. Again this is huge, it is bigger than huger, it is massive. If you want to make sure a table is never dropped or you don't like the sql code that is generated then you can write a utility to change it before it is created - write the utility and use it for every build.

wowsers....

Inside an SSDT Deployment Contributor

https://the.agilesql.club/blog/Ed-Elliott/2015/09/23/Inside-A-SSDT-Deplo...

Repository of sample deployment contributors

https://github.com/DacFxDeploymentContributors/Contributors

Deployment Contributor that lets you filter deployments (don't deploy x schema to y server etc)

http://agilesqlclub.codeplex.com/

Deployment Plan Executor

Where deplotment plan modifiers can change the plan and add, edit or remove steps a plan executor gets read only access to the plan and is called when the plan is actually executed. The example on MSDN shows a report of the deployment to give you some idea of what you can do with them.

Walkthrough: Extend Database Project Deployment to Analyze the Deployment Plan

https://msdn.microsoft.com/en-us/library/dn268598.aspx

Help and Support

I created a gitter room to answer questions and give advice on writing deployment contributors but I would be more than happy to help answer questions on them or any part of the DacFx so feel free to drop in:

https://gitter.im/DacFxDeploymentContributors/Contributors

All welcome :)

What is SSDT? Part 2 - More Reasons to Bother!

  • Posted on: 6 January 2016
  • By: Ed Elliott

In part 1 of this series available here I introduced my drawing of what I think SSDT is and talked about the first major category the development ide and how it can help us. Just to recap for each subject on the image I give a bit of blurb and then some links to further reading.

In this part (part 2 would you believe) I will give an overview of the second major category which is that it is a deployment utility. What I mean by deployment utility is that we can take our code and deploy it to a SQL Server (hopefully that is fairly obvious).


what is SSDT? It is a complete deployment utility

DacPac / BacPac

We start again with these as they are critical to the whole process, the development IDE creates a dacpac from an SSDT project (or a dacpac / bacpac are exported from live databases) and then we have a set of tools to take these models of what we want a database to look like and make a database look like that. If you have ever used the redgate sql compare tool then you will understand what I am talking about, it is pretty straight forward right?

Deployment Utility

DacServices API

I will talk about this first as everything else uses this in one form or another. The DacServices API is responsible for comparing a model (inside a dacpac / bacpac) and comparing it to a live database. It is also responsible for the reverse - for extracting code, schema and in the case of a bacpac for exporting the data.

The compare / merge that happens is quite a scary thing when you think about it, there are a lot of different types of objects that SQL Server understands all with a very inconsistent syntax (I can think of at least 4 ways to create each type of constraint so multiple that by the types of constraints and the rest of the objects and it is a big number for a tool to understand).

I have been using SSDT to deploy database changes for around 2 years and at first I was really wary about losing something or a release going wrong but as long as you have a set of tests, verify your deployment scripts in a pre-production environment and run your tests then I am now confident to use the deployment to deploy to any environment including production. I understand that this is a big step for a lot of people but you can start auto-deploying to dev and test environments and start building up your confidence to full continuous deployments.

DacServices Class

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacserv...

Looking at SSDT upgrade scripts

https://the.agilesql.club/blogs/Ed-Elliott/2015-11-01/Looking-at-SSDT-up...

sqlpackage.exe

sqlpackage is the main tool I use to deploy dacpacs it is a command line tool that takes a dacpac (or bacpac, from now on I will just say dacpac and it always applies to bacpacs but if anything doesn't then I will call it out) and compares it to a database and does some stuff. The things it can do are:

  • Extract - Take a live database and create a dacpac (not a bacpac)
  • DeployReport - Generate an xml report of things it would change if it was allowed to
  • DriftReport - If you register a database with a dacpac then this displays a report about what has changed since it was registered (see below for what registering means)<.li>
  • Publish - Deploy any changes that the DacServices find to a live database (show me the money!)
  • Script - Generate a script that can be run later or just stored so it can be examined later if required (auditing etc)
  • Export - Take a live database and create a bacpac (not a dacpac)
  • Import - Takes a bacpac (not a dacpac) and creates a new database from it - this is how you deploy an existing database to SQL Azure (or whatever it is called this month)
  • Pipe - I have never really figured this one out, it is undocumented and when you try it tries to
    connect to a WCF service on the local machine and always fails for me

Things to know about sqlpackage are:

-There are at least 3 ways to tell it what server and database to connect to:

  • Via a publish profile (more on that later)
  • Via the /TargetConnectionString
  • Via /TargetServerName and /TargetDatabaseName

-The arguments are in the form "/" Arg ":" note the colon rather than an ='s

-sqlpackage.exe is always deployed to %ProgramFiles(x86)%\Microsoft SQL Server\%VERSION%\DAC\bin but it is just a .net app so you can copy the folder wherever you want. The only thing you will need to do if you run it on a machine that doesn't have the SQL Client tools installed or SSDT is also copy %ProgramFiles(x86)%\Microsoft SQL Server\%VERSION%\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

-If you wrap calls in sqlpackage.exe in a powershell script you need to redirect stdout and stderr to get all error messages.

What does registering a database mean?

What happens is that the dacpac is stored inside the database itself so later on if you want to know if anything has changed the DacServices will compare the stored dacpac to the database to verify that the schema and code hasn't changed.

I don't use the Visual Studio SSDT Publish

(Basically why I like to fawn over sqlpackage.exe)

https://the.agilesql.club/blog/Ed-Elliott/Visual-Studio-SSDT-Publish-My-...

SqlPackage.exe

(The command line args documentation, get used to this page it is your friend)

https://msdn.microsoft.com/en-us/hh550080.aspx

Publish Profiles

When you deploy a dacpac there are like a hundred options you can set (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacdepl...), you can use sqlpackage and pass each option as a /p:Option=Value but that gets quite tiring typing it over and over and also you tend to forget things in different environments so what you can do is to use a publish profile which is an xml file with the details of where you want to publish to and also any options you might want to use in your environment.

To create a publish profile, if in SSDT you go to publish the database it pops up with a dialog and at the bottom of the dialog it has a button "Save Profile As" this will save your profile and you can just edit this as it is an xml file or you can use the publish database ui that saved it to modify it. Note if you double click a publish profile in SSDT to edit it you must choose save as changes are lost without prompting you to save them. That is only annoying about the first hundred times you forget that :)

Publish profiles are basically the easy way to manage different configurations between different environments, use them and live the dream.

Publish Profile Files in SQL Server Data Tools (SSDT)

http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/09/publish-profil...

Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe

http://www.benday.com/2012/12/18/deploy-a-sql-server-database-projects-d...

Pre/Post Deploy Scripts

When you do an actual deployment or generate a script to be deployed later the process goes:

  • 1. Compare dacpac to database
  • 2. Create deployment steps (i.e. alter this, create that)
  • 3. Add the Pre-Deploy script to the beginning of the deployment steps
  • 4. Add the Post-Deploy script to the end of the deployment steps

This means you can run your own t-sql before and after the deployment. This sounds cool right? It gets better, if a table is modified then the constraints on that table are disabled, then the pre-deploy script is run, then the steps and then your post-deploy script and finally the constraints are re-enabled so if you wanted to setup your data of a new column or do something fancy then it is super simple.

You can have one pre-deploy and one post-deploy script per dacpac and if you use references to link together multiple dacpacs when you deploy only the scripts in the main dacpac are run, the other ones are silently ignored.

To add a pre/post deploy script in SSDT you simply add a new Script of type Pre / Post deploy and what happens is that the build action of the script (click on the file in solution explorer and see the properties window) is set to pre/post deploy - any existing scripts that already have the build action will have it removed.

Pre-Compare & Pre-Deployment Scripts to SSDT

https://the.agilesql.club/Blog/Ed-Elliott/Pre-Compare-and-Pre-Deploy-Scr...

Why does sqlpackage.exe not compare after the pre deployment script has run?

https://the.agilesql.club/Blog/Ed-Elliott/Pre-Deploy-Scripts-In-SSDT-Whe...

Peter Schott: SSDT: Pre and Post Deploy Scripts

http://schottsql.blogspot.co.uk/2012/11/ssdt-pre-and-post-deploy-scripts...

Go read anything from Peter Schott, really useful and insightful stuff, it really helped me get to grips with SSDT!

Refactor Log

When you use SSDT to refactor something so maybe you change its name or move it to a different schema to avoid the DacServices dropping the old object and creating a new empty object (horror of horrors!) SSDT adds an entry to the refactorlog. The refactorlog is an xml file that has a list of changes in it, fairly straight forward.

Each entry in the refactor log has a unique guid and when the refactor operation has happened on a database the guid is stored in a table called _refactorlog - what this means is that no matter how many times a dacpac is deployed a specific refactor is only executed once. This stops situations where you have an object, rename it to something else and then create a new object with the old name accidentally being renamed again.

If your refactorlog starts to get quite big and you know all the entries have been applied to all your servers you can also manually edit it and remove any entries you no longer require.

Refactoring with SSDT

http://dotnetspeak.com/2012/03/refactoring-with-ssdt

Schema Compare

I left the schema and data compare until last as although they are really useful I really try not to use them.

The schema compare is basically like the redgate sql compare tool it lets you compare a database and an ssdt project (actually any of a database, ssdt project or dacpac can be compared to each other) and then lets you update the target to match the source. You don't have to set the target to a database you can compare from a database back to an ssdt project and update it.

The reason I try to avoid it is that generally I find people use it to compare back to a project because they have been doing their development in SSMS and need to merge the changes back in - take the plunge and fix any headaches and just do the work in SSDT.

The second reason I try to avoid it is that I find people use it to compare to live environments instead of building an automated deployments and tests etc to prove that everything is good. The correct approach is to move towards continuous integration and delivery and then after that continuous deployment when your dba is bored of running scripts that never fail :)

If you do use it then sometimes it tries to deploy objects that haven't changed to see why, click on the object and in the status bar at the bottom it shows the reason why.

How to: Use Schema Compare to Compare Different Database Definitions

https://msdn.microsoft.com/en-us/library/hh272690.aspx

Schema Compare or Publish [SSDT]

http://sqlblog.com/blogs/jamie_thomson/archive/2013/12/19/schema-compare...

Data Compare

Data Compare is similar to the schema compare except it compares data in tables - I rarely use it but it is useful when you need to do that sort of thing. You can only compare live databases and it generates insert statements for you.

Compare and Synchronize Data in One or More Tables with Data in a Reference Database

https://msdn.microsoft.com/en-us/library/dn266029.aspx

Summary

Using the DacServices via whatever method you want (schema compare, sqlpackage, powershell, something else?) really makes it simple to spend your time writing code and tests rather than manual migration steps. It constantly amazes me who well rounded the deployment side of things is. Every time I use something obscure, something other than a table or procedure I half expect the deployment to fail but it just always works.

Over the last couple of years I must have created hundreds if not thousands of builds all with their own release scripts across tens of databases in different environments and I haven't yet been able to break the ssdt deployment bits without it acyually being my fault or something stupid like a merge that goes haywire (that's one reason to have tests).

If you liked the idea of SSDT but for some reason didn't like the deployment stuff you could always use the redgate sql compare tool to compare the ssdt source folder to a database which would at least get you started in the right direction.

In the last part (part 3) I will discuss what is arguably a game changer for RBDMS's a complete API to manage your code :)

What is SSDT all about aka Why should I bother?

  • Posted on: 5 January 2016
  • By: Ed Elliott

I often get asked the questions “What is SSDT" and I have wanted to have a single reference as to what it is as it is actually pretty big. To be clear I am just looking at the database projects version of SSDT what was originally SSDT rather than the BI tools that came with SSDT-BI and are now being merged with SSDT – Perhaps I will expand this to include those one day but probably not.

What I have done is created a picture of the parts of SSDT and I will write a little blurb and give one or more links to somewhere for further reading, I really hope that someone finds this useful as SSDT is a great tool that should be used by more SQL developers.

For me:

SSDT is a tool set that consists of:

  • A development ui
  • A deployment system
  • A documented api


what is SSDT? It is more than just an IDE

DacPac / BacPac:

The three major components in SSDT are linked together using dacpac’s and bacpac’s.

The dacpac and bacpac are either the output from SSDT projects or exported from live databases. The files themselves are zip files which use the Microsoft “Open Packaging Conventions". If you rename them to .zip you can extract them and see the contents. A dacpac contains the model which is enough information to be able to re-create both the DDL and DML of the database to be deployed. A bacpac contains the model but also contains data files with the table data in the BCP format. Having both the schema and the data means that not only the schema but also the data can be deployed.

“Dacpac braindump - What is a dacpac?"

http://sqlblog.com/blogs/jamie_thomson/archive/2014/01/18/dacpac-braindu...

Msdn: Data-tier Applications (see section “BACPAC")

https://msdn.microsoft.com/en-GB/library/ee210546.aspx#Anchor_4

Development IDE

The first major category that we can break SSDT down to is that it is a development IDE, it has tools and utilities to help us write code and that can’t be a bad thing.

Visual Studio

Visual Studio is a great UI, the editing capabilities are excellent (things like alt+shift selecting lines and changing multiple lines at the same time and tabbing lines across together). The best thing about it though is that it is extensible so you can use tools like resharper or the redgate sql prompt or even your own extensions.

In my case I use resharper and altought doing ctrl+n (with the resharper keyboard mappings) will not find t-sql objects it does find files so as long as you name each file the same as an object it is super easy to navigate around a project.

If you do not have a license for Visual Studio then you can use the free express version or the Visual Studio shell which is good but you can’t add extensions. If you are working on a community project or part of a smaller company then you can use Visual Studio Community edition which is the same as the Professional edition but is free.

Downloads | Visual Studio

https://www.visualstudio.com/en-us/downloads/download-visual-studio-vs.aspx

Source Control

You do not strictly have to use source control to use SSDT but you do need to get your DDL and DML into at least one text file and have that in the file system so you may as well use source control as you effectively get it for free.

When you create a project you can either start adding files manually or you can import from a database so taking a database and getting it under source control is really quite simple. If anyone is stuck doing this feel free to ping me, I am sure that I can help you :)

HOWTO Get T-SQL into SSDT

https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Get-An-Existing-Schema-...

Declarative Development

The traditional way of writing code for SQL Server, either the underlying schema so tables and indexes or the code i.e. stored procedures and functions etc. is to take an existing database (even an empty one) and make incremental changes to it until it ends up how you want it. Even writing this sounds like a waste of time but I digress.

With SSDT you specify what you want and then let the tools take care of making that happen so if you have a table definition like:


create table knees_up_mother_brown(
knees_up_id int,
knees_up_date datetime
)

And you want to change the definition so that it looks like this:


create table knees_up_mother_brown(
knees_up_id int,
up_date datetime2,
down_due_date datetime2 null,
constraint [pk_knees_up_mother_brown] primary key (knees_up_id)
)

If you use SSDT and the declarative approach then your work is done, if you use the traditional approach then you need to work out how you want it to look like (done) and then the steps to go from the first version to the second.

Something like:

  • sp_rename on the knees_up_date column
  • find all the references to the old column and change them to the new one (hopefully there won’t be many ?
  • add the extra column down_due_date
  • add the primary key constraint

On top of this as well as storing this update script you need to make sure you still have the definition from which you changed to apply the script to. You should also make it idempotent so you only actually try to make the changes if they haven’t been run before. Alternatively use the declarative approach in SSDT and not worry about any of that.

Declarative Database Development with SSDT

This is a talk by Gert Drapers, he is the grandfather of SSDT, go listen to him tell you a bedtime story.

https://sqlbits.com/Sessions/Event10/Declarative_Database_Development_wi...

SSOX

The SSOX or SQL Server Object Explorer is a cool utility that lets you connect to a live database and do things to it like debug stored procedures or update individual objects. It also lets you see a view of you projects after all references have been resolved so if you use “Same Database" references you can see how your end project will end up – really useful.

Place Holder

There hasn’t been much written about the SSOX but it is really cool, go check it out. I have an article at Simple Talk that is waiting to be published, when that happens I will put the link here.

Code Discovery

What I mean by this is you can find code easily, you can right click on an object name be it a table or function or whatever and jump to the definition. You can also do the reverse you can right click on an object and find all the references. This reference discovery is really useful as you can get examples of how objects are used within the project and get an idea of how wide ranging a change to it would be.

No Link

Code Analysis

If you write a stored procedure that references a table that does not exist it lets you merily deploy it but in SSDT you will not be able to build the project. This is huge for SQL developers as it drastically reduces the risk of a typo causing a runtime error at a later date. The code analysis in SSDT ranges from validating that the syntax is correct for the specific version of SQL Server your project targets (on the properties page you can set the version) to validating that references to objects are correct as well as providing some analysis rules and a framework to let you create your own rules.

Creating your own rules is really quite simple and imagine as a DBA creating a rule for your developers which dis-allowed or flagged certain things (scalar functions, cursors, non-sargable searches that could be made sargable pretty simply) that would be a great thing to do. I know that if I was still a DBA I would be basically insisting that developers writing code for databases I could get called out for at night used SSDT and there were some pretty robust code analysis rules in place :)

Walkthrough Authoring a Custom Static Code Analysis Rule Assembly for SQL Server

https://msdn.microsoft.com/en-us/library/dn632175.aspx

Enforcing T-SQL quality with SSDT Analysis Extensions

https://the.agilesql.club/Blogs/Ed-Elliott/Enforcing-TSQL-Code-Quality-T...

Refactoring

This, for me, is the killer feature of SSDT – consider the normal approach to compare / merge deployments when you want to rename a table. If you rename a_table to a_cool_table when you go to do the compare the tool will say:

  • Create new table a_cool_table
  • Drop table a_table

Dropping the table is really bad as it means you need to write some custom code to handle it. In SSDT if you use the built-in refactoring support to rename or move an object to a different schema it will not
only find all the references to your object and update them but it will also create an entry in the refactor log (more on that later) which will cause a sp_rename to be generated rather than a drop and create. I know right, awesome :)

How to: Use Rename and Refactoring to Make Changes to your Database Objects

https://msdn.microsoft.com/en-us/library/hh272704(v=vs.103).aspx

I also have an article coming out on simple talk about this so I will add the link when it is published.

Unit Testing

If you write code you should unit test, ok? There are a few approaches to unit testing in SSDT, if you like using tSQLt then you can simply include it in the project (I include it as a reference in a dacpac
see https://the.agilesql.club/Blog/Ed-Elliott/AdventureWorksCI-Step5-Adding-...).

SSDT however comes with it’s own test framework which lets you write unit tests in VB.Net or C# - the tests run T-SQL and there are pre/post scripts you can run so if you can do it in SQL Server you can test it using this.

Getting Started with SQL Server Database Unit Testing in SSDT

http://blogs.msdn.com/b/SSDT/archive/2012/12/07/getting-started-with-sql...

Intellisense

We shouldn’t really have to have this discussion but if you are developing without intellisense then you may as well develop in notepad – you can do it but it is not efficient so don’t do it.
SSDT comes with a great version of intellisense that makes development faster and easier – a real no brainer here. I realise that the redgate sql prompt and ssms also include intellisense but this isn’t a comparison it is just telling you what SSDT is all about :)

No Link

Intellisense completes words etc what do you want to know more than that?

Build / Code Validation

This is where SSDT really gets interesting, instead of a disparate set of tables and procedures each doing their own thing and possibly generating runtime errors you cannot build an SSDT project with invalid code.

The example I always give is of the stored procedure that runs at the end of each year which relies on a table that has been modified, the traditional method of writing T-SQL does not stop breaking changes and because not many SQL developers write unit or integration tests it is unlikely that any issues would be found until the critical year end.

If you have an error in the SSDT project the project won’t build – so you must fix it before you can move on. If you project does build then it creates a dacpac which is effectively the binary output format that can be used to deploy changes to actual databases.

No Link

I couldn’t find anything that talks about the SSDT build and verification process – if anyone has a link let me know and I will update it or maybe write something myself.

Summary

That is it for part 1, in part 2 and 3 I will cover the deployment and api parts of SSDT. Any questions please shout and if anyone has some better links please feel free to send them to me and I will update this.

Finally if anyone thinks I am missing any major part of SSDT then shout and I will add it to the diagram :)

Pages