Need a High Availability Option - But Have Std Edition 2

[JohnBates]

hi everyone,

We use SQl Server 2005 Standard.

I need to implement a good high-availability technique.

Is database mirroring working well for anyone that has chose that route? Do you have to use it in conjunction with any other feature (such as clustering)?

Is log-shipping better? Which is easier to set up?

Thanks, John

 

[mrdenny]

Clustering is the High Availability solution. In SQL 2005 clustering in a two node cluster is supported in Standard Edition.

Log shipping will not give you a high availability solution.

Database mirroring can, if you set it up with a witness.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

I am using database mirroring with a witness server. I chose this option because I can create a snap shot against the mirror and use it for reports.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[JohnBates]

Thanks Paul and Denny.

Everything I have read says clustering is complex to set up and manage. And my SQL Server 2005 Admin book (by WROX) says clustering is not viable when the inactive node/server is not local - in my case, the failover server is 15 miles away (but it is on the same domain).

So, I'm seriously looking into db mirroring. * We have the Standard edition of 2005 *. I'm not clear about why a "witness" server is necessary.

- is a 3rd server, the witness, absolutely necessary?
- Does the witness just enable me to read data? I cant create a database snapshot with the Std edition. o fcourse I could do that with snapshot replication.

I guess the big question is ... is a witness server necessary and what does it do for me?

Thanks a lot.
John

 

[ptheriault]

If you want the server to fail over automaticaly then you need a witness. If that doesn't matter then you can just set up an alter to notify you when the primary is unavailable and you could fail over the server manually.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[JohnBates]

Thanks. Now I get it. Just one more question.

What would the witness server look like? Must it have SQL Server installed and if so, does it need to have an instance of the database that's being mirrored ? And how does it sense that it needs to trigger the failover - and by what means does it make the failover occur?

Sorry, that's 3 questions. You have been a big help.

Thanks, John

 

[mrdenny]

Clustering is fairly easy to setup (once you've done it once or twice).

It's pretty hard to setup without both nodes in the same datacenter and connected to the same fabric. It can be done, but it does require some very high end storage hardware and setting up what is called a majority node set.

In your case mirroring (either with the auto failover or not) will be your best bet.

If you want the database to be HA you'll need auto failover.

Setting up mirroring as an HA system will require that you change the connection strings / DSNs adding in the mirror servers information. You also have to use the SQL 2005 driver instead of the SQL 2000 driver. However once it's all setup it should work nicly for you. When I was testing it I was getting subsecond failover (when manually triggered, and with a very low workload).

You can setup the mirror on the backup machine at the backup site in order to save a little money on the extra hardware. It must be running at the backup site because if the primary site goes down and the witness is at the primary site there will be nothing to tell the backup to come online, and your solution won't work.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

mrdenny,

"You can setup the mirror on the backup machine at the backup site in order to save a little money on the extra hardware."

So if I have the mirror on the backup machine .... I would not need a 3rd server( the witness) ?

Please clarify, and thanks very much for your advice.

John

 

[mrdenny]

You still need a witness, but you configure the mirror (backup machine) as the witness as well as being the mirror.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

.... OK I'm making some progress. I ran the ALTER DATABASE SET PARTNER stmts and they completed successfully.

Restored db and log and now shos 'Restoring' on teh mirror server.

End points look good.

But when I run this on the Principal, no recs are returned
Select * from sys.database_mirroring where mirroring_guid is not null

And the log on the principal has: 'Error 1443 Database mirroring has been terminated'

I'm not sure it ever actually started.

Not really sure how to troubleshoot this condition; I probably configured something incorrectly, but what?

Thanks, John

 

[mrdenny]

Sounds like the mirroring isn't actually running.

Right click on the database on the principal and select properties and check the mirroring section and see what it says.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JohnBates]

Success, at last.

I didn't use the Mirror Wizard.
What I learned: You have to run the ALTER DATABASE... SET PARTNER stmt on the mirror server *first*, then Set the partner on the primary server.

I do have a server at the failover site that can serve as a witness but I've not enabled it to be a witness yet. Have to walk before I can run.....

Next, I'll look for ways to detect when the failover has kicked-in and send me an email. I want to have that figured out before enabling the automatic failover (adding a Witness).


Thanks for the help, Denny. It's not difficult; you just have to do the steps in the correct sequence.

John

 

[JohnBates]

mrdenny,

What is the best way to keep master and msdb updated on the mirror? Obviously Jobs and logins need to be in-synch between servers.

Do you script jobs and logins on primary, then run the script at the mirror?

Need to leave the databases online else mirroring will fail, right?

Thanks for your advice - the user databases are nirrored and I was able to failover in my test.

John

 

[ptheriault]

I use sp_help_revlogins. After you create a new login on the primary I run sp_help_revlogins to script all my logins. I then run the script it creates on the mirror server. This ensures that the SIDS match on both servers. As far as jobs go when you create a new job just script it out and run it on the mirror.

There is an FAQ with the code for sp_help_revlogins.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

That's probably the easiest way. SQL 2005 and above have there own version of sp_help_revlogins. You'll need to SQL 2005 version as the SQL 2000 version doesn't work in 2005+.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JohnBates]

.... In an ideal world we would reboot our SQL Servers maybe just once a year.

But in my experience it has been once per month or more often.

A reboot will cause the failover database to come up as "Disconnected", then some steps are required to reestablish mirroring.

How do you handle reboots of your mirrored servers?

Looking for ideas. Thanks, John

 

[mrdenny]

At this company we don't have anything mirrored at this point. In the past I didn't do anything special when rebooting either server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JohnBates]

mrdenny, Did the mirror reestablish automatically after you rebooted the servers? I need to test this at my company to find out what happens.

John

 

[mrdenny]

Yes the mirror brought it self back into sync automatically after either machine was rebooted.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[cjamodeo]

JohnBates, did you find a way to get a notification after a failover? I'm looking to implement this.