SQL Server – Issues I have with SSDT based deployments: Part 4.1

Continuing my series on SSDT based deployments, in this part, I’m going to look into something that’s not entirely SSDTs fault, but I’m sure could be less painful: importing pre-existing databases into an SSDT project.  Particularly ones that have cross database references, or three part naming of objects (database.schema.object) which effectively make them a self referencing object.

Firstly, I want to state that I don’t really like cross database references.  In a world of filegroups, schemas and so on, I think a large number of multiple database setups should probably just be merged into a single db.  I address quite a few of these thoughts in my article SQL Server – When is a new database appropriate?

However, I’m also aware that this refactoring takes time, and that the damage is already there.  Add this into the fact that these databases will most likely not have been developed with any source control, and so somehow we have to drag them, kicking and screaming, into TFS and an SSDT project…..

1581831440-jesus_kicking_and_screaming_cartoon

6. Retro-ing a database is a right PITA.

Let’s start by saying that I’m not a fan of the import database functionality in SSDT.  The options for structure are limited; for some reason we can have schema/object, but we can’t have object/schema?!  In addition to this the limit of 1000 files per folder makes finding stuff impossible.  Yuck.

After you’ve imported your database, you’d think you could just build it.  However, if you’ve got any of the aforementioned cross database, or self references, then your build will fail.

On the face of it, database references in SSDT are simple.  In your SSDT project, you right click on References, select Add Database Reference, and fill in the form below:-

adddatabasereference

This isn’t too much of a problem if we’re working with new databases, because we’ll have been building those projects up over time, and we’ll be creating our reference DB first in the project and then developing from there.

But, in our scenario where we want to get all our existing DBs into our new deployment methods using SSDT, hey’re riddled with some of the following:-

  • Cross database rteferences, perhaps including linked servers (eurgh).
  • Three part same database references (these are effectively a cross database reference to itself).
  • Circular cross database references.

Note, from the Add Database Reference screen, the way in which database references are implemented.  Both the server (if on a different server and being referenced by a linked server) and database are replaced by a SQLCMD variable.  So, essentially, in our database project, all our scripts will need to have all cross database (including self referential database references) applied in the scripts as the SQLCMD variable, not the actual text that will be in the stored procedure, so:-

SELECT Col1, Col2 FROM [$(Database)].Schema.Table

not:-

SELECT Col1, Col2 FROM Database.Schema.Table

This is fine, but if we’re importing the database into SSDT from a database, the exact text of the procedure is used; kind of annoying. Especially when you have thousands of stored procedures that were all written with full three part naming convention described by some ages old standard. Sigh.

Fortunately, I do have some powershell that might help; though I’d want to be pretty careful checking it all before I just went and deployed to live!  In theory, there shouldn’t be any changes to the live code if we get this right, so careful checking of the deployment script should give us some satisfaction that it isn’t going to change every procedure.  Though perhaps you could use these as an opportunity to remove that self reference; obviously a more risky approach.

EDIT: Since writing this, I’ve discovered there’s a much easier way of dealing with this.  When you select your DACPAC, the sqlcmd variable is automatically populated.  You can simply remove the text in that box and then the normal reference will work.  Obviously, you would no longer be able to deploy a database with dynamic names, but it will save you potentially updating thousands of scripts!  The variable is required for linked server references, however; doesn’t appear to be a way around that.  Linked servers suck though, so serves you right!

Once we’ve made that change we still need a DACPAC to do the self referencing (and for the other DBs that we’re going to reference), which somewhat obviously we can’t build from our project because we need those references.

Fortunately, we can get a DACPAC from either SSMS, or SqlPackage.exe.  I chose to knock up a little bit of powershell to build all the databases on a server using SqlPackage.exe (I used a support server, I don’t know the impact of doing this against a live machine as the:-

$GetDatabases = "SELECT name FROM sys.databases
                WHERE database_id > 4
                AND name not like 'ReportServer%'"

$TargetServer = "MyServer"

$Databases = invoke-sqlcmd -ServerInstance $TargetServer -Database "master" -Query $GetDatabases

ForEach ($Database in $Databases)
{

$Program = "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\SqlPackage.exe"
$Params = "/Action:Extract", "/SourceServerName:$TargetServer", "/SourceDatabaseName:$($Database.name)", "/TargetFile:C:\$($Database.name).dacpac"

write-output "Running $Program $Parmas"

& $Program $Params
}

We can now use these DACPACs as the source for our database references.  Hurrah!  Incidentally, you can use the DACPACs to import the database, but I can’t really see any advantage/disadvantage to doing this; it was a little slower in my machine when I tried it otherwise was the same.

In the next post, we’ll have a look at getting the cross database references up and running, and some of those circular references sorted, so we can actually try and build the project.

Advertisements

6 thoughts on “SQL Server – Issues I have with SSDT based deployments: Part 4.1

  1. Hi after extracting dacpac I need like

    SELECT Col1, Col2 FROM [$(Database)].Schema.Table

    not:-

    SELECT Col1, Col2 FROM Database.Schema.Table

    how can I do this can you help me

    Like

  2. I have a sqlcmd variable defined which I am using when deploying, then I extracted the dacpac from the deployed database and comparing two dacpacs which is showing the differences stating
    Actual
    SELECT 1 FROM [$(Database_Name)].dbo.tbltest
    Extracted
    SELECT 1 FROM [TestDb2].dbo.tbltest

    Like

      • Apologies for the delay in replying to this. So, to confirm, the dacpac you have you have extracted from a live database, not built from an SSDT project?

        If so, then the SQLCMD variables won’t be created from the live db. You will have to import that dacpac into an SSDT project, and then use a method of find and replace to change the references to the SQLCMD variable. My powershell script to do that en masse will help:-

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s