SQL Server 2008 High availability which method to implement?



Dear all
We are now studying the High availability implementation on our SQL Server 2008
I know that there are the following ways or methodologies:
1.      Database Mirroring
2.      Log Shipping
3.      Replication
We have only 1 instance of SQL server which contains about 10 databases, 2 of these databases contains heavy transactions.
Our ERP system is Microsoft Dynamic GP 2010.
Actually I don’t know what is the best method to implement?
Please I need your advice since that I am new to High availability stuff.
Thanks,
RamzyNEbeidAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
go for Database Mirroring.
0
dwkorCommented:
The answer as usual - "it depends" on requirements and budget.

Database mirroring - works on the database level. Only single destination. Mirrored database is not accessible (you can create database snapshots though). Clients can automatically failover in some modes/setups. Almost 0 data loss with synchronous mirroring. You can also have the issues with 10 databases - everything would depend on the load.

Log Shipping - database level - multiple destinations. Destination database can be accessible in read only mode. Data loss is limited based on how often you backup/ship the log. No automatic failover for the client

Replication - works on the data level. Everything depend on the setup

Failover clustering - instance level. Single point of failure - disk drive system. Usually people implement failover clustering with another method - such as log shipping.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:


it is up to your business decision (how much money can you lose if your system is down),

 your budget $$$
 your Infrastructure architecture:

e.g. is your server is VMware or physical server ,EMC\SAN implemented

for VMware - there are several tecnics for SQL Server 2008 High availability
for EMC\SAN
 another -- snap, clone
--also where would you like to have 2nd server to be - in the same Datacenter or somewhere far away ..

--
Probably Cluster can be good - no need to change code as for mirror
you may like to adopt combination: of all  the methods

Depends-- all about $$$ and support (again $$$)

High Availability Solutions Overview
http://msdn.microsoft.com/en-us/library/ms190202.aspx
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

RamzyNEbeidAuthor Commented:
- i have 1 server on the same data center & another server on diffrent location
 - yes - it is very important not to lose any data.
 - our server is blade server.
 - Transactions are too big.

thnaks,
0
Ramesh Babu VavillaCommented:
In High Availibility every one had there own advantages and disadvantages,

Database Mirroing is a great option but implementing Mirroring for 10 databases, makes high netwok utlization and makes your server busy and slow,

Logshipping can be implemented, but check you network bandwidth

replication is good option to impletment, but should be care with closr monitoring,
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Database MIrroring in asynchronous mode had the following advantages over other solutions:
> You don't have to handle the mess that is Windows 2008 cluster layer.  Windows 2008 clusters in the perspective of SQL Server hosting is full of bugs.  The have pulled out a zillion fixes but the product is still unstable especially on the storage layer and RHS.
> Failover is reduced to a minimum and can easily be automated.
> Database Mirroring is less disruptive on the transactional throughput source system.
> Database MIrroring is less likely to be deprecated than log shipping in future versions.  Especially with the AlwaysOn architecture ini SQL 2012.

Hope this helps...
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
In SQL 2008, database mirroring has greatly been enhanced since the changes are now compressed and streamed to the mirror.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
probably logshipping for you case is the best (vs mirror no code change - if you use DNS alias - just DNS alias IP change in DR )
check network between your Data centers
if it is slow :
you may need to consider alternative ways - including tape overnight  delivery ..

---
trans log size can be smaller if you run more often tran log backup (check your trn back schedule)

--
BTW:
what is your SQL Server 2008
edition and service pack (some editions do not have all options from above posts)?

is VMware or physical server?

also there are extra new methods based on your network and Data Center locations in miles or km
0
CarlosDominguezCommented:
What edition do you have? Do you have one or two licenses?
If you have enterprise edition, I think that asynchronous mirroring is the choice.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<probably logshipping for you case is the best (vs mirror no code change - if you use DNS alias - just DNS alias IP change in DR )>>
@Eugene

Perhaps you meant something else or perhaps you were not aware that latest ADO allows declaring both principal and mirror instance names in the connection string once, making failover automatically handled both on the server and the client side , hence no code change at failover time.

Regards...
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Racimo: .. I am aware about ADO connection string for mirroring
howere this will require code change in many applications (in big companies it can take 100s hours and at least 6 months.. to make this "simple change")

DNS alias change takes no time and no code needs to be changed
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
OK I see your point now.

