SQL Server 2005 High-Availability Strategies

Two separate instances (Default + Named) / servers

Log Shipping

A very simple way of keeping a full copy of a database on another system.

#Transaction log:

• It records all data modifications;


• insert + update + delete statement >> transaction log >> the log is checkpointed >> the data is written to the database;


• helpful in maintaining the integrity of the db.


• [primary server] --T-log file--- [secondary/standby server]

Benefits:

• Fault tolerance


• Offloading backups


• Offloading query processing


• Doesn't require extra hardware + provide a full warm spare

Log shipping

does not provide automatic failover.

can keep the primary server fully online at all times. Lengthy, time-consuming backups can be done on the separate server.

standby server -> read-only for query processing; all users need to be disconnected during the data restoration.

Requirements:

databases -> ***full / bulk-logged*** recovery model; cannot be simple recovery model;

SQL server Standard
/
SQL server Workgroup
/
SQL server Enterprise
on all server instances involved in log shipping.

servers involved -> same case sensitivity settings

for Configuration
must be a sysadmin on both servers
steps: (1) backup the primary + restore on the secondary (2) enable log shipping

Monitor Server
a third server;
records the history + status of log backup and log restore operation;
can raise alerts if any of the opations fail;

terms for secondary servers and spares:
cool = can be powered up & restored
warm = standby; automated method to keep up to date;
*hot = a cluster; keep up to date + automatic failover to the secondary server

TIP To decrease the latency between the primary and standby servers, simply change the timing in the schedules of the jobs. On the primary server, change the schedule of the backup job. On the standby server, change the schedules of the copy and restore jobs.

Procedures for changing Roles

1. Manually failover from the 1' db to 2' db



2. Disable log shipping jobs



3. Reconfigure log shipping (if desired) to swap the roles



4. Bring the 2' db online (RESTORE cmd; NORECOVERY for each restore except the last)

Database Snapshots

are only available in the Enterprise edition

created at a moment in time

used for reporting purposes for that given time

concept referred to as copy-on-write;

Purpose and Benefits:

• Protecting data from user error



• Safeguarding data against admin error



• Maintaining historical data for report generation



• Using a mirror db that you are maintaining for availability purposes

EXAM TIP One of the great benefits of database snapshots is the ability to overcome user errors. By creating database snapshots on a regular basis, you can mitigate the impact of user errors such as inadvertently deleting data, or of an administrative error such as what might occur during a manual bulk-load process.

Creating and Using a Database Snapshot

snapshot creation permission ~ db creation permission

E.G.
CREATE DATABASE AdventureWorks_Snapshot_0900 {name + time} ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_Snapshot_0900.ss' )
AS SNAPSHOT OF AdventureWorks;

RESTORE DATABASE name_of_database_to_be_restored FROM DATABASE_SNAPSHOT = 'name_of_the_snapshot';

Database Mirroring

Significant difference bw. log shipping & db mirroring:

datbase mirroring -> automatic failover

Principal SQL server ---State data exchanged // T-Log file --- Mirror SQL server

Requirements:

• sql server 2005 service pack 1



• recovery model <= FULL



• only datbases {except master + msdb + tempdb}; not servers



• supported on SQL Server Standard + Enterprise editions

Benefits:

• no additional hardware



• as witness server => high availability



• using with snapshots, db mirrors can be queried directly by users

Witness Server

to enable automatic failover / high-availability mode;

the db mirroring session must be in synchronous operating mode:
trasaction safety on (synchronous) {high availability}
transaction safety off (asynchronous) {high performance}

Database Mirroring Endpoints

endpoint = sql server object = to facilitate communication within db mirroring connections

a unique listener port

1 server instance <--> 1 db mirr. endpoint <-=> multiple db mirr.

E.G
CREATE ENDPOINT DB_Mirroring_Endpoint STATE = STARTED AS TCP ( LISTENER_PORT = 5088 ) FOR DATABASE_MIRRORING (ROLE=PARTNER);

its default state is stopped

select * from database_mirroring_endpoints

Implementation:

RESTORE DATABASE Chapter10
FROM DISK = 'C:\Chapter10_Full.bak'
WITH NORECOVERY,
MOVE 'Chapter10' TO 'C:\LogSecondary\ShipChapter10.mdf',
MOVE 'Chapter10_Log' TO 'C:\LogSecondary\ShipChapter10.ldf'
RESTORE LOG Chapter10
FROM DISK = 'C:\Chapter10_Log.bak'
WITH NORECOVERY

TIP The WITH NORECOVERY clause is critical. The mirrored database is not restored into a fully functional database, but is instead restored to a state that allows transaction logs to be constantly applied to it.

Replication

It distributes --publication-- parts of tables / views => articles

Publisher Metaphor
publishers + distributors + subscribers +
articles + publications + subscriptions (pull & push)

EXAM TIP When choosing articles for publication, you should also consider the objects you’ve created to optimize them. For example, if indexes were created to optimize the performance of queries against specific tables, you should consider including these indexes in the publication. If they are omitted, the queries could perform poorly on the Subscribers.

Methods:

Merge => tracks changes through triggers & metadata tables; multiple subscribers can update the same data; subscribers need to be able to change data offline & later synchronize changes with publisher; if subscribers require different partition of data; conflict detection & resolution; no access to intermediate data states; allows multiple subscribers to update data;
+
Transactional => changes through transaction log; starts with snapshot; occurs in near real time; access to intermediate data states; high vol. of transactional activity; when the publisher (or) subscriber is not a MSSQL server db;
+
Snapshot => when data rarely changes; out of date data on subscribers are acceptable; only small amounts of data are replicated; large vol. of data changes in a short period;

EXAM TIP The warnings for either long or slow merges over different types of connections can be very helpful in determining if merge replication is experiencing problems due to the network infrastructure. It could be that the merge replication is configured as best as possible, but connectivity problems are preventing merge replication to complete in a timely manner. Creating a warning with an alert allows you to be notified as soon as the problem is encountered.

Tracer tokens are used to easiliy measure latency esp.lly for subscribers.

Clustering

• the ultimate high-availability solution



• automatic failover of teh entire server



• nodes = servers within a cluster



• up to 8 nodes allowed;



• most current cluster config. require that at least one node be inactive; N+1 nodes where N is the no. of primary servers



• costy



• difficult to configure & maintain



• useful when we have one or two large databases

Number of Cluster Nodes Supported in Different Editions:

SQL Server Standard Edition => 2
SQL Server Enterprise Edition => 8
Windows Server 2003 Enterprise => 8
Windows Server 2003 DataCenter => 8