October 21, 2010 at 2:13 am
Hi guys,
I am new to this forum and pretty new to SQL Server 2008. I have been asked to look into setting up a high availability database.
What I have are 2x Dell Poweredge Servers both with Windows Server 2008 Standard 64bit and 2x SQL Server 2008 Workgroup.
I basically want to setup a database so that if server1 goes down, server2 will kick in and continue to service the software that will run off this database. I have read a bit about the high availability and its apparently a big feature in 2008.
I have had a search around the forum and havent found anything that can help me yet. Hope someone can point me in the right direction to the best of way of doing this.
Thanks in advance. Ben
October 21, 2010 at 2:19 am
Database mirroring is what you want. Have a read through Books Online, there's a lot of info in there on Mirroring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 2:51 am
Thanks, I will take a look.
Will database mirroring allow automatic fail over to the mirrored DB?
October 21, 2010 at 3:05 am
It can, depending which mode you run it in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 4:25 am
Reading through the MS notes, it is saying Automatic failover requires 3 individual servers, principal, mirror and witness. Is this true, as we have only purchased 2 servers.
Can it be configured to auto failover with just 2?
Thanks again, much appreciated.
October 21, 2010 at 4:37 am
Automatic failover requires three instances. With only two you can only have manual failover.
The witness doesn't need to be a server. A cheap desktop PC is more than adequate. There's no load on it, it just needs to give the quorum for failover.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 4:42 am
Or maybe not...
I just noticed that you have workgroup edition of SQL. Workgroup cannot be mirrored at all. It also can't be clustered.
If you want failover, you're going to need to up that licence to Standard edition at minimum. Workgroup is not intended for the 'high availability' situation.
Best you can do with workgroup is log shipping, which has no automatic failover and does not have a secondary that's always completely the same as the primary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 4:45 am
Would the best way to install SQL on 3 machines then backup the DB on the 1st server, then restore the DB to the other 2 machines so they all have the same instance? Then setting up the mirroring on all 3.
Sorry about this! Never done anything like this before. The notes on MS are not that easy to read.
October 21, 2010 at 4:54 am
bh 58161 (10/21/2010)
Hi guys,I am new to this forum and pretty new to SQL Server 2008. I have been asked to look into setting up a high availability database.
What I have are 2x Dell Poweredge Servers both with Windows Server 2008 Standard 64bit and 2x SQL Server 2008 Workgroup.
I basically want to setup a database so that if server1 goes down, server2 will kick in and continue to service the software that will run off this database. I have read a bit about the high availability and its apparently a big feature in 2008.
I have had a search around the forum and havent found anything that can help me yet. Hope someone can point me in the right direction to the best of way of doing this.
Thanks in advance. Ben
If you've never done anything like this before, then it would make sense to hire someone to help. Blindly attempting to implement these solutions and getting them wrong will result in high expenditure, no results and possibly you typing your resume!!
The software you currently have is not designed to be used for any high availability.
Are you providing high availability for one database or for many?
bh 58161 (10/21/2010)
Would the best way to install SQL on 3 machines then backup the DB on the 1st server, then restore the DB to the other 2 machines so they all have the same instance? Then setting up the mirroring on all 3.
No!! That's not how mirroring works, as already stated how many user databases do you need to provide HA for?
bh 58161 (10/21/2010)
Sorry about this! Never done anything like this before. The notes on MS are not that easy to read.
You would be wise to look at my suggestion above!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2010 at 5:04 am
bh 58161 (10/21/2010)
Sorry about this! Never done anything like this before. The notes on MS are not that easy to read.
See my previous comments about the editions. Is pointless going on about this when you can't implement any of this because of the edition of SQL you have..
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 5:07 am
Just 1 database.
I know restoring it isn't how mirroring works. I meant to restore it so all have the same instance then configure the mirroring within the management studio.
We are changing the software from Workgroup to Standard edition so it can be done.
Personally I am just after any advice you experts can give me as I want to do this myself to get the experience rather than hiring paid help. We all have to start somewhere!
October 21, 2010 at 5:07 am
Double post sorry.
October 21, 2010 at 5:34 am
bh 58161 (10/21/2010)
I know restoring it isn't how mirroring works. I meant to restore it so all have the same instance then configure the mirroring within the management studio.
Um, no, that's not how you start a mirroring setup. Please read up on configuration of mirroring. Get it wrong and you have start over and you've wasted time.
I'm pretty sure you can find some mirroring tutorials on the net that give step-by-step instructions. There may even be one in Books Online.
Personally I am just after any advice you experts can give me as I want to do this myself to get the experience rather than hiring paid help. We all have to start somewhere!
Sure, but a HA project is not necessarily a good thing to start on with no experience. There's no faster way to learn than to sit with someone who knows what they are doing.
If you insist on doing it yourself, first set up two test instances (on cheap hardware/local machine) and try the setup, break it, fix it, fail over, fail back, crash one instance, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2010 at 6:32 am
bh 58161 (10/21/2010)
I know restoring it isn't how mirroring works.
Actually that's exactly how mirroring a database starts!
bh 58161 (10/21/2010)
II meant to restore it so all have the same instance then configure the mirroring within the management studio.We are changing the software from Workgroup to Standard edition so it can be done.
Personally I am just after any advice you experts can give me as I want to do this myself to get the experience rather than hiring paid help. We all have to start somewhere!
Research mirroring more before attempting it!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2010 at 6:35 am
Thanks for your advice. Which one of you is correct, one says that isnt how it starts and the other says it is??
I am waiting for my colleague to purchase standard then i will get it tested before implementing.
Unfortunately this has been dumped on me at short notice and i have 3 days to test, which is why i have come to forums for advice first.
Thanks again.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply