Consolidating MS SQL Database: Things to remember? 1

[boolean]

Hi,

My current assignment involves the Consolidation of 2 MS SQL 2000 database Servers into one. Most of the work that hsa been done here have been by temporary programers, who seem to have developed a lot of spread-out applications. There is a lot of redundancy and things have to be sorted out and the databases in the 2 boxes normalized and moved into one central Box.

To give you an overview of the stuff here, there are close to 30-40 different web/windows/service type applications in production and quite a lot of them are what I would rate Legacy!! not legacy applications, but apps written in VB/ASP that have existed for quite sometime... Most of the people here are not even sure which ones are used and which ones are not!

Now, the task is to concentrate at the database and reduce the 'number' as much as possible. A couple of things I would want to do right aways is to figure out if there is any way I can figure out as to when a database or a database table or a database SP or for that matter, and database entity has been lastly used. Is there a way this can be figured out??? I know that if the tables had a timestamp column on them, I could look at that but on a general perspective, is there a way an MSSQL Databases' history can be drawn out and then analyzed?

Let me know guys... Also keep posting "Things to remember" when consolidating databases/DB servers....

Thanks
Sham

Sham aka boolean... be practical/be straight... true/false?!

 

[SQLSister]

Profiler can be set up to run a trace that can be analyzed to see what databases, tables, queries etc are called over a period of time. However, it will be a resource drain and it can;t give you past information, only from the time the traces started until it is completed. This can give you are starting point. It will identify frequently occurring tasks but might not show infrequent tasks such as monthly reporting or annual reporting.

Personally I think a better bet is to ask all the users to tell you specifically what databases or applications they use. Then you know which ones to concentrate on.

My first step would be to script put the databases and spend a good bit of time getting familar with the structure of each one. I would look over the data which is actually being stored. More than likely you will find whole tables with no records or whole fields with no data. Likely these are no longer being used.

Make a backup of everything before you make any changes and store it someplace where it won;t get overwritten. Then if you omit something in your redesign that turns out to be necessary, at least you can easily get the structure and original data back. It is also best to not delte anything until your redesign is complete and tested. Why, becasue data won't stop getting entered and changed while you rework this. SO you will need to do the data import twice. The first time to get the data you need into your new structure to use to create your new application. The second time to get the current data when you are ready to go to procdution.

Take copius notes as you examine the databases. The act of taking notes helps you remember things. It also gives you a place to look for things when you start to analyze data types and field sizes. If I knkow I willwant to consolidate data from two separte databases, I will usually make a spreadsheet with the database, table fieldname and datatype for each of the common fields (like address, phone,etc.) It makes it easier to decide how big to make the final version.

Get familar with the application front ends. Since most everything that happens is done with these, you will find from them waht features are avalaible inthe application and more importantly which do not appear to be used.

When I have to familiarize myself with a new database, I sit down and read every single stored procedure, function, and application generated query. I need to know what is happening befoer I can understand how to change things. Yes this is a boring, tedious, time-consuming task. It will save you hours of frustration in the long run. Note things you see off-hand that need changing as you read. Things like cursors, use of @@identity, temp tables instead of table variables, etc. Again these notes will help you decide how to fix the problems as ou consolidate things.
In my notes I write a general description on what each sp and function does. This is especially important if you can't tell from the name.

In a process like this bug-tracking software is invaluable. You can note eachtask you need to do in it when you find it and then you won't forget to do it when the time comes that you are ready for that part. This is a long, multi-step process. You will need to know exactly what you need to do and when. Plus, after you put each task inthe tracker, you have record of exaclty what was done, to show management exactly how complex a task it was. Very helpful during your annual review! (I am assuming you actually then go one to perform the tasks you put in the tracker.) I was amazed at how much better our development process was when we started putting all tasks into the bug tracker, not just bugs. Nonboby forgot anything anymore and people did stuff faster. In a project like this a halfhour or hour aday invested in documenting what tasks need to be done and updating the tasks to show completion or add notes about problem areas is invaluable. Just don't let anybody count these items as actual bugs for performance metrics because they aren't, it's just a handy way to keep track of the many, many tasks you will need to do. I found it much easier to maintain than trying to use project management software.

This kind of task is hard work and very complex. You have to stay organized or it will never get done.

Things to look for:
tables that need to be normalized,
data repeated from database to database,
lack of data integrity checks
Lack of indexes
poorly performing queries and stored procedures
tables not being used
sps not being used
applications not being used
functions not being used
wrong datatypes for the data stored - the worst for this is often date data stored as some character type of data. Almost always this data has not be checked for date validity and will cause problems when converting it to a real datetime field.

I'm sure there's more. I will think of it later and post again.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[boolean]

Thanks SQLsister,

I had thought about most of the things you have laid out. The problem is just that most of the developers are not around! well... thats no big deal... as you said, it would just be all the more tedious and time-consuming... will keep you posted... but people... please do keep posting any and everything you think might be important to take care of when consolidating 2 DB servers into 1!!

Later
Sham

Sham aka boolean... be practical/be straight... true/false?!