But I still don't understand how DNS Alias based failover strategy makes it more complicated to use mirroring than log shipping.  When we can rely on a solid routing network strategy (a luxury in most companies I worked for), then any failover or disaster site recovery plan is made simpler.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
if you have logshipping - the 2nd server has different name let say Server2 : if you set your primary sql server named Server1 with DNS alias as Prod1 server -- all connection string is using Prod1 even the real server name is Server1
and one day server1 is down and your 2ndary Server2  is the main server and you just going in DNS and set Ip address of Prod1 to server2 .. -- all apps that are using name (alias) Prod1  are happy ..

--
about this Q: we are still waiting for RamzyNEbeid answers\clarification at least about what sql server 2008 edition he has  so we can continue thuis forum
see


Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability
0
RamzyNEbeidAuthor Commented:


Hi all
Our SQL version is SQL Server 2008 Enterprise edition SP2
Yes we have more than 1 license; I am not worry about it.
We have two solutions for the implementation:
Solution 1:
1 - Two Windows Servers clustered with same Data storage (Blade Servers)
2 – One Windows Servers with its Data storage (Blade Servers) in another Data Center
Solution 1:
1 - Two Windows Servers with Two Data storage (Blade Servers)
2 – One Windows Servers with its Data storage (Blade Servers) in another Data Center

 - it is very important not to lose any data.
 - Transactions are too big.
I need you support and you advice what is the best method (s) to use?
Thanks,
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
go with
Solution 1:
1 - Two Windows Servers clustered with same Data storage (Blade Servers)

and check logshipping and\or mirriring and \or replication as extra insurance for DR (as backup for backup)
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Really depends on your budget.  I'd go for the following (If I had the budget):
> Clustering with redundant SAN block level replicated storage between  the two sites with Active Passive failover configuration failover between the two sites.  In this solution, you must have a rock solid F5 routing scheme and fiber optics between the two sites.  0 data loss, and a full application  transparent DSR.  
0
RamzyNEbeidAuthor Commented:
i want to tell you that 2 Sites are away from each other by 100 KM.
so i dont know which one is better Log shipping or replication ?
also i think that if i used "Databse mirroring Synchronous Replication"  it will slow performance specially that we have a lot of transactions the whole day?

thanks,
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<also i think that if i used "Databse mirroring Synchronous Replication"  it will slow performance specially that we have a lot of transactions the whole day?>>
Which is why I wrote: Database MIrroring in asynchronous mode.

100KM distance between the two sites can be considered as WAN.  In your case, choose Asynchronous Database MIrroring over Log shipping.  In SQL 2008, Asynchronous Database Mirroring main advantage over log shipping is that it streams and compresses exchanges between the sites.

Traditional replication is irrelevant in the context of DSR planning.

Hope this helps
0
RamzyNEbeidAuthor Commented:
i concluded from what you said the following:
1 - the best was is to use Log shipping
2 - mirroring is not the very best becuce i will use asynchronous mode
3 - replication is irrelevant in Disaster recovery plan
4 - then where can i use replication?

thanks,
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
1- No.  I recommended Asynchronous Database MIrroring not Log Shipping.  
2 - No.  Aynchronous Database MIrroring is best in your situation because it compresses streams and Log Shipping does not.
3 - Correct
4 - Replication is mainly useful for offloading reporting from the OLTP server to another server based on a per table basis.  Replication primary purpose is not to guarantee some kind of failover solution.

Hope this helps.
0
RamzyNEbeidAuthor Commented:
but what if for any reason Asynchronous Database MIrroring did not save the data in the DSR site then i wiill lose data?

in my understanding that log shipping will be great becuse it takes the log file and upload every 15 minutes.

in a lot of transactions Asynchronous Database MIrroring will make a big load on the Source server ?

i am sorry for asking a lot but i am new to this subject.
thanks
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<but what if for any reason Asynchronous Database MIrroring did not save the data in the DSR site then i wiill lose data?>>
Asynchronous Database MIrroring (especially compressed) has better RPO than Log Shipping since the transactions are continuously streamed as opposed to Log Shipping which is done periodically and requires file transfer.  It is better suited for biger transactional loads on the source.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Asynchronous Database Mirroring is also less intrusive on the source.
0
RamzyNEbeidAuthor Commented:
Racimo

can i use both Mirroring and log Shipping why?
if any case happned and Database MIrroring in asynchronous mode failed
then every 15 minutes log shipping starts and send the log to the destination databse.

the the log shiiping will add all the failed transaction automatically?
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<can i use both Mirroring and log Shipping why?>>
Never tried.  Don't overcomplicate things.  Risk Zero does not exist.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You can't use both on the same database target.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
<<can i use both Mirroring and log Shipping ?>>
Yes, you can

