May 26, 2014 at 12:42 am
Hi!
I want to ask about creating a secondary or standby Database server. I do have two DB server, right now the one server was already setup as Primary and which is up & running. the second server supposedly is to act as standby database if ever the primary goes down... I do know How to make a log shipping but currently I am not confident to take it because I am not sure if this thing could really meet my requirements...
Ideally, to be broad about my requirements, as I mention, i want to have a standby DB server. Let say, If the primary goes down the standby server will up and no need to restore any databases or instances(as you all know it will take time to restore the db right?).
Is anyone would like to give me an advise what should I need to consider before setting up the secondary db?What is the best practice in implementing it and What are things should I know in setting up Standby DB using MS SQL server 2008
thanks in advance.
May 26, 2014 at 6:13 am
Based on your requirement you need to work out; follwoing questions will help you to narrowdown the options:
Howmuch downtime is acceptable?
Howmany Databases on the SQL instance?
What is the storage you are using? Is it SAN?
Configuration of Primary & Secondary hardware are same?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 26, 2014 at 10:01 am
without knowing the questions that free_mascot asked, you really have four choices;
log shipping
mirroring
replication
or a active\passive cluster
Or another idea is going SQL 2012 and using AlwaysOn.
Good Luck
MCSE SQL Server 2012\2014\2016
May 26, 2014 at 7:46 pm
@free_mascot. Thank you for your reply as for your questions:
1.Howmuch downtime is acceptable?
2.Howmany Databases on the SQL instance?
3. What is the storage you are using? Is it SAN?
4. Configuration of Primary & Secondary hardware are same?
1.We allow to have a downtime for about 1hour(restart the primary if needed)
2.We have two instances... The 1st instance includes 30databases and the 2nd instance has two databases.
3.Array setup
4.Referring to physical(hardware) not really. it is more advance than the primary server. however, the configuration & setup is the same as the primary db
Any suggestions?
--------------
@lkennedy76. thanks also for your reply. We are using SQL server 2008 ENT. EDition and I believe the setup in the secondary should also the same as the primary DB. Can you give me atleast the pros and cons of each choices you mention.
Also,as what I mention above, What are the choices Do you think is the best?
Thanks again.
May 27, 2014 at 8:31 am
miaka102 (5/26/2014)
@free_mascot. Thank you for your reply as for your questions:1.Howmuch downtime is acceptable?
2.Howmany Databases on the SQL instance?
3. What is the storage you are using? Is it SAN?
4. Configuration of Primary & Secondary hardware are same?
1.We allow to have a downtime for about 1hour(restart the primary if needed)
2.We have two instances... The 1st instance includes 30databases and the 2nd instance has two databases.
3.Array setup
4.Referring to physical(hardware) not really. it is more advance than the primary server. however, the configuration & setup is the same as the primary db
Any suggestions?
--------------
@lkennedy76. thanks also for your reply. We are using SQL server 2008 ENT. EDition and I believe the setup in the secondary should also the same as the primary DB. Can you give me atleast the pros and cons of each choices you mention.
Also,as what I mention above, What are the choices Do you think is the best?
Thanks again.
It really depends on your situation. Since you do not have SQL12 AlwaysOn is not an option.
Log Shipping - I have used it but when things get out of sync it can be a big headache, or at least it has been for me.
Mirroring - I have also used this, if it gets unsync'd it usually only takes a couple of minutes to recover. SQL Operator \ Alerts \ Mail will need to be setup to alert you when there is an issue. This could be an option for you because you can fail it over when Primary has an issue over to the secondary.
Replication - Oh Replication, its a love hate relationship for me however there are allot options to consider, below is a link to the article so you can read up and take a test drive.
http://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx
Clustering - an active passive node is what I would pick. The active \ passive option for you would be the choice however you will need to check what type of licensing you have for SQL and failover licensing options.
MCSE SQL Server 2012\2014\2016
May 27, 2014 at 7:36 pm
The information you had provided is really useful to me...As you know, we wanted to take in a standby DB and I think Log shipping will be fine they don't need an automatic, they rather prefer to turn it on manually.
I read a lot of forums about it and most of them use it for reporting purposes only. In our end, our objective is, if the primary goes down, we wanted to act it as a backup
Since you'd experience to work on log Shipping, Can you please give me an advice on this?
Besides, I want to clear up something about log shipping
1.How Do I know if the log shipping is working?
2.How I can measure if the data is upto date?
3.Can I have a monitoring log or alert if there is wrong in restoring data?
4.If ever the primary goes down, How do I switch the standbydb to production?
thanks again.
May 28, 2014 at 2:45 pm
below is a link to how to configure log shipping and all it's options. This should answer all your questions.
http://technet.microsoft.com/en-us/library/ms190640.aspx
MCSE SQL Server 2012\2014\2016
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply