Some ideas

Topics: Database Publishing Wizard
Nov 6, 2006 at 10:53 PM
I used to work with a company that was working on a VERY large project. It is still going. When I started with them it was in the days of SQL Server 4.x and they need something just like the Database Publishing Wizard -- they called it PrintDB. It was a great tool and I have often needed something like it since leaving. It appears that the Database Publishing Wizard is heading in that generally direction.

It had a couple of features that you may want to consider.

1. Rather than scripting the entire DB, we could elect to generate the tables, the keys (PK and FKs), the SP, permissions, the Views and the data to separate files. We could also generate the drops in a different file to the creates/inserts. When scripting the data we could nominate which tables had lookup/reference data that needed to be scripted and which tables contained run time data that did not need to be scripted.

This meant that if we needed to do a heap of data migration we could readily drop all of the keys, move the data and then re-apply they keys.

Also, these files were configured in source safe. This meant that any changes to the database could be tracked.

2. Their database was BIG. Many hundreds of tables, probably thousands now. The software was spilt into subsystems. Each subsystem was a major software engineering project in itself. PrintDB had a special table in which we could nominate which tables, views and SPs belonged to which subsystem.

In this way we could readily perform maintenance on a subsystem and configure it in sourcesafe in isolation to all other subsystems.

I was hoping SQL Server 2005 would feature something like this. Although the scripting is much more powerful it still falls short. It appears that the Database Publishing Wizard is not adding that much more functionality other than the data scripting.

I feel with a bit more effort this could become a great tool that solves swathe of db admin problems rather then just publishing databases to hosted environments.

Dave A