Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Which HA option to select?

Author  Topic 

prathap_sv
Starting Member

10 Posts

Posted - 2012-07-26 : 01:48:03
Out of four HA options(logshipping,mirroring,replication,clustering)
, which one is used frequently and what are the scenarios for these four options?

prathap_sv

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 06:10:03
All are used frequently depending on the requirements.
This can depend on how critical the system/data is, the budget and the experience of the staff maintaining.
Also how much effort can be spent on the maintenance.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-07-26 : 15:21:11
Hello prathap sv,

Here is my summaries from several blogs/website I read for long time.
Sorry I can not remember reference URL, but hope it useful to you.
---------------------------------------------------------------------

1) Snapshot is a static read only picture of database at a given point of time. Snapshot is implemented by copying a Page (8KB for SQL SERVER) at a time.
For e.g. assume you have a table in your DB, & you want to take a snapshot of it.
You specify the physical coordinates for storing snapshot & when ever original table changes the affected rows are pushed first to the the snapshot & then changes happen to the DB.

Usage Scenario: You have a separate DB for report generation, and want to ensure that latest data for that is available. You can periodically take snapshot of your transactional database.

2) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server.
If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

For example, it can be used to provide a full copy of your primary environment, typically used as a warm standby that can be manually brought online.
This can be used to provide additional redundancy to your backup strategy.
Log shipping can also be used to offload reporting from a primary server by creating a read only copy of the production database at an alternative location/server

Offers hardware, OS, software, and data fault tolerance (assuming two servers, primary and hot spare)
High reliability
Potential for some lost data (time between log backups). Transaction consistency is not guaranteed.
Manual failover
Switchover is often 1+ hours
Lower Cost

Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.

3) Mirroring which was introduced with 2005 edition, works on top of Log Shipping.
Main difference is the uptime for the standby server is quite less in mirroring.
Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ).
Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

Offers hardware, OS, SQL Server, and data fault tolerance
Automatic and manual failover options availables
Failover can be fast
Mirrored databases can be at different physical locations
Moderate cost

Database Mirroring has the extra benefit of being able to switch roles which Replication does not. It is easily to bring back data to/from Principal/Mirroring.
Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.

4) Replication is used mainly when data centers are distributed geographically.
It is used to replicate data from local servers to the main server in the central data center.
Important thing to note here is, there are no standby servers.
The publisher & subscriber both are active.
Merge Replication used to spreading the workload of an application across several servers, i.e. distributed processing architectures
Merge replication often requires an application that is relatively aware of its environment.
Techniques such as conflict resolution also have to be taken into consideration in order to ensure data consistency across the entire integrated environment

Transactional Replication can be used in a similar fashion to log shipping however you can limit the specific objects that are replicated to the subscriber.
This can be useful if only a subset of tables is required for reporting purposes.

Designed for data, not for schema modifications, because of this, hard to
implement for stand-by server
Replication is unreliable
Manual failover
Potential for minimal lost data (time between transactions). Transaction consistency is not guaranteed.
Switchover is often 1+ hours or more
Lower Cost

Replication can be distributed more that 1 site, while Database Mirroring can't.
Replication can modify data at Subscriber while Database Mirroring can't.
Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.

5) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS.
Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN.
Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s

Failover clustering is an availability technology that provides redundancy at the hardware level and is built on top of Windows Clustering technology, i.e. it is not specific to SQL Server.
For example, the processor blows up on Server A. Fortunately Server A is part of a SQL Server Cluster and so Server B takes over the job of providing the SQL Server Service, within a matter of seconds.
All of this occurs automatically and is transparent to the database users and or application being served by the cluster.
The main difference between Database Mirroring and clustering is that SQL Clustering provides redundancy at the instance level whereas database mirroring provides redundancy at the database level.

Offers hardware, OS, and software fault tolerance
High reliability
Automatic failover
Very fast switchover
No lost data
Works with most application software
Does not offer data fault tolerance
High cost
Coupled with SAN as shared array, can offer great performance
Nodes have to be located physically close together

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
Go to Top of Page

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-07-26 : 15:32:37
Following are my findings about restrictions and limitations of each method.
Many people did not consider about their restrictions and limitations.
Until the day they said "Why don't someone tell us about limitations?" or "Who design this solution?"

Replication
You have to follow strictly these requirements and rules to maintain "SQL Server Replication" working properly.

Resource Needed
1. Network bandwidth
Must have sufficient link bandwidth for replication between Distribution Server and Subscriber.

2. Disk Space (at Distribution Server to store Snapshot and Transaction Changes)
Snapshot require free space at least 1*total size of publisher databases. (recommend 3 * total size of databases)
Transaction Changes require vary / have to benchmark / up to how much transaction/day.

