SSDT: Pre and Post Deploy Scripts

At some point, you will likely need to release changes that contain more than just schema changes. You may need to update data in a table, remove data that could cause a problem, or insert some data to support a code release. To support these changes, SSDT provides support for Pre-Deploy and Post-Deploy scripts.
These scripts are not created by default in a new SSDT project. To organize your scripts, you may want to create a folder structure similar to the following.

If you right-click the Post or Pre Deploy folders, you can choose to add a Script item.

You will then get a choice of which type of script to include. Choose Pre or Post deployment, as appropriate, and name the file accordingly.

You can only have one Pre-Deployment script and one Post-Deployment script per project as part of the Build! All included scripts must be set to “Not in build” or you will likely have errors.

If you look at the properties of these scripts, you will see that the Build Action is set to PreDeploy or PostDeploy. Opening the Post-Deploy script will show this default text.

/*
Post-Deployment Script Template       
--------------------------------------------------------------------------------------  This file contains SQL statements that will be appended to the build script.  Use SQLCMD syntax to include a file in the post-deployment script.  Example:      :r .\myfile.sql  Use SQLCMD syntax to reference a variable in the post-deployment script.  Example:      :setvar TableName MyTable                SELECT * FROM [$(TableName)]     
--------------------------------------------------------------------------------------
*/

 
Using Pre and Post Deploy Scripts
These files are interpreted as SQLCMD scripts by SSDT. You can use most valid SQLCMD syntax within the script file to include other scripts and set variables. At the time of this writing, you cannot use the :ON ERROR IGNORE command within a script. This will hopefully be addressed in a future release of SSDT.
Pre-Deploy scripts will always run before the schema changes are run. If you alter the database in a way that changes the schema, you may encounter errors with the release. E.g., if you make schema changes to the Person.Person table, but drop that table in a Pre-Deploy script, your publish action will likely fail.
Likewise, Post-Deploy scripts will always run after the publish action. This makes them a great place to insert new data, make small adjustments to the system, or perhaps implement custom security and permissions. The caveat to using post-deploy scripts to make changes is that the changes need to be repeatable. If you write an insert statement for a new lookup value, that same insert will run next time unless you check for the existing value first.
To create a new script to run in pre or post deploy:

• Right-click the appropriate folder (Pre-Deploy, Post-Deploy, other)
• Choose to add a "script" and select the "Script (Not in build)" option
• Give it a name, preferably one without spaces as it will make it easier to run the pre or post deploy script.
• Add your code changes. You may want to give it some descriptive comments and sometimes a Print statement can be helpful if you want to see progress as you run a deploy script manually.
  
  
  
  • Make sure that you can re-run this script without causing errors! Check for data that may already exist, use outer joins, wrap your code in Try/Catch - whatever you need to do to make sure that you can have this run again if necessary.
• After saving the script, edit your pre or post-deploy script and add a new line in a non-commented area something like:

Run a script called "MyScript"

1. :r .\MyScript.sql

• This assumes that your script is in the same folder as your pre or post-deploy scripts. Adjust the relative paths as needed for the script you created.


• Save your deploy script.

The next time you publish your project, it will pick up this script and include it in your change set. If you choose to create a script on publish, you can see the full text of your scripts included in the pre or post-deploy section of your change script.
 
Cleanup of Pre and Post Deploy Scripts
There are several options for cleanup, but one of the best suggestions I've seen has been to generate project snapshots and then remove the script references from your pre/post deploy scripts and the script files themselves from the project. They will still be saved in the snapshot, but will not be in your project anymore.  You may be able to manage this well through your Version Control System, but snapshots do have some advantages.
Pros:

• Your project will remain somewhat clean.
• Script files will be saved with their appropriate snapshot, ideally tied to a particular release.
• Less concern about whether a script is replayed because it's removed.
• Good for scripts that have a larger impact and should only be run one-time.

Cons:

• It's a manual process and requires some attention to detail.
• You need to look in the snapshots to see what scripts were used.
• It may require that you have a more formalized release process.
• You may need to publish several snapshots to bring the database up to a current version.

SSDT: External Database References

If your database references other databases, you'll need a *.dacpac file for each external database referenced in your code. For example, if you reference AdventureWorksDW2008 from Adventureworks2008, you'll need to add that as a database reference.
 
To add a Database Reference to your project

• Right-click the "References" folder in your project or choose the "Project" menu and select the option to "Add Database Reference"

• Browse for your dacpac file
• Choose the appropriate database location. (Same, Different DB / Same Server, or Different DB / Different Server)
• If your database name doesn't change, clear out the "Database Variable" column. It isn't immediately obvious, but doing this will let you keep referencing your database in the same manner you do currently.
  • If this database name can vary, populate the Database Variable field. You'll need to replace this reference in your code with the variable from this column.
  • If you're referencing a database on a linked server, this would probably be better served with a static database name and a "Server Variable". Make sure that server variable is set appropriately when you publish the database.
  • This should be addressed with a future release of SSDT.
• You may choose to suppress errors if there are unresolved references in the project. This is useful if your dacpac file could be out of date. You can still code against an object you know to exist, but isn't in the reference file yet.
• If you use Linked Servers, you’ll probably want to use the Server Variable to ensure that you can release your code in different environments.

 
Some Best Practices and Lessons Learned

• Make a shared folder to store your dacpac files in a common place. This should be easily accessible from all of your SQL Projects.
  • If you use C:\DatabaseProjects as your base for all SQL Server projects, create a folder in there to store your shared dacpac files.
• Because all paths are relative to the project, you may need to copy the master/msdb dacpac files that ship with SSDT into your shared folder. You can find these files in your Visual Studio folder in a path similar to the following.

Drive:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\VersionNumber\SqlSchemas

• If you support multiple SQL Server editions, you may want to rename master/msdb files with their user-friendly version number. E.g., the master.dacpac file in your "90" folder could be renamed to master2005.dacpac
• This should be addressed in a future release of SSDT and could already be fixed.

• If you use variables, don’t forget to replace any TSQL with the variable in the form of [$(VariableName)].
• Square braces may not be necessary, but in most cases, they’ll work as expected for DB or Server names.
• If you need to reference a dacpac that is already in use, you’ll need to create a copy of it. This comes up more often if you’re referencing a replicated database for which you already have a production dacpac.
• Choose good variable names for the Server variable name.
• Don’t forget to set the variables in the Project and/or Publication properties!

SSDT: SQL Project Options

To set the properties for your SQL Project, right-click the Project and select Properties. You'll see a window containing several tabs to define your project.
 
Project Settings
Here you can set your normal target platform (SQL 2005, 2008, 2012, or Azure).
You can enable the T-SQL verification option, but this is mostly for Azure projects.
If you will generally output to a script, choose the "Create script" option. On the whole, just using the dacpac file will likely provide greater flexibility in the long run as it can be adapted to a different target. Generating a script will only be guaranteed to work against the target used to generate the script.

You can also set the dacpac file properties, which sadly do not do much when used against a database not using these as data-tier applications. There is some talk of enhancing the projects to use this version number and description somewhere within the database, but right now they are pretty much only there for you to populate if you wish.

 
You can also set the Database Settings for your project. These options are the normal ones you would set when setting options in SQL Server. You can choose to publish these to your targets so set these to your preferences. (More on that in a future post.)



I'm going to bypass the SQLCLR and SQLCLR Build tabs as I am not familiar with them and don’t currently use either in our system.
 
Build
The Build tab is the place to specify which warnings to ignore, the output path for your files when you build the project, and the name of the file. You may want to specify certain warnings to ignore, especially if you use cross-database projects or any references to system databases. Those can often result in warnings about missing objects, even though they'll work perfectly fine when you publish them.

• Note that the warnings are set per configuration. If you use multiple configurations be sure to set the warnings in each one.

 

 
SQLCMD Variables
SQLCMD variables can be used throughout the project as placeholders for values passed in at publication time. For example, you may choose to change the way your scripts run based on your Development, Customer Acceptance, or Production environments. Define these variables here and use them wherever applicable in the project by inserting them as $(DeployType). SSDT will substitute them with the proper value on publication.

 
Build Events
Build events can be set up to run certain commands before and after the project is built. You can also choose to run the Post-Build event commands only when the project builds successfully or on every build.
 
Debug
Start Action and Target Connection String should make sense for those who need to change them. One item to note is that the debug action defaults to a SQL Express type instance of your database that is released to (localhost)\DBName. This will run within SSDT so you can try out your T-SQL before committing the changes in the project.
** Note that if you use FILESTREAM, you will not be able to use these user instances. You will need to change your debug database to a SQL Server install.
In your Deployment Options, you have a screen that looks something like this:

"Block incremental deployment" is one to be aware of. If you run into any constraints, refactoring, data type changes, NULL to NOT NULL, or similar changes, you could have your debug action stop unexpectedly. It can be really useful to avoid dropping columns that could contain data, but if you are aware of the changes, this option could also get in the way of normal releases. According to the MS documentation, this will only block the change if there is data in the table.
"DROP Objects in target but not in project" can be useful to make sure that everyone is running with what's in the project and that they don't have leftover objects that could interfere with testing. However, it's also possible to lose a bunch of work if you publish a project with this option against your local instance containing the stored procedure you've worked on for the last 2 weeks.
"Advanced" contains a lot of different options about what to compare and how to generate the changes. Some of the more interesting options include:

