In my spare time I've been working on a utility for generating SQL scripts and XML data documents.  The utility currently only supports SQL Server 2000/2005, but could easily support any other database that has an OLEDB provider via the MyMeta library.  The main goal of this tool was to produce scripts to be checked into source control, and to generate differential scripts between databases for use in db upgrades (migrations).

 

First and foremost, if you have a migration tool ala RoR ActiveRecord in your environment - use it.  A migrator written in a DSL is going to be much quicker to get going... and since you're working at a higher abstraction level it allows you to target many different databases with the same DSL code.  There are a few out there, so take a look.

 

My first goal was to get a baseline script of our databases.  There are quite a few tools out there for this, but I really wanted the ability to tweak the output of my scripts just so.  I really like scripts that don't error when you run them multiple times, so this generally requires an IF EXISTS before any action.  To get things formatted and written the way I wanted them, I ended up using NVelocity along with a few templates.

 

Secondly, I wanted to use XML to store my data.  I like XML files better than SQL scripts for several reasons:

1. XML files are easier to edit than a SQL script with a bunch of insert statements.
2. Its faster to bulk load XML data then run a bunch of SQL inserts (using SQLXmlBulkLoad).
3. I can edit the XML data in a tabular fashion inside Visual Studio by switching to DataGrid view.
4. Its easier to produce a diff between a table and an XML document than a SQL script and a table.
5. XML is meant to transfer data between systems, so why not use it?

To load XML data into SQL Server I created a wrapper around the SQLXmlBulkLoad COM component.  It works good enough for what I need, however it does make some assumptions about the way things are structured, its not quite as general use as BulkXml. 

The one advantage my implementation has is that when you run the loader, it generates the XSD that the SQL Server bulk load component requires at runtime based off the target table, so there's no need for you to hand write an XSD or worry about checking it into source control.

Here's how you would use SqlMigration to transfer data from one db to another (assuming the target db table is empty):

This creates an XML file for all the data in the customer table on my local machine:
sneal.sqlmigration.console /dir=c:\mydb /xmldata /tables=Customer /server=localhost /db=AdventureWorks

This applies the XML file just generated to my prodsql1 SQL Server.
sneal.sqlmigration.console /server=prodsql1 /db=AdventureWorks /execute=c:\mydb\data\dbo.Customer.xml

Pretty straight forward.  I'm not sure I like the command line syntax since you could potentially run an execute and script command at the same time.  I'm thinking of creating a separate console application for loading data and running scripts, or at least providing a command line switch that sets the mode to either input or output only.

I would like an MSBuild target wrapped around this, but I haven't needed that yet.  Another thing lacking is the ability to point it at a database and say "script everything."  That feature I know I'm going to need here very shortly.  Hopefully I'll have some time to fix the DDL migration portion of the tool, right now only migration (diff) SQL data scripts are supported.

As always, the code can be checked out from Google code via SVN (VS 2005).  Eventually when the tool is a little more mature I'll post a binary.

I thought this looked like an interesting approach to validating your SQL statements in a continuous integration environment.  Basically you execute the script with the NOEXEC option.

I'm not sure I would actually us it though since I think I would prefer to actually run the scripts and and then do some further processing, however maybe this is a good prebuild step?

I'm working on creating a SQL migration tool to help automate some of the drudgery in moving and upgrading databases.  Initially I'm only supporting SQL Server 2000 since this is what I primarily use.

The SQL scripts are being generated from the SQL entities (sort of like a generic SMO) via NVelocity templates.  I think the NVelocity approach is working very well because the C# code only needs to populate the top most object, like Table or Column, and the template can walk the hierarchy db objects as needed.

Right now I have Create/Drop table, Add/Drop/Alter column, Create/Drop index, Create/Drop foreign key complete.  This should be enough to modify a table for the most common scenarios.  Currently the committed code only has SQL script generation via the scripting API, so there's no intelligence currently backing it in regards to migration.  It only will write SQL scripts at this point.

The one thing I'm making sure to do, is to bulletproof the generated scripts so they can safely be run multiple times without error.  Here's an example template which safely adds a column to a table.

/****** Object:  Column [$column.Table.Schema].[$column.Table.Name].[$column.Name] $time ******/

IF NOT EXISTS
(
  SELECT * FROM [information_schema].[columns]
  WHERE table_name = '$column.Table.Name'
  AND table_schema = '$column.Table.Schema'
  AND column_name = '$column.Name'
)
BEGIN
  ALTER TABLE [$column.Table.Schema].[$column.Table.Name]
  ADD $SqlScriptHelper.WriteColumn($column)
END
GO

 

I chose to use NVelocity templates because of their flexibility and the ease of use for generating text documents.  NVelocity is also faster for script generation then SMO (and it actually works unlike SMO), and it gives me the flexibility to do things the non-SQL Server way which sometimes is inappropriate for production systems (change a column's nullability and you'll see what I mean).  One more benefit, is it allows me to customize the output just by modifying the template, no recompile necessary.

Keeping my SQL generation separate from SQL Server will also allow me to re-use this migration tool for other databases like MySql.  It could even potentially allow me to export scripts from SQL Server to MySql syntax since I have an abstraction layer over the database like an ORM would.

What would the minimum set of features be to create a SQL Schema Migration tool?

Add FK Contraints
Remove FK Contraints

Add Non-Clustered Indexes
Remove Non-Clustered Indexes

Add Tables
Remove Tables

Add Columns
Remove Columns

Add Sprocs
Remove Sprocs
Alter Sprocs

Add Views
Remove Views
Alter Views

LHS is a dependency of the RHS

Add dependency order (removal dependency order is the opposite):
Tables -> Columns -> FKs -> Views -> Sprocs

Are there other common scenarios I'm not think about?  Like changing the nullability of a column or adding/removing a default?

Today at work we needed to compare the contents of a SQL table against an FTP directory.  We originally had a process that involved a lot of steps using Excel, NotePad, and command line FTP.

You could create a PowerShell script that iterates the SQL Server data and verifies each file is on the FTP server. This seemed like it would be slow so I opted for running FTP directly from my query (assuming you have xp_cmdshell enabled). Here's an example. Notice it's getting the FTP commands to run from the ftpoptions.txt file.

declare @FilesOnDisk table

(

[File] [nvarchar] (50)

)

 

insert into @FilesOnDisk ([File]) exec xp_cmdshell 'ftp -A -s:c:\temp\ftpoptions.txt ftp.microsoft.com'

 

select * from @FilesOnDisk where [File] is not null

 

Now we can join this temp table data to our local SQL Server table to find missing files on the FTP server...

I encountered a really annoying bug in the SQL Server SMO scripting library today.  Once you script out a single "DROP" script the internal scripter is left in a bad state and starts throwing NullReferenceExceptions on any subsequent call to the Script method.  I haven't found a good way around this yet.  Unfortunately the only reference I can found about this issue confirms the bug but nothing else.  Hopefully this will be fixed in SQL Server 2005 SP3, because it sure isn't fixed in SP2.

Sometimes I like to drop databases locally even when I'm connected to them.  This usually comes in handy when I'm rebuilding a DB using a script.

USE MASTER
GO

-- If the database already exists, drop it
IF EXISTS(SELECT * FROM sysdatabases WHERE name='Northwind')
ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name='Northwind')
    DROP DATABASE Northwind
GO