Requirements
3. SQL Server Agent
SQL Server Agent must start and configured to start automatically.

4. Shared Folder (over network on Distribution Server / Used to store Database Snapshot)
Must have write permission to Publication Server.
Must have read permission to Subscriber.
Must enabled firewall for Windows Network File Sharing to Publication Server and to Subscriber.

5. SQL Login and permission (at Publication Server)
Require login with db_owner database role on the publication database or sysadmin server role to replicate data.
Require login with setupadmin server role on the publication database to setup(first time).

6. Windows Account for SQL Services (at Publication Server)
Required Windows Administrators Privillege for MSSQLServer service account and for SQLServerAgent services account.
"Local System" and "Network Service" are not usable.

7. xp_cmdshell must be enabled on Distribution Server


Restrictions and Rules
8. Primary Key required.
Every tables can not be published if it does not have Primary Key column.

9. Restoring Database (at Publication Server)
Every time you restore Publication Database, you have to manually reinitialized Subscription Database and synchronized it(take time).

10. Modifying Database (at Subscriber)
Not allow. If conflict occurred, subscriber have to be reinitialized (manually and take time)

11. Create new tables, views, SPs or UDFs (at Publication Server)
They need manually re-configure replication to included these objects into the articles lists (otherwise, new table will not be replicated).
Not complete, until you create new snapshot and reinitialize it to Subscriber, also drop objects at Subscriber.

12. Dropping or Truncate tables, views, SPs or UDFs (at Publication Server)
Not allow, until you remove object from articles lists.
Not complete, until you create new snapshot and reinitialize it to Subscriber.
Also drop objects at Subscriber.

13. Retention & Expiration
In the case lost of communication between Distribution Server and Subscription, the subscription is not synchronized.
13.1 If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours)
The subscription must be reinitialized.

13.2 If a subscription is not synchronized within the publication retention period (default of 336 hours),
the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher.
The subscription must be recreated and synchronized.

Limitations
14. Foreign Key Constrain, Index, Trigger, User Define Rules
Can not be replicated. Creating or Modifying on these objects need to apply at Subscriber manually.

15. Login and Password can not be replicated.
Any changes at Publication Server, should be applied at Subscriber manually.

Mirroring
SQL Server 2005 or newer, Standard Edition or Enterprise Edition.
Mirror database remain in RESTORING State, could not connect to Mirror Database.
Restoring database at any Principal or Mirror database must stop Mirroring Session first
Reinitialize Mirroring needed after restoring database.
Restoring at Principal Database need to manually apply restoring at Mirror Database to continue Mirroring Session.
Cannot mirror system databases(master, msdb, temp, or model). That is mean, logins, linked server, SSIS Packages and SQL Agent Jobs could not be mirror, have to copy manually from Principal Server to Mirror Server
Full Recovery Model must be used.
ICMP Packet(Ping) must be enabled on firewall between Principal Server, Witness Server and Mirror Server.
Database Mirroring allows a Principal and Mirror to occur only in pairs.
Mirror database must be initialized to ensure it is synchronized before start the mirroring session.
Steps.
- Full Recovery Model
- Backup Principal Database
- Restore Mirror Database using NORECOVERY
- Copy all necessary system objects to the instane of Mirror Database (Using SSIS to transfer Logins, SQL Agents Jobs etc.)
Database Mirroring listen on port 5022 for TCP Endpoint.
SQL2005 SP1 required or Trace Flag 1400 enabled on Startup Parameter.


Log Shipping
Primary Database must be in either Full or Bulk-Logged recovery mode.
SQL Server Agent must be started.
Sysadmin can config log shipping.
Network path with Read/Write permission to SQL Server Agent Service Account at primary server, and Read permission to SQL Server Agent Service Account at secondary server
Backup the transaction log independently will disrupt the log shipping process.
Both primary server and secondary server must have enough space to store Full Backup and Transaction Log Backup for specific retention period.
In Standby Mode, user can connect to database. While restoring database(periodically), users forced to be disconnected.



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
Go to Top of Page

prathap_sv
Starting Member

10 Posts

Posted - 2012-07-30 : 01:52:37
hi komkrit,

Thank you very much for your detailed post, Can you please highlight which HA option provides "read access for stand by server"?

prathap_sv
Go to Top of Page

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-07-30 : 22:53:23
Hello prathap,

Suitable solutions for "read access" standby database are Replication and Log Shipping. :)


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
Go to Top of Page

prathap_sv
Starting Member

10 Posts

Posted - 2012-08-01 : 01:14:10
hi komkrit,

Thank you.......

prathap_sv
Go to Top of Page
   

- Advertisement -