• Allow incompatible platform - this will let you push your changes to a version of SQL Server other than the one specified in the project properties. The changes will fail if you try to do something not allowed in the target, but useful in case you have your project set to the lowest version supported.
• "Drop xyz not in source" - these options control what will be dropped from the target if it's not defined in the source. The default options are likely good for most users.
• "Ignore xyz" - defines what should be ignored when comparing the project against the target. You may want to ignore details about the partition schemes, or what permissions are set in the target
• "Include Transactional Scripts" will set up the release script as transactions when publishing the database. This is useful when you want to make sure that if one part of the publish action fails, the change script is rolled back.
• "Verify Deployment" - stops the publish actions if the script could run into some problems.

 
Reference Paths
This section is used to modify and maintain the various Server & Database variables used with cross-database references. See the "External Database References" page for more detail.
 
Code Analysis
This option can be useful to check for common issues such as using reserved words, non-standard characters in object names, or using "select *" in your queries. You can also specify whether to show these as errors or warnings. Set appropriately for your environment.

SSDT: Importing an Existing Database

To Import From an Existing Database
Import Database into your project by right-clicking the project name or selecting the Project Menu and selecting the Import -> Database option. Note that you can also import from SQL Scripts or an existing dacpac file. One of those may be necessary if you don't have direct access to your source server.

You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.

 
Importing From a Dacpac File
SSDT supports importing from a dacpac file. This can be useful if you're working on a project for a database to which you do not have direct access in order to import the schema directly. These files also can be used as external database references if you work in an environment where multiple databases interact.
In order to create a dacpac file, you have a couple of options.
You can generate a dacpac file from a SQL Server database using SSMS 2012. Right-click the database you wish to use. Select Tasks, then "Extract Data-tier Application…".

Set the file location and any other options you want to use.

Click next. Verify the settings. Click Next again. The system will now generate a dacpac file in the location you specified.
Note that choosing the "Export Data-tier Application" will create a bacpac file. This is used to backup all schema and data in a database so it can be published to an Azure database. You cannot use a bacpac file to import schema into your project.
 
Generating a dacpac using SQLPackage.exe
You can always use the SQLPackage command line to extract the DB Schema into a dacpac file. For a complete list of parameters, see http://msdn.microsoft.com/en-us/library/hh550080%28v=VS.103%29.aspx.
For a quick extraction from a trusted server, you can run something like the following:

Code Snippet

1. Sqlpackage.exe /a:extract /ssn:localhost /sdn:Adventureworks2008 /tf:Adventureworks2008.dacpac

This command uses an "Action" to Extract the file, with the SourceServerName of localhost, a SourceDatabaseName of Adventureworks2008, and a TargetFile of Adventureworks2008.dacpac

SSDT: Creating a New SQL Project

Download the latest SSDT package if you do not have the product installed already:
http://msdn.microsoft.com/en-us/data/tools.aspx
 
To create a new SQL Project, you can either start by creating a new Project or create a new project from an existing database.
When creating a brand new project, be sure to choose the "SQL Server Database Project".

 
This is not the same as the Database -> SQL Server Project template. Using this will create a usable DB Project, but not the newer SQL Project.

 
Once you've created a new, empty project, you'll need to populate the project in order to be able to use it. You have a couple of options. You can import from an existing database, import from a Dacpac file, or just start creating your objects if this is a completely new project.
 
To Start a New Project from a Connected Database
Open your "SQL Server Object Explorer" window. Connect to the database server containing the database for which you want to create a project. Right-click that database and choose the "Create New Project…" option.

You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.

SSDT: Why Use SQL Projects?

What Are SQL Projects and why use them?
SQL Server has always been lacking in a good solution to control versions of your database. We make changes directly to the database and if we're good, we remember to take backups. If we're really good, we save scripts so we can make those changes elsewhere. Too often, we resort to tools for schema differences or try to save up all of the scripts to replay them later. Both have some advantages. Schema differences guarantee that you can make two environments look the same. Scripts can be replayed in order to keep the systems in sync. However, a schema difference doesn't handle data changes well and can't handle making partial changes when not everything is ready to promote. Sets of scripts can also work well, but fail if you end up needing to push sub-sets of those scripts because certain functionality isn't going to be released yet.
Borrowing from Gert Drapers' excellent discussions on Database Development Challenges:

• Databases are inherently stateful
  • ALTERs are generally run instead of DROP/CREATE
  • Dependencies can greatly complicate the changes and scripts
  • Data needs to be persisted
• Databases are often not integrated well with the application development life cycle
• DB Versions are frequently not persisted or managed well
• SQL Scripts may not handle different SQL Server versions well

Sequential Change Scripts

• Required to run in a set order
  • Script 1, Script 2, Script 3, …
  • Can't do Script 1, Script 4, Script 9
• Validating the end state after script runs vs. an expected end state can be complex
• Could easily miss a script in the sequence without knowing
• Handling a release where scripts need to be "skipped" can cause cascading dependency problems
• Can require extensive use of IF NOT EXISTS statements
• Manual process
• Point in time model
• Used by systems such as DBDiff

Declarative Model

• The model is the desired end state
  • Compare the current state of the target against the desired end state
  • Use the differences to generate an upgrade plan
  • Use the upgrade plan to generate a change script, correctly ordered to bring the target up to the desired end state
• Generated / Programmatic process
• Always current
• Used by Red Gate, Microsoft, DB Ghost

Microsoft and Red Gate have both provided solutions to store versions of database schemas. While Red Gate offers a good product, it also requires some external tools to provide a complete solution for schema control. Until recently, Microsoft's solution wasn't accessible to everyone because it required a copy of Visual Studio to handle even the basics. This improved in Visual Studio 2010, but still required a separate purchase. With SQL Server 2012, Microsoft introduced SQL Server Data Tools and SQL projects.
With SQL projects, developers can store a version of their database objects in a source control system of their choice. Tables, stored procedures, views, and even permissions can be stored in such a way as to provide upgrades to databases and even build a new set of databases from scratch. This could even be used to build and release these databases continuously in an automated fashion to release new code.

Tales of a Lazy DBA–MySQL, SSIS, and “0” Dates

We’ve recently been tasked with converting a lot of MySQL Data into our system from a multi-tenant MySQL Database. We don’t have a fixed schedule to actually execute the imports because they’ll be on a “per customer” basis.  Well, that sounded like a great task for SSIS.  I set up the ODBC driver, connected, set the initial “sql_mode” options for our connections to (‘mssql, allow_invalid_dates’) and started to work.
First problem we ran into with an ADO.NET connection to MySQL and writing a SELECT * from schema.table was when we hit a MySQL “Date” column containing a value of ‘0000-00-00’.  SSIS threw an error, not sure what to do.  Thanks to some others who have solved this problem, I realized that within the MySQL Select statement, we could do something like:

CASE date_created WHEN '0000-00-00' THEN NULL else date_created END as date_created

to pass those dates over as NULL. That solves the implicit conversion to datetime (SQL 2005) and avoids the invalid dates.  I ran something similar for a “Time” column to:

CAST(Time_Created as char(8) ) as Time_Created

 
So that solved one particular table export with about 20 or so CAST and CASE statements.  Needless to say, I wasn’t looking forward to doing this for another 500 tables with a total of almost 6000 columns.
I finally set up a really basic query to generate most of the SELECT statements we would need to pull our MySQL data across without too much pain. Admittedly, a small step and I’d still need to copy/paste when I set up each new ADO.NET source, but it worked reasonably well. I’m adding the code snippet for MySQL here in case anyone else has a similar problem.

select

CONCAT(CASE WHEN ordinal_position = 1 THEN 'SELECT ' ELSE ', ' END,

 CASE Data_Type WHEN 'date'

 THEN CONCAT('CASE ',column_name,' WHEN ''0000-00-00'' THEN NULL else '

    ,column_name,' END as ',column_name)

 WHEN 'time' THEN CONCAT('CAST(',column_name,' AS CHAR(8) ) as ',column_name)

 ELSE column_name END, CASE WHEN Ordinal_Position <> MaxOrd THEN ''

 ELSE CONCAT('

    FROM MySchema.', c.table_name) END

    ) as Select_Column_Name

from information_schema.columns as c

JOIN (select table_name, MAX(ordinal_position) as MaxOrd

        from information_schema.columns

        WHERE Table_Schema = 'MySchema'

        GROUP BY Table_Name) as t1

    ON c.Table_Name = t1.Table_Name

where table_schema = 'MySchema'

order by c.table_name, ordinal_position LIMIT 0, 50000;

I’d love to hear other ideas if anyone has encountered this before and come up with a more elegant solution for translating “0” Date or Time data from MySQL into SQL Server.