reviews >> coding tools >> Package SQL Server Databases As If They ...

Package SQL Server Databases As If They Were Programs, with SQL Packager

By : Brad McGehee Mar 11, 2005

Every once in a while, a truly unique SQL Server product pops up, one that you have never seen before, and at the same time, provides a clever solution to a common problem.

For example, let's say that you are a developer and you need to include a database (both schema and data) with your software for the customer to install. One option would be to include scripts to create the schema and populate the tables. Another option would be to create a backup of the database that can be restored by the end user. And along this same line, a database can be detached and reattached by the end user.

I have seen all of the options, and a few others, used by software developers to role out their databases. All of these work, but they also assume that a DBA, or similar person, will be available to "install" the database. If the end-user does not have any SQL Server skills, using any of the above options can only lead to frustrated end-users and lots of support calls.

What if you could just provide the end-user with an executable that they could run just like a setup program, to automatically install the database on a SQL Server? Or, what if you had a toolkit that allowed you to embed, within your current setup program for your application, an easy way to install a SQL Server database automatically? In fact, both of these options are available with a new program from Red Gate Software, called SQL Packager.

 

What Can SQL Packager Do For You?

Here's a quick rundown on what SQL Packager can do for you:

• It can take a currently existing database, and then reverse engineer its schema, and data, if desired; and turn it into an executable that can be run on a SQL Server to automatically install a new database.
   
• If you want to include the database install from within your own setup program, you can also use SQL Packager to create C# code templates you can incorporate into your own code. SQL Packager will still automatically reverse engineer the schema, and data, saving you a lot of work.
   
• SQL Packager also has the ability to upgrade pre-existing databases, not just create new ones. For example, say that you need to role out an application upgrade. No problem. SQL Packager can be used to create an executable, or C# code template, to upgrade a database, both schema, and data, if needed.

Up to this point, all of my examples of how you might use SQL Packager have centered around developers and their customers, but of course, it has many other DBA applications, such as:

• Used to role out internally-developed applications.
   
• Used to role development databases to test versions of the same database for QA, user testing, etc.
   
• Used to move databases from one server to another.
   
• Installing the same database on multiple SQL Servers quickly and easily.
   
• Installing read-only databases onto SQL Servers at remote locations.

 

How Does SQL Packager Work?

The best way to find out how SQL Packager work is to try it out, and this is what I'm going to do in the rest of this article. As you will soon see, it is very easy to use.

After installing the program, and when you first bring it up, SQL Packager brings up a wizard to step you through the process of creating an executable or C# template files to automatically install a SQL Server's database's schema and data on another SQL Server. Here's what the initial wizard screen looks like:

If your goal is to create an install package for installing a new database, all you have to do to get started is to specify the server, authentication, and database name of the database you want to work with. In my case, I will use my personal favorite, Northwind.

If you want to create an upgrade package, then you would have to select the "Update an existing database" option and specify which database has the version you want to upgrade, and specify in the "Latest version database" the version of the database that includes the current schema and data. In a sense, this creates a "delta" package that is used to upgrade pre-existing databases.

The next screen in the wizard gives you the option to select which objects you want to include in your install package. The default is for all objects, but you can be selective if you want.