Learn how to use tSQLt to professionally unit test T-SQL code. To enroll in the free self-paced course visit here

SSDT How To Fix Error SQL17502

  • Posted on: 6 November 2017
  • By: Ed Elliott


If you build an SSDT project you can get an error which says:

"SQL71502: Function: [XXX].[XXX] has an unresolved reference to object [XXX].[XXX]."

If the code that is failing is trying to use something in the "sys" schema or the "INFORMATION_SCHEMA" schema then you need to add a database reference to the master dacpac:

Add a database reference to master:

  • Under the project, right-click References.
  • Select Add database reference....
  • Select System database.
  • Ensure master is selected.
  • Press OK.

Note that it might take a while for VS to update.

(Note this was copied verbatim from the stack overflow question with my screenshots added: - I will explain more if you get past the tldr but it is quite exciting! )


I like this question on stack overflow as it has a common issue that anyone who has a database project that they import into SSDT has faced. It might not affect everyone, but a high percentage of databases will have some piece of code that references something that doesn't exist.

The question has a few little gems in it that I would like to explore in a little more detail because I don't feel that a comment on stack overflow really does them justice.

If we look at the question it starts like this:

The first thing is the error:

unresolved reference to object

What does this mean? When you write some code in SSDT, a stored procedure, function, foreign key (admit it, you weren't expecting foreign key were you!) SSDT validates that when the code references another object that the other object exists and is usable in that scenario. So if you have a table called "" and you made a mistake and typed "select a from dbo.ab3" then SSDT will notice this and give a warning or an error (more on this later).

After the unresolved reference we have



What is the INFORMATION_SCHEMA? Well, one of the most exciting things about modern RDBMS's (I say modern but basically forever) is that the SQL language specification dictates that the language used by an RDBMS is capable of querying and interacting with the environment as well as the data. Think of an RDBMS as being like a DevOps dream decades before Gene Kim et al. sat down at a typewriter and spilt the Pheonix project onto paper. The INFORMATION_SCHEMA is a way to query things about the database environment. In the example in the question, they are using it to find the number of tables that exist that have a specific name (tip: it will either be 1 or 0).

In SQL Server, the INFORMATION_SCHEMA is in the master database and is special in that you can type "SELECT * FROM INFORMATION_SCHEMA.SOMETHING" and you don't have to type the name of the master database first like "SELECT * FROM master.INFORMATION_SCHEMA.SOMETHING". This is nice as it saves our little typing fingers but also a little hard for SSDT as it means that it has something else to handle.

Moving on we have:

Within Visual Studio, the schema and name for the view are both marked with a warning.

There are two possible outcomes in SSDT when it can't find a referenced object, the default is what has happened here there is a warning, and you get a squiggly line under the code:

The last piece of the question is interesting,

I also tried changing the implementation to use sys.objects instead of this view, but I was given the same warning for this view as well.

The sys schema is similar to the INFORMATION_SCHEMA schema except it is SQL Server (and Sybase ha ha) specific rather than cross-platform as INFORMATION_SCHEMA is supposed to be.

The final thing about the question is it is by someone called Sam with (at today's count) 17 thousand stack overflow points, that is some serious stack overflowing!

The Answers

If we look at the first answer we have:

The answer is spot on and fixes the error for the view Sam is talking about in the INFORMATION_SCHEMA. I like that stack overflow lets someone ask and then answer their own question. He was stuck, he asked, he found the answer and instead of leaving a page without an answer like DenverCoder9, he found the answer and left it for the world:

Answer 1 Comments

The first comment is by the great Peter Schott (B | T):

To give a bit of background on this, Peter has a team where some people have installed Visual Studio to the C drive and some to the E drive, and they were getting errors when referencing the master.dacpac.

What happens with database references is that you get a block of XML that looks something like this added to your .sqlproj file:

When SSDT tries to resolve a reference, it looks at the "Include" attribute on the "ArtifactReference", and if it doesn't find that then it looks in the "HintPath" and failing that I think it looks in the build bin directory, but we won't rely on that working.

This reference is from a Visual Studio 2017 project and you can see that the "HintPath" is not hardcoded to the C drive but instead uses a variable to get to the dacpac. This was not always the case, a while back this was hardcoded using ".." relative paths (WTF!), a full discussion on this pain was:

So this fixes the shared dacpac from different system dacpac's but what if the code we are referencing was in a user dacpac? How do we set the reference and allow people to map their source code folders or build servers to the same projects?

There are two approaches. The first is to put the dacpac's in a known location "c:\dacpacs", "\\dacpacserver\dacpacs" etc. and always use that path. The second option is to check the dacpac's into source control with the solution and referencing using relative paths.

Both ways work, do what is best for you.

The next two comments are:

Veysel chose to copy the dacpac's into the solution folder, yay to Veysel.

Now Orad seems to have got stuck as they have already referenced the master dacpac. As a guess, I would say Orad's problem is either that they are referencing an object in another schema and has the same error but not for the sys or INFORMATION_SCHEMA schemas or Orad has mistyped a referenced object and the warning or error is actually valid.

They should re-check the code is correct. It is possible that they are referencing an object in the INFORMATION_SCHEMA that isn't in the dacpac. If this was the case then Orad could declare the missing object themselves, and it would probably just work, but we would need to keep his reference to master, otherwise, Orad would lose their "create schema INFORMATION_SCHEMA" statement in his master.dacpac.

After Orad's comment we have:

Martin is happy :)

NYCdotnet less happy but they do mention something interesting that there is a setting under "Online Editing". SSDT has a couple of different usage scenarios. The first, and in my opinion, the main reason for using SSDT is the offline editing where you edit T-SQL code offline, build and validate before you get anywhere near any a database.

The second scenario is online where you connect to a database and change the code like an old school DBA with a live production database in SSMS. The setting in this comment talks about the online version and doesn't fix the warning or error in the offline scenario which is probably being mentioned in this question (if this isn't about the offline version then I am going to hang up my technology boots).

The final comment on the first answer is by Scarl:

Scarl I guess either doesn't have an SSDT database project open or the installation has failed, try re-installing SSDT and creating a new database project from scratch.

Answer 2

techfield has suggested this:

In Sam's original question, they mention that they get a warning and the project still deploys but techfield points out that this can cause an error and if you get an error you can't deploy a project as it won't build. This can result in much frustration.

The difference between this error being displayed as a warning which can be ignored (unless you have "treat all warnings as errors" which of course you should but don't) and an error which stops the dacpac from being built is this setting. You can either edit the .sqlproj file and change the XML, or you can go to the properties of the project and tick the box:

Jowen then correctly said you shouldn't turn off errors willy nilly - personally I always check this and enforce it unless I am working with an "inherited" database where it isn't possible but over time make it possible and then check it. When it is possible, I then work towards clearing the warnings if practical.

Final Answer

The final answer is by Russell:

What has happened in Russell's case is that he has a table in a project that is in a .sql file but the build properties of the file "Build Action" is not set to build so although the file exists and the table is valid it is invisible to SSDT. Set the "Build Action" on the file properties in Visual Studio to "Build" and it will be included and the reference can be validated.

Try for yourself

I have setup a GitHub repo to show this error in all of its glory in case you would like to play along at home.

If you would then get yourself a command prompt that can run git and do:

git clone

I know I called the project the error number, this is like inception in real life. If you go ahead and open the SQL71502.sln solution when you build you should get a warning and also the reference to INFORMATION_SCHEMA should show a blue squiggly line:

Note about the warning, the warnings are only displayed when a file is compiled, if you build then build again without changing a file it won't get re-compiled so all your warnings will disappear! The warning is still there, add a new line or something to the file to cause a rebuild and the warning to be re-displayed. Because of this feature, I sometimes do a visual studio clean and re-build when using SSDT to get a full overview of the issues.

Let's go hardcore and set the project to treating T-SQL warnings as errors. Go and run:

git checkout 1-errors-as-warnings

You will probably need to reload the solution in Visual Studio and then when you build you will get an error instead of a warning, and the dacpac won't build.

To fix the error, we now need to add the database reference to the master database, follow the advice from the stack overflow question above to add the reference and then when you build you should get a successful build. If you didn't want to add it yourself you could run:

git checkout 2-fix-error

When running git checkout, if you have changed any files you will get an error running "git checkout -- ." will reset git and let you switch branches (it throws away any changes so don't do it unless you want exactly that).

With the database reference added we could rebuild and there should be no error or warning:

Now, if instead of a system reference we had a missing user reference, we would get a similar thing with the same error number. Switch to the next branch:

git checkout 3-broken-user-reference

you will see this error:

We fix it by creating the object that it was expecting:

and then it will build successfully:

Happy days.

I hope no one minds we copying and pasting from stack overflow, I guess it is the modern way :)

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.


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: (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 :)


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:

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 TABLE dbo.person
first_nmae VARCHAR(25) NOT NULL, --typo is on purpose!
last_name VARCHAR(25) NOT NULL

CREATE TABLE hr.departments
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 )
FROM person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id =;
EXEC hr.get_employee 1480;

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 )
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 =;

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 )
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 =;

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="">
<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" />

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.


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"?>
<Parameter name="TestDatabaseConnectionString" value="server=.;initial catalog=tSQLt_Example;integrated security=sspi" />
<Parameter name="IncludePath" value="AcceleratorTests" />

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:

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 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;

Any issues shout!


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"/>

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:

Hopefully it helps someone else.


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:


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.


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

Using the TransactSql.ScriptDOM parser to get statement counts

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

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
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

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


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!


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

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.


Inside an SSDT Deployment Contributor

Repository of sample deployment contributors

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

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

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:

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

Looking at SSDT upgrade scripts


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)


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

Publish Profiles

When you deploy a dacpac there are like a hundred options you can set (, 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)

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

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

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

Peter Schott: 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

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

Schema Compare or Publish [SSDT]

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


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 :)


Site Search with Duck Duck Go