SQL Server Architecture - Restructuring - Suggestion needed

Question

SQL Server Architecture - Restructuring - Suggestion needed

mkalyana.murugan

SSC Enthusiast

Points: 146
More actions
May 23, 2012 at 4:20 am

#392320

Hi All,
In my server I have totally 100 databases on which 7 are core databases. Meaning all the 93 databases will fetch/refer the data from the 7 databases. The databases are keep growing on monthly basis. (ie new database and well as database size),Currently I don't have any High Availability configured. As the dbs are keep growing I am planning to restructure the entire server by adding an instance and separate all the database and keep the core database in the default instance. But I don't know how the users will fetch the data... obviously I can not create linked server for all those users.
Also I don't want the core databases to be available all the time.
Please suggest me... what kind of restructuring I can go for. What would be the best HA, DR I can go for.
(Current server config : 128 GB RAM, 4 CPUs and local + SAN drives with 4 drives 1 TB each)
Thanks in Advance 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sqlbee19 SSCommitted Points: 1597 More actions · Answer 1

mkalyana.murugan (5/23/2012)
Hi All,
In my server I have totally 100 databases on which 7 are core databases. Meaning all the 93 databases will fetch/refer the data from the 7 databases. The databases are keep growing on monthly basis. (ie new database and well as database size),Currently I don't have any High Availability configured. As the dbs are keep growing I am planning to restructure the entire server by adding an instance and separate all the database and keep the core database in the default instance. But I don't know how the users will fetch the data... obviously I can not create linked server for all those users.
Also I don't want the core databases to be available all the time.
Please suggest me... what kind of restructuring I can go for. What would be the best HA, DR I can go for.
(Current server config : 128 GB RAM, 4 CPUs and local + SAN drives with 4 drives 1 TB each)
Thanks in Advance 🙂

First of all,Its all depends on your application's and budget and how much data you can afford losing.

Here is my suggestion,

As you have SAN in your environment think of going with clustering (A-P) for high availablity and for DR think of logshipping setup to different data center(secondary site).

In your case Database mirroring is not supported when using cross-database transactions as you mentionted your large volume of databases depends on core db's.

Check out the following link

http://msdn.microsoft.com/en-us/library/ms366279.aspx

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

1) What is your RPO (recovery point objective)? You want to make sure you don't lose more data than can be tolerated.

2) What is your RTO (recovery time objective)? How much downtime can the users tolerate?

In your environment each of these may be on a DB by DB basis. The core DBs may have a higher (or lower) RPO and RTO depending on how they're used. And..

3) What are the technical restrictions?

You mention that you can't add a linked server for each user so splitting to multiple instances is hard. Remember that linked servers are server side and per instance, not per DB. Personally, mirroring is my go-to technology for HA. It doesn't have any single point of failure (unlike a cluster which has single storage) and has automated failover (unlike logshipping). However, if there are a lot of inter-DB transactions it is a problem because you can't enforce multiple DBs failing over at the same time before 2012. If you can use 2012 check out Availability Groups (which can enforce that).

If you have any flexibility to modify the application design to make it easier to split DBs off to different servers you may want to look into that since constantly adding DBs to a server isn't sustainable. Short of an application redesign, have you looked into replicating the core DBs onto multiple servers so each "user" DB accesses the copy on it's server? I would also be careful doing multiple instances on a single server since when you're looking at server load that doesn't get you anything.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

Good answers above for the HA stuff.

For the core databases: how often does the data change? Would replication work well? You could replicate data from the 7 to the 93 if it's not too real time.

That makes HA more complicated, and you might have to fix things on a failover, but at least you'd have some good data in each database, and if you use replication, you could potentially scale out those 93 databases to other servers if needed.