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.
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. |
|
|
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/serverOffers 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 architecturesMerge 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 environmentTransactional 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 toimplement 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 YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
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?"ReplicationYou have to follow strictly these requirements and rules to maintain "SQL Server Replication" working properly.Resource Needed1. Network bandwidthMust 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.Requirements3. SQL Server AgentSQL 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 ServerRestrictions and Rules8. 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 & ExpirationIn 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.Limitations14. Foreign Key Constrain, Index, Trigger, User Define RulesCan 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.MirroringSQL 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 firstReinitialize 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 ServerFull 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 ShippingPrimary 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 serverBackup 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 YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
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 |
|
|
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 YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
prathap_sv
Starting Member
10 Posts |
Posted - 2012-08-01 : 01:14:10
|
hi komkrit,Thank you.......prathap_sv |
|
|
|
|
|
|
|