Moving 3 databases into 1. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Moving 3 databases into 1.

We currently have 3 databases that perhaps should be one. This messy setup has situations where the 2 JOB databases that contain…. jobs. A job has a customer id. The customer table resides in the ACCT database. Nice hey. We have a numerous queries and stored procs that have hard coded database names in them to help resolve the queries requiring data from all 3 databases. When I got here we at least moved to using views. I see significant opportunities for savings by moving all the tables into one database. The only thing I see we are giving up is you can restore ACCT without affecting the physical database JOB. Even then if the applications are using all three databases, then is there any benefit to having these separate? I say no. I see no reason not to merge these 3 databases. Does anyone see any pitfalls to doing so? Thanks in advance for your opinions! Rob.

There is no point in keeping saperate databases if they are interelated. Havina all the data in one database will imrove refrential integrity in the database. The maintenance cost (back up etc.) will decrease although the time for backup may remain the same. Since there will be one log file only, its better to maniotain. The user logins will become more consistent. The administrtaor does not have to worry about users in various databases. The administrative overhead will decrease. The only advantage I see is that you can store and resore the data at multiple places. But the same can be done by using filegroups. As far as restoring one database independent of other is concerned, you can use the backup and restore filegroups functionality in SQL Server. So my advice is to consolidate the database and put them into one. Gaurav
Gaurav is right on all counts. One DB will ease ALL maintenance tasks and will definately result in improved performance (reduced cross-talk between databases, streamlined object ownership resolution, I/O efficiency etc). A merger could turn out to be a blessing this time round. Nathan H.O.
… just to add up, if the database gets bigger and bigger then try archiving data (which are used for MIS purposes) to keep up the current DBs performance. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, I agree with Satya. Data archiving had shown immense increase in performance in our case. Gaurav
Thanks everyone for you feedback. I’ve wanted to do this for a while. You have confirmed my thoughts and provided an unbiased third party opinion that I can use to make this a high priority. Thanks again. Rob. Rob Adams
]]>