<
A given database can be mirrored or log shipped; it can also be simultaneously mirrored and log shipped. To choose what approach to use, consider the following:

How many destination servers do you require?

If you require only a single destination database, database mirroring is the recommended solution.

If you require more than one destination database, you need to use log shipping, either alone or with database mirroring. Combining these approaches gives you the benefits of database mirroring along with the support for multiple destinations provided by log shipping.


If you need to delay restoring log on the destination database (typically, to protect against logical errors), use log shipping, alone or with database mirroring.


This topic discusses considerations for combining log shipping and database mirroring.

>
more

Database Mirroring and Log Shipping
http://msdn.microsoft.com/en-us/library/ms187016.aspx
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
one more :
how fast network between 2 datacenters do you have?

what sql server 2008 edition do you have ?

please check
Features Supported by the Editions of SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx

here you will see if you have Enterprise edition you have fully functional sql server
with Backup compression to reduce yourfull and trans log backup files
fully functional but if the mirrored database is experiencing high transactions -you will not have very stable situation
--

Logshipping hase for DR much more then mirror - go for logship with replication of most important tables...

Log Shipping vs database mirroring-- you will get all what you want to know why vs why?

http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/ee05954e-0934-4305-8936-b9226e231d06/




also

Replication primary purpose -- replicate data and data changes -- save data : can be very critical during DR situations
so as the idea you can have on top of logshipping replication as well (all depends on your servers power horses number, etc.)
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<If you require only a single destination database, database mirroring is the recommended solution.>>
I guess we can all agree that database mirroring and log shipping serve different purposes.  Database Mirroring is more relevant in the context of higher availability and disaster site recovery.  Database mirroring is more architecture oriented than log shipping.  OTOH, Log Shipping seems more versatile until next version of SQL 2012 where multiple target are allowed in mirroring.  I'd go with database mirroring anyway for the fact that mirroring is designed to become the main model in 2012 and I don't believe it is a good idea to mix both (that would increase the administration required to maintain).  IMHO.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I guess the question could be rephrased as : do you really need more high availability or do you need more offloading read load from your OLTP server.  If the first one is more relevant then go with database mirroring.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
RamzyNEbeid: it is up to you now
please check all above and make selection from the posted menu
0
RamzyNEbeidAuthor Commented:
dear all
i want to thank you for your replies.

i have another thing to say.

if i go for asynchronous database mirroring and log shipping in the same time.

so as i understand that all transaction will be send to the DRS and commited in the same time log shipping is wroking every 15 minutes so it will not duplicate data?

thanks,
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
no duplicate data in the same destination :
on mirrored one
on logshipped another
copy
0
RamzyNEbeidAuthor Commented:
Here is an example, let us say user creates invoices:
Main Site      DRS Site
1000      1000
1001      1001
1002      Failed
1003      1003
1004      Failed
1005      Failed
1006      1006

Using asynchronous database mirroring invoice 1000, 1001, 1003 and 1006 are mirrored to our DRS.

In the same time 1002, 1004 and 1005 are failed, here comes the Log shipping it will take these 3 invoices to our DRS site and everything will be great.

This is my understanding?
Please correct me if I am wrong.

Thanks,
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
logshipping is going to 1 server

mirror is going to 2nd - another server

so-- if mirror is bad - you have plan B - logshipping
your choice

I am using for my DR - logshipping (developers do not need to change code) and for reporting and  as extra backup of specific tables:
replications (trans and snapshot)  ..  but in your case mirror or mirror with log shi[pping maybe good
try - see --deside
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
oops typo -- decide ..
0
RamzyNEbeidAuthor Commented:
but i am talking about only 2 Servers
server 1 is the main
server 2 in the DR site

so i will apply both methods on Server 2.

so the above scenario will work fine ? it will not make any confilicts for me.
0
RamzyNEbeidAuthor Commented:
some one told me that if i am going to use Mirroring then the bandwidth must be 1 GB?
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
it depends: e.g. how far is you2nd server? Network speed? Amount of data? etc.
if you can try \test each  method  -do so and decide
0
Ramesh Babu VavillaCommented:
in Database mirroring a continous connectivity will be established between the Principle and Mirorired server,
it even takes the list of things in consideration
1. how busy are you principle server,if your server has heavy transactions then
2. it is advised not to have more than 4 Databases in mirroring, it kills the performance
3. if you dont have good network connectivity , mirroring is going to  kill your day.:(
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.