July 11, 2011 at 4:24 am
I'm migrating our SQL server from version 2000 to 2008 R2 Standard Edition. On 2000 we have 10 databases – each department have his “own” database, common data are replicated from one databases to other. Databases are not big – from 5 to 15 Gb, they are oltp databases.
Question – what do you suggest?
1. To “merge” all databases into one single “big” database.
We have tables & views which have same name in different databases but contains different data (like Invoices). So we must rename them or use different schema and upgrade our programs.
2. Use one database for common data and use views across different databases.
No foreign keys, if I'm not wrong.
3. Use, like now, more databases and replicate common data.
Replication is “time consuming” and we must maintain all databases (backup...).
What about performance, maintenance...?
Any suggestions will be appreciate.
With regards,
Dussan
July 11, 2011 at 9:01 am
Well there is no way on this planet i would try to upgrade sql server versions at the same time I have to restructure anything. It does sound like the system architecture is a bit sloppy but don't make your upgrade impossible by changing architecture at the same time. Upgrade sql server keeping everything as it today. Get everything stabilized. THEN after the dust settles look into changing the architecture to something a bit more friendly. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2011 at 9:06 am
Sean has made the exact comment I have made. Doing the upgrade AND changing the structure in one step is most likely a recipe for disaster. They should be separate projects and managed separately.
The question about the same database or multiple databases is a common one and, in my opinion, there is no right or wrong solution. Each has its own plusses and minuses. Here are some of the things I'd look at when making the decision:
2. Do they share a large number of tables or a small number of tables? (large & small would be a personal decision)
3. Do they share an SLA or have different SLA's? Essentially what is the restore plan you need to have for each database.
4. What is the security model for them? Does having separate databases make managing security simpler and easier to document?
5. What are the loads on the different databases? Is there the possibility that one or more may be generating more load (or may in the future) which might require migrating it to it's own server?
Those are just a few things I'd consider. There are likely more.
Edit: Added the list of things I'd consider.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2011 at 9:21 am
To Jack's list, I would add one more consideration: Is the number of possible databases finite and manageable or is there a possibility that it will get beyond reasonable control?
I'm speaking from experience here. We have a third party in house which creates a brand new database for each and every new project that arises and which the application manages. As we generate new projects on a near-weekly basis this gets rather unwieldy for maintenance, support, recovery, etc.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply