x

Regarding High Availability for a 3TB Data environment

Hi guys,

A client of ours has about 70 MS SQL databases on a bunch of single servers running. The client wants to consolidate all those databases into 1 large database. Data wise, we are talking about 3 TB. The client insists High Availability as the data is critical.

I've setup a couple of clusters [Active\Passive] in the past, but I don't know if that's the right choice in this case.

The Client suggested to run the database on 2 or 3 instances. As I'm someone who is specialized in Oracle. I thought that was the main difference with Oracle, that it's not possible to run a database on several instances with MS SQL.

I'd love your intake on this one.
more ▼

asked Jan 26, 2012 at 11:43 AM in Default

Freddy gravatar image

Freddy
36 2 2 4

(comments are locked)
10|1200 characters needed characters left

6 answers: sort newest
i think, Log-shipping is not a better choice. as mirroring does better that Log-shipping. I would recommend you to set up cluster for database access availability. Mirroring, replication would cater for disaster recovery. for performance, you can use RAID 10. number of spindles, enough RAM, proper configuration of RAM, Indexing and stats, regular maintenance.
more ▼

answered Feb 03, 2012 at 03:05 PM

Zahid gravatar image

Zahid
91 9 10 11

(comments are locked)
10|1200 characters needed characters left

Sacred Jewel, thanks for the quick response !!

more ▼

answered Feb 03, 2012 at 02:52 PM

Freddy gravatar image

Freddy
36 2 2 4

You would have got a much quicker response if this has been a new question. Question was with full details, nice image, but of little use here. BTW, I cannot see your application A connect to failover if the NETWORK lines goes down? So in this case, the application would still be connected to the principal?
Feb 03, 2012 at 03:08 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

For

"Will the principal know in this case that the mirror server became the principal and execute a role switch on his own?"

No. The principal would then work as the mirror, and its database becomes the new mirror database. (But if some users or some application is still connected then that is a seperate issue. Since WITNESS is disonnected, the automatic failover will happen if configured)

For "Does the principal at that point become the mirror database as the mirror database was previously forced by the witness to become the principal"

Same as above. But After a role switch, certain metadata must exist on both partners to ensure that all of the database users can access the new principal database. In addition, backup jobs must be created on the new principal server, to ensure that the database continues to be backed up on its regular schedule.

For "If for a weird reason, both the mirror & principal have transactions on each server that haven't been applied to the other side .. Will there be data loss at that point? or what?"

That would depend upon the situation, if there are users connected to the former principal, and some other users connected to the new principal servers, this may lead to a scenario, where data loss could be the result.
more ▼

answered Feb 03, 2012 at 02:25 PM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

@Freddy Question in another question...do not know how it will be dealt with. You should post it as another question. Some people would not even look into it as there are already 4 answers against it.
Feb 03, 2012 at 02:34 PM Sacred Jewel
O no....Now since my answer is voted up, the question is now standing above the question. What a bad...
Feb 03, 2012 at 02:56 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

Guys, I have a rather urgent question.

I setup a design with 2 datacenters where the witness server and the Mirror server are in site B, the principal server in site A. The layer above will be an application server that pushes data from the application server to the database.

Possible issues that he network architecture suggested was:

1) If the network line between the two sites is down ==> Principal disconnected from the witness and mirror. What will happen at that point with the principal as the witness will force the mirror to open and become Active?

=> Will the principal know in this case that the mirror server became the principal and execute a role switch on his own?

2) When the network line between the sites comes back up. Does the principal at that point become the mirror database as the mirror database was previously forced by the witness to become the principal?

The network architecture wants to be sure that both servers are not working as principals when the network line comes back up.

3) If for a weird reason, both the mirror & principal have transactions on each server that haven't been applied to the other side .. Will there be data loss at that point? or what?

Can anyone help?

alt text

Design.jpg (86.3 kB)
more ▼

answered Feb 03, 2012 at 10:30 AM

Freddy gravatar image

Freddy
36 2 2 4

You should have posted this as a new question to get a much faster response.
Feb 03, 2012 at 02:02 PM Sacred Jewel
Hmmm. Moderator I may be, but I don't seem to be able turn an answer into a question. It'll have to be reinput. Sorry.
Feb 03, 2012 at 02:57 PM Grant Fritchey ♦♦
@Grant Fritchey My bad... But thanks for looking into it.
Feb 03, 2012 at 03:04 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

I agree with @Chris Shaw's hints on making sure the business/client says what is required. They need to be crystal clear on this so that you can choose the right solution.

Going by what you have said, I would suggest consolidation onto an server in DC1 and have DB-mirroring to a second server in DC2. This gives you safety across sites (DC1 is down, so use DC2). Added to this, DB-Mirroring can be setup so that the failover is transparent to the client software (involves a small change to the connection string of the application).

You can further improve availability by making the servers in each location active-passive clusters, but this raises your admin (although the passive nodes are essentially free of license costs for SQL Server).

As @Chris Shaw also stated, SQL 2012 will make this a little better/easier with the AlwaysOn. This rolls the HA features into one feature pack and allows you to specify what you want and be less concerned with how SQL Server achieves those goals.
more ▼

answered Jan 27, 2012 at 01:45 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x91
x46
x20

asked: Jan 26, 2012 at 11:43 AM

Seen: 1706 times

Last Updated: Feb 03, 2012 at 03:09 PM