Module 9 Maintaining High Availability

 

Availability is not the same as uptime

Means that the service provided by the server is available weather or not the server is up.

 

Factors that effect availability of your database service:

-         software failure

-         hardware failure

-         network failure

-         natural disasters

-         terrorism

 

Things you can do:

-Hardware redundancy (RAID)

-network redundancy

-server and database redundancy (clustering, db mirroring, log shipping)

 

Clustering:

-Feature of OS not SQL Server

-What is clustering?

-What are considerations to keep in mind if you intend to use clustering as an available solution?

Virtual Server:

-two servers that appear as a single server.
            -each of the servers needs to have it’s on distinct computer name.

-clients connect to the virtual server

-virtual server passes connection on to which ever server is the primary server

-if the primary server fails the request is passes to the second server.

-Windows Server 2000 (data center) supports a MAX of 4 nodes in a cluster.

-Windows Server 2000 (advanced server edition) only supports 2 nodes to a cluster.

-Server 2003 8 nodes

 

-SQL server can install on multiple notes.

            -Enterprise Edition 8 nodes

            -Developer Edition 8 nodes

            -Standard Edition 2 nodes.

You want to coordinate SQL server & the edition of Windows server here.

 

In diagram there is a physical disc sitting between the two physical servers.

That disc is shared storage.

 

SQL Server is installed to that shared storage.

There is also a cable between the two servers…

There must be a second network between the two servers so that the servers can monitor each others health.

 

Considerations:

 

Hardware

-if the hardware you want to use is not on the hardware compat list for clustering it will not work.

-make sure the hardware you you want to us is on this list.

 

Clustering is a feature of the OS

-need to set the OS up for clustering

Shared Disk
-shared storage can be a single point of failure.

-Make it as available as possible… ups, RAID, etc.

-Since the shared storage is not built into one of the computers it is helpful to have a  fast connection between the servers and the storage.

 

Installing SQL Server in a cluster

 

-once you have the OS set up for clustering SQL server KNOWS its being set up in a clusterd environment.

-Your installing it on the Virtual server not on a specific box.

-If your installing multiple instances you can only put one instance of sql in each resource group.

-the resource group is…a set of resources that make up the virtual server.

 

Database Mirroring

 

- faster/better than clustering for failover.

- Alternative to failover clustering

            -faster failover

            -no dedicated server required

                        -secondary server holds the mirror and does nothing else (unless you want it to)

            -no shared storage (so no single point of failure)

            -hardware doesn’t have to follow compatability list

-Mirror Server

            -hot server

-Witness Server (optional)

            -Provides auto failover (w/o failover is manual)

-Automatic redirection

-for clients that use the sql native client (.NET 2.0)… redirection to new server is automatic.

Note: with a mirror you can pick which databases, with a cluster you get them all…

Also, a cluster is on a server level, a mirror is on a database level.

 

Options

 

Mode

Automatic Failover

Protection From
Data Loss

High Availability

Yes

Yes

High Protection

No

Yes

High Performance

No

No

 

-“High Availability” Requires a witness server

Two things to think about need Autofail over or total protection from dataloss

-Depending on how many transactions you want to loose.

 

Availability Automatic failover requires witness server, nothing is lost.

Protection from data loss specific configuration does not get auto failover or witness server

Performance no Auto, no protection from data loss

 

Database Mirroring Endpoints

 

-first endpoint must be created on the mirror.

-create endpoint… (needed on witness as well) on all servers involved

Note: as far as I can tell this is a special kind of account that is only used by mirrors.

-work as addresses for mirroring communication

CREATE ENDPOINT endpoint_mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 7022 )

    FOR DATABASE_MIRRORING (

       AUTHENTICATION = WINDOWS KERBEROS,

       ENCRYPTION = SUPPORTED,

       ROLE=ALL);--witness can go here

GO

 

Preparing Servers for Database Mirroring

1) create endpoints and logins

2) set recoverymodel to full

3) backup DB and restore on mirror

4) Manually copy over server-level resources

Establishing a Mirror Session

1) Set the principle server on the mirror server (set partner=endpoint execute on mirror)

2) set the mirror server on the principle server

2a) witness using a set witness=

3)Change the session mode from High Protection (default)

            -Add a witness server here for high availability

                        -enables auto failover

            -Set partner safety off for high performance mode

                        -possible dataloss during failover.

 

3/3/08 Stopped at 2780 M9 3.6 (4:32pm)

3/4/08 Started  at 2780 M9 3.5 (8:18am)

 

Handling Failover

Automatic Failover

            -Mirror and witness agree that the primary is down

            -Auto failover occurs

                        Former principle becomes the mirror if it returns to operation.

Manual Failover

            -Object Explorer in Management Studio

                        ALTER DATABASE AWData

SET PARTNER FAILOVER

Forced service (on mirror server)

            ALTER DATABASE AWData SET PARTNER

            FORCE_SERVICE_ALLOW_DATA_LOSS

 

Log Shipping

Involves two servers

Does not involve auto failover

Failover w/o loosing transactions except those not written to the log.

Basic idea:
When the TL is backed up on the prod DB the TL is automatically restored to the DB on the standby server.

Then the job on secondary server restores the log to the stand by DB.

 

Two Tools:

            -SSMS

            -TSQL (run on primary)

                        Sp_add_log_shipping_primary_database

                        @database = N’AdvWorks’

                        ,@backup_directory = N’c:\logshipbackup’

                        ,@backup_share=N’\\server1\logsshipbackup’

                        ,@backup_job_name= N’LogShipBackup_AdvWorks’

Switch Server Roles when there is a failure

1.      Copy transaction log backups

2.      Backup Primary DB

BACKUP LOG AdventureWorks TO AdvWLogBackup

WITH NOTRUNCATE

3.      Restore primary database on secondary server

RESTORE LOG AdventureWorks FROM AdvWLogBackup

WITH NORECOVERY –(Until last log)

4.      Disable log shipping jobs

5.      Configure log shipping on new primary server.

 

Demo:

Primary Server is \\MIAMI

Network path: \\MIAMI\LogShipSrc

Local path: C:\LogShipBackup\LogShipSrc

Job Name: LSBackup_AdventureWorks

 

Secondary Server is \\MIAMI\SQLINSTANCE2

Secondary DB: AdventureWorks

Destination Folder: \\MIAMI\LogShipDest

Job Name: LSCopy_MIAMI_Adventureworks

Restore Job Name: LSRestore_MIAMI_Adventureworks

 

Finished M9 at 9:45am