We use a single database in SQL Server 2005 and the size is around 190 GB. We plan to spilt the database in following methods;
1. single instance multiple database without synonyms
2. single instance multiple database with synonyms
3. multiple instance multiple database
Need inputs on the below queries:
Which best method we can go for?
Pros and Cons of each method?
Related help links/guides to split the database in the above types?
As any good DBA will tell you, "It depends". Not knowing how your data is distributed (as in, table structure) is a big hindrance to giving good advice. There are advantages and disadvantages to each of the methods you list. You have other options too:
Single instance, partitioned data
High-Availability Cluster
Bottom line is you have to look at:
Data volatility (as in, does the data change a lot),
Data distribution (as in, do you have lots of tables with a bit of data each, or do you have just a few tables that contain the bulk of the data)
Data Usage (as in, are there lots of users hammering it at once, or is it more batch-oriented, or is it more heavily used for reporting and querying).
Budget (as in, how much money do you have to throw at it...licenses and hardware can be expensive).
Strictly speaking, synonyms don't have anything to do with how the database will perform. It does, however, give you some freedom from an administrative standpoint as far as being able to move databases around between servers without disrupting the apps or users.
I can't be any more specific without knowing more about the content of the database. I hope what I've given you helps somewhat. Good luck!
We have 3 applications which depends on this single database. There are some common objects which are used for all the three applications. Apart from this all the other objects are WRT with their applications.
For E.g. Abacus is the Database, RMS, CMS and DSM are the 3 applications.
Currently these 3 applications have the same Abacus database. so at the time of fixes and releases we have to get the downtime for other 2 applications also, and for taking backup and restore it is taking more time. To avoid that, we planned to split the database, per application. So now Abacus will be split in to Abacus_Common(for Common Objects), Abacus_RMS(RMS objects + common objects), Abacus_CMS(CMS Objects + common objects), Abacus_DSM (DSM Objects + common objects).
This is what we are trying to do
Need inputs on the below queries:
Which best method we can go for?
Pros and Cons of each method?
Related help links/guides to split the database in the above types?
Your response gives a slightly better picture of what you're doing. However, I still have to fall back on "it depends". Part of the problem of breaking up data has to do with referential integrity, part volatility and part budget.
So, for example, if you know that the "common" data is shared, what would be the impact on restoring it independent of the other "application" databases? What would be the impact of updating the common data on the other application databases? How much rewriting would have to take place to ensure that referential integrity is preserved? What would be the impact of NOT doing any rewriting?
Frankly, there is no "best" method. Each will introduce problems. If you leave the whole thing alone, that has problems (which you listed above). If you split across servers, that has problems (and cost). If you split databases on the same server, that has problems (as I mentioned above). You just have to decide which set of problems you (and your user community, and your management) would prefer to live with.
As far as links and guides, I believe you can google that yourself. Best of luck to you!
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.