SSDT – A Geeky Gulati

I’ve been learning a lot of new things with this client – SSIS, SSRS, and now we’ve also implemented a workflow using SSDT. In short: This is a well supported way to check your database schema and seed data into source control. So, looking back, what are the resources that I wish I had known going in?

Installation

Near as I can tell, SSDT is installed if/when you install SQL Server Express. There’s a basic version also installed if you install VS2012 Premium and up, supposedly.

It can also be installed directly via http://msdn.microsoft.com/en-us/jj650015

Once installed, you’ll be able to do Schema Compares, Data Compares (useless to me?), and database projects (*.sqlproj). As a lot of people don’t seem to have it installed, I usually put it in a different solution than the main project solution.

Capturing an Existing Database into a Database Project

Create database project; If you choose to go blank at first then..
Right click solution, compare schema, SWITCH DIRECTIONS, Update Target
Hint: Don’t save any user objects, not till you are very comfortable with it.

As of the current version, I no longer have to have one database project, and a different server project (as I did when I looked at it in 2010 or so).

Making Changes

Can either make them in the database and then suck the changes in, or can edit them directly in visual studio – either graphically or in SQL.

A build action will verify every reference – for example, sproc referencing columns, etc. There’s a bit of a confusion around getting casing incorrect that you can turn off.

When you check the changes into source control, they are saved as the T-SQL version. Very self-documenting, diffable, etc.

Applying Changes to Local Database Interactive

Use schema compare, select/deselect what you want to update, and hit Update.

This does NOT run pre- and post- deployment scripts.

Applying Database Changes Automated / Without Visual Studio

Compile the project, get a .DACPAC file. Then use SQLPACKAGE.EXE, located usually at C:\Program Files\Microsoft SQL Server\110\dac\bin\sqlpackage.exe. Newlines added for readability.

“If I publish it, what will it do?”

/action:DeployReport
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/OutputPath:differences.xml
/p:DropObjectsNotInSource=false

“What is absolutely everthing that is different between the two?”

/action:DeployReport
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/OutputPath:differences.xml
/p:DropObjectsNotInSource=true

“Do it. (but don’t delete those users!)”

/action:Publish
/sourceFile:Flintstone.Database.dacpac
/TargetServerName:BEDROCKCITY
/TargetDatabasename:FlintStone
/p:DropObjectsNotInSource=false
/p:BlockOnPossibleDataLoss=false

It runs it with pretty colors in a console app.

With Sql Server Management Studio, caveats

In SSMS, under database, tasks, there is “Update Database-Tier Application”. This does effectively the same thing, except you don’t get to choose the options above, so it usually does too much. However, you can save off a change script and then walk the change script by hand.

Handing Data Changes

This guy wrote an excellent blog article about it: http://schottsql.blogspot.com/2012/11/ssdt-pre-and-post-deploy-scripts.html. I extended it a little bit:

Created a ScriptHistory table with a script name (PK) and when it was run, to avoid rerunning
This is for the one time refactors that move data from an old location to a new location (or going from inferred settings to explicit settings), but cannot be run again, but there is no way to detect if it’s a rerun or not.

Use MERGE statements like this article suggests: http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

Best Practice for Security / Users / etc

I have not had a chance to do this yet, but the general idea is to create roles for permissions, have SSDT deploy the roles, but the user membership is unique per server and is handled manually. (We’re talking about situations like I am developing in COMPANY1’s domain, but the production server is in COMPANY2’s domain and Windows Auth is being used.)

In Conclusion

I watched my dba-idol Richard Svatos struggle with this in 2003.

I’ve tried to solve it in a variety of ways

I’m glad there is a usable, well supported solution now in the ecosystem which I am flourishing.

My new project involves getting data from 500+ remote sites into a target database; in parallel, quickly. Thanks to some proof of concept work done by my coworker Mike “The Devastator” Hurd (I made up the nickname), I got to learn SSIS, and I’m cursing that I had not picked it up earlier.

What Is SSIS?

Imagine you wanted to write a generic program to get data from point A to point B, but modify it along the way.
You wanted pluggable components that could talk to each other.
You wanted anything that could be done in parallel to be done in parallel. Ie, read from two tables at the same time if you have bandwidth.
You want it to work buffered rather than row by row. But dynamically figure out buffer sizes so that they fit well to the page size on the computer (to minimize swapping)
You want a GUI that you can configure these components with.
You want every component to have a configurable “if things fail” state, to redirect failed rows to other components, etc.
You want it to be solid – no leaking memory, etc
You want to parameterize the stuff getting into the components
You want it to be runnable from a command line, with ability to override parameters and connection strings.
You want the whole configuration to be saveable as an XML file, or hosted in a SQL Server
You want to be able to schedule it to run from SQLAGent (SQL Server’s “Cron” thing)
You want to be able to run it directly from C# via managed code.

Then you would write SSIS.

What Does It Look Like?

Initial Gotchas

SSIS requires some level of licensing, so this does NOT work:

Install SQLEXPRESS
install Visual Studio 2012 Premium
Install SSIS add-on
Install SSDT (“database tooling for VS2012”)

It successfully loads, and you can debug packages within Visual Studio, but you cannot run them outside via dtexec and dtexecui (the command line thingies for SSIS). Instead, you need to add:

Install SQL Server Developer Edition (which includes SSIS) (or standard edition)

Select Integration Services & Data Tools
Do NOT have to install the engine.
Might have to install an updated SSDT to get VS2012 support

Then everything runs fine.

The other thing I quickly learned is it is very Schema-dependent. The reason for this is it allocates row counts into buffer sizes based on the size of the rows – so a Varchar(MAX) field is very different from a Varchar(250) field – and the schema will fail to validate if the underlying data source it is reading from does not match perfectly.

The solution I had to apply was to do a select cast(x as varchar(250)) as x to extract the data to guarantee the schema coming in to SSIS.

Conclusion

Very glad to have this tool in my arsenal. Thanks Mike!

Tag: SSDT

Primer on SSDT / Checking Schema into Source Control