August 12, 2011

Microsoft MSSQL Server 2008 R2 can be setup in a High Availability model in a few different ways. The simplest way to this with two server is to use database mirroring. This document will only cover how to setup database mirroring with manual failover in synchronous high-safety mode between two server running Microsoft MSSQL Server 2008 R2.

Assumptions

· A server is already installed with Microsoft Windows Server 2003 or 2007

· Microsoft Server 2008 R2 Software to perform the install.

· The two servers are in a workgroup and NOT a domain.

· The two servers are on the same IP network.

· Remote access has been setup for Administrator.

· Access to the administrator account.

Installing Microsoft MSQQL Server 2008 R2

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

1. Make sure you have Microsoft .NET Framework 3.5 installed before you proceed.

2. Create the Service Accounts that will be used for each MSSQL process.

a. Start->Control Panel->Administrative Tools->Computer Management->Local Users and Groups

b. Use the table below for an example of the user names that need to be created for each SQL Service Account and what check boxes should be checked. You can use different passwords for each or the same one. Make sure you uncheck “User must change password at next logon”.

c. Right click “Users” under “Computer Management (Local)”->”System Tools”->”Local Users and Groups” and left click “New User”.

d. Below is a table that provides some recommend SQL Service Account names and the groups which they should belong to. The assignment to each group will be done during installation. If you don’t do it at installation you can do it afterwards, but it is a bit more complicated.

Service Account	Service Account Description	Group Membership	General Settings
sqlsa	SQL Server Agent Service Account (mssql agent)	· SQLServerSQLAgentUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
sqlsde	SQL Server Database Engine Service Account (mssqlserver)	· SQLServerMSSQLUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
sqlsas	SQL Server Analysis Services Service Account	· SQLServerMSASUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password
Sqlsrs	SQL Server Reporting Services Service Account	· SQLServerReportServerUser$<servername>$<instanceid>· Users	· Password never expires· User cannot change password
Sqlffdl	SQL Full-text Filter Daemon Launcher Service Account	· SQLServerFDHostUser$<servername>$<instancename>· Users	· Password never expires· User cannot change password

3. The group membership bit you will not be able to do at this stage since these groups get created when you install Microsoft MSSQL Server 2008. You can see the memberships that should exists after installation if you look at the table above.

Let’s begin the install now we have SQL Service Accounts.

1. Locate the “Setup” file and double click it.

2. It is recommended to run “System Configuration Checker” before going forward, but not required.

3. This is the output from the “System Configuration Checker”.

4. After you have verified you meet all the requirements for “System Configuration Checker” click “Installation” on the left. On the right side click “New installation and add features to an existing installation”.

5. The first thing that happens is the install does some checks.

6. Click “Install”.

7. Some more checks. Click “Next”.

8. Enter your “Product key” and click “Next”.

9. Check “I accept the license terms” and click “Next”.

10. Choose “SQL Server Feature Installation” and click “Next”.

11. Click “Select All” and then click “Next”.

12. Yet another set of checks. Click “Next”.

13. Select “Default instance” unless you want to specifically name your instance.

14. Disk Space check is preformed. If you have enough space click “Next”.

15. This is where we use the SQL Service Accounts we created earlier. Refer to the table at the beginning of the section to properly populate these account names.

16. Choose “Mixed Mode (SQL Server authentication and Windows authentication)”, click “Add Current User”, and then click “Next”.

17. Click “Add Current User”, and then click “Next”.

18. Make sure “Install the native mode default configuration” and click “Next”.

19. Click “Next”.

20. Yet again we have more checks done. Click “Next”.

21. Click “Install” to do begin the actual installation process.

22. At the end of “Installation Process” click “Next”.

23. If everything went good you should have a similar screen. Click “Close” to finish.

Background on Database Mirroring

Database Mirroring Overview

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Note

You cannot mirror the master, msdb, tempdb, or model databases.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Database mirroring is a simple strategy that offers the following benefits:

Increases data protection.
Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
Increases availability of a database.

In the event of a disaster, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

· Improves the availability of the production database during upgrades.

How Database Mirroring Works

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Transaction Safety and Operating Modes

Operating Modes

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two mirroring operating modes.

One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded. This mode requires MSSQL Server 2008 Enterprise.

This document will only cover how to setup/administer the synchronous high-safety mode.

Transaction Safety

Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode. Since SQL Server Management Studio is used in this document the end result will be synchronous.

If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase.
If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode.

Topology – Synchronous High-Safety Mode without Automatic Failover

All database mirroring sessions support only one principal server and one mirror server. The following figure shows the configuration of synchronous high-safety mode without automatic failover. The configuration consists of only the two partners. This configuration is shown in the following illustration.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).

Note

Establishing a new mirroring session requires that all involved server instances run the same version of SQL Server.

Role Switching

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

· Automatic failover

This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

The role of the witness is to verify whether a given partner server is up and functioning. The witness role is not discussed further in this document.

· Manual failover

This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.

· Forced service (with possible data loss)

Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.

Configuration of Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

To configure mirroring you will need to have two databases that are identical. If you have not preformed a backup of your database you will want to follow these short steps.

Microsoft SQL Server Management Studio

1. Right click on your database in the “Object Explorer”, go to “Tasks”, and click on “Backup…”.

2. In the “Source” section select your “Database” in the dropdown.

In the “Backup set” section give the backup a name and provide a description if you like.

In the “Destination” section click “Add” and pick a location for your backup to be saved to.

Note

The backup file name needs to end in “.bak”.

Click “Ok” to start the backup.

3. Repeat Steps 1-2 but in the “Source” section change the “Backup Type” to “Transactional” and in the “Destination” section use the same file you used for the Full backup (the default backup setting is “appended to the backup file”).

4. Copy the backup file you just made or created before to the server that will start out as the “mirror server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create a database/transactional log backup on the Principal server.

Note

The default database recovery model is “Simple” but we want “Full”.

On the Principal server set the recovery type for each database you wish to mirror.

USE master;
GO
ALTER DATABASE <DatabaseName>
SET RECOVERY FULL;
GO

Example:

PRINCIPAL SERVER:

USE master;
GO
ALTER DATABASE TempDB
SET RECOVERY FULL;
GO

2. Backup the Database on the Principal Server.

BackupType:

Full = Full Database Backup

Log = Transaction Log Backup

To do a Full Database Backup:

BACKUP DATABASE <DatabaseName>
TO DISK = 'C:<DatabaseName-BackupType>.bak'
WITH FORMAT
GO

Example:

PRINCIPAL SERVER:

BACKUP DATABASE TempDB
TO DISK = 'C:TempDB-Full.bak'
WITH FORMAT
GO

Backup the log database too.

BACKUP DATABASE TempDBLog
TO DISK = 'C:TempDBLog-Full.bak'
WITH FORMAT
GO

To do a Transactional Log Backup:

BACKUP LOG TempDB
TO DISK = 'C:TempDB-Log.bak'
GO

Backup the log database too.

BACKUP LOG TempDBLog
TO DISK = 'C:TempDBLog-Log.bak'
GO

Copy the backup file over to the Server2 that we want to be the Mirror server.

Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. Open “Microsoft SQL Server Management Studio” on the “mirror server”, right click “Databases”, and left click “Restore Database…”.

2. In the “Destination for restore” section populate the “To database:” box with the exact same database name that you preformed the backup on, on the principal server.

In the “Source for restore” section click the radio button “From device:” and click the button on the far right.

3. You should get a “Specify Backup” window. Click “Add”, locate the backup file you copied to this server, and click “Ok”. Click “Ok” one more time.

4. You should be back to the “Restore Database – <database name>” window. If you are you need to check the box in the restore column for the file you just finished added.

On the left hand window under “Select a page” click “Options”.

Make sure you select the transactional log and the full backup.

5. In the “Recovery state” section click the radio button for “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” and click “Ok”.

6. You should now see your databases in a state of “Restoring”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Restore database/transactional log backups.

Assumption:

We are going to assume you have copied it over to Server2 and have it in the root of the C: drive.

BackupType:

Full = Full Database Backup

Log = Transaction Log Database Backup

2. To Restore a Full Database Backup:

RESTORE DATABASE <DatabaseName>
FROM DISK = 'C:<DatabaseName-Full>.bak'
WITH NORECOVERY
GO

Restore Full Database Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Full.bak'
WITH NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Full.bak'
WITH NORECOVERY
GO

3. To Restore a Transactional Database Backup:

RESTORE LOG <DatabaseName>
FROM DISK = 'C:<DatabaseName-Log>.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

Configuring Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are going to be mirroring should be visible. Right click on the database you want to setup database mirroring. Go to “Tasks” and then to “Mirror…”.

2. Click on “Configure Security”.

3. Click “Next”.

4. Choose the radio button “No” because we will not be using a witness server. Click “Next”.

5. The dropdown “Principal server instance” is likely already selected to be the name of the server you are connected to and if not make sure it is.

I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <mirror server hostname> <DatabaseToMirror> Mirroring (i.e. Server2 TempDB Mirroring).

Click “Next”.

6. The dropdown “Mirror server instance” needs to be set to the name of the mirror server. After setting it click connect.

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <PrincipalServerHostname> <DatabaseToMirror> Mirroring (i.e. Server1 TempDB Mirroring).

Click “Next”.

7. Leave all of these boxes blank and click “Next”.

8. Click “Finish”.

9. MSSQL will create a Data Mirroring object under “Server Objects”->”Endpoints”->”Database Mirroring” on the Principal and Mirror servers and make the necessary database changes. If those changes are done successfully you should have a screen similar to what I have below.

Click “Close”.

10. You will receive a warning similar to the one shown below. Click “Start Monitoring” to tell MSSQL to attempt to initiate/complete the mirroring connection.

11. Click “Yes”. MSSQL is just warning you that you are not using a Fully Qualified Domain Name (FQDN). To use a FQDN you need to connect to everything using a FQDN via “Microsoft SQL Server Management Studio”. This also means you need to have a domain setup. This guide is intended for setup outside a domain.

12. If mirror was setup successfully you wil see the status window now says “Synchronized: the databases are fully synchronized.” If you have the same status I have described you can click “Ok”. If you do not you will need to trouble shoot the issue.

13. You should now see the following status “Principal, Synchronized” on the “Principal Server”.

14. You should now see the following status “Mirror, Synchronized / Restoring…” on the “MIrror Server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create endpoints on both servers

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Example:

PRINCIPAL SERVER:

CREATE ENDPOINT “Server2 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

MIRROR SERVER:

CREATE ENDPOINT “Server1 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

2. Set partner and setup job on mirror server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring

Note

The Database monitoring default interval is 1 minute.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

3. Set partner and setup job on principal server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring

Note

The Database monitoring default interval is 1 minute.

Example:

PRINCIPAL SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

Administering MSSQL

Managing Logins and Jobs After Role Switching

Only content within the principal database is mirrored. Associated information in the master or msdb system databases cannot be mirrored. Such associated information includes jobs set up against the principal database and logins that are added to the principal server.

If such information is important to supporting role switching, the information should be duplicated at the mirrored site. If it is possible, after roles are switched it is best to programmatically reproduce the information on the new principal database. The most common issues are logins and jobs.

· Logins

For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

· Jobs

Jobs, such as backup jobs, require special consideration. Typically, after a role switch, the database owner or system administrator must re-create the jobs for the new principal database.

When the former principal server is available, you should also delete the original jobs from the new mirror database. Jobs on the mirror database fail because it is in the RESTORING state and so it is unavailable.

Note

The partners might be configured differently, with different tape drive letters or such. The jobs for each partner must allow for any such differences.

Mirroring States

During a database mirroring session, the mirrored database is always in a specific state (the mirroring state). The state of the database reflects the communication status, data flow, and the difference in data between the partners. The database mirroring session adopts the same state as the principal database.

Throughout a database mirroring session, the server instances monitor each other. The partners use the mirroring state to monitor the database. With the exception of the PENDING_FAILOVER state, the principal and mirror database are always in the same state.

The possible mirroring states of the database are as follows:

Mirroring state	Description
SYNCHRONIZING	The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
SYNCHRONIZED	When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
SUSPENDED	The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.SUSPENDED is a persistent state that survives partner shutdowns and startups.
PENDING_FAILOVER	This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
DISCONNECTED	The partner has lost communication with the other partner.

How to Manually Failover the Database between Principal and Mirror

To manually failover the database between the principal and mirror server you will need do the following.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are mirroring. Right click on the database you want to failover to the mirror server. Go to “Tasks” and then to “Mirror…”.

2. If you simply click the button “Failover” the failover will happen. If you are mirroring the log database for your database you will want to failover that database first (i.e. database = TempDB, database log = TempDBLog).

Using Microsoft Transact-SQL (T-SQL)

When the mirrored database is synchronized (that is, when the database is in the SYNCHRONIZED state), the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.

On the principal server first we will set the database context to the master database and then initiate an immediate transition of the mirror server to the principal role.

Note

On the former principal, clients are disconnected from the database and in-flight transactions are rolled back (meaning lost).

USE master
GO
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER
GO

Example:

PRINCIPAL SERVER:

Failover any <DatabaseName>Log databases first.

ALTER DATABASE TempDBLog SET PARTNER FAILOVER

Failover the main database after you have successfully failed over the Log database.

ALTER DATABASE TempDB SET PARTNER FAILOVER

How to: Force Service in a Database Mirroring Session

Caution

Forced service is strictly a disaster recovery method. Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately. If forcing service risks losing significant data, we recommend that you stop mirroring and manually resynchronize the databases.Forcing service suspends the session and starts a new recovery fork. The effect of forcing service is similar to removing mirroring and recovering the former principal database. However, forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes.

In high-performance mode and high-safety mode without automatic failover, if the principal server fails while the mirror server is available, the database owner can make the database available by forcing service to fail over (with possible data loss) to the mirror database. This option is available only under all the following conditions:

Or said differently

We are going to assume Server1 (Principal Server) is dead and now we want to change Server2 (currently in a mirror mode) into a “Principal Server”. To do this we will have to execute a SQL Query because I cannot find any documentation on how to do it within “Microsoft SQL Server Management Studio”.

Using Microsoft Transact-SQL (T-SQL)

To check the current failover state and Safety Settings:

SELECT SD.name, SD.database_id, SD.state_desc, SDM.mirroring_state_desc, SDM.mirroring_role_desc, SDM.mirroring_safety_level_desc, SD.recovery_model_desc
FROM sys.database_mirroring AS SDM
JOIN sys.databases AS SD
ON (SDM.database_id = SD.database_id)
WHERE mirroring_guid IS NOT NULL

Example output (Principal Server):

Example output (Mirror Server):

To force service in a database mirroring session

Connect to the mirror server and issue the following statement:

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The mirror server immediately transitions to principal server, and mirroring is suspended.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
ALTER DATABASE TempDBLog SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

References:

Technical Information:: http://technet.microsoft.com/en-us/library/ms187875.aspx

Leave a Comment

August 11, 2011

Poor man’s query logging (from http://www.mysqlperformanceblog.com)

Filed under: database, linux, mysql, perl — lancevermilion @ 5:51 pm

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with

--log

(or

--general-log

in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by

--log-slow-queries

parameter (MySQL 5.1 requires also

--slow-query-log

), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many databases out there run with none of these and it would require a restart to get the logging in place and possibly another restart to disable it when no longer necessary (though actually slow log can be disabled by simply setting

long_query_time

MySQL variable vale high enough).

So what can be done when you really need to see the queries, but can’t afford any downtime?

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s my quick one-liner which I will write in multiple lines:

garfield ~ # sudo /usr/sbin/tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

The output may contain little garbage, but it can be easily filtered out.

Obviously this method works only when applications communicate with MySQL through TCP sockets. When localhost (not to be confused with 127.0.0.1) is used as a MySQL host, this will not work since all traffic goes through a unix socket file.

It’s most definitely not a MySQL log replacement, but can be very useful if you need just a few minute dump.

Maciek

Here is sample output

tcpdump: listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
110811 17:47:32     325 Connect     bill@10.168.192.90 on
                    325 Query       select @@version_comment limit 1
110811 17:47:38     325 Query       SELECT DATABASE()
                    325 Init DB     accounting
                    325 Query       show databases
                    325 Query       show tables
                    325 Field List  records
110811 17:48:15     325 Quit

Leave a Comment

mysqlsniffer :: MySQL network protocol sniffer

Filed under: centos, database, linux, mysql, perl — lancevermilion @ 5:49 pm

You’ve got a production database server, and you can’t enable query logging… so how do you see the queries being executed against the database?

The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You’ll have to do a little compiling, but it’ll be worth it. Note that this will not usually work for local connections, although you are welcome to try.

mysqlsniffer is a tcpdump clone specifically for dumping/sniffing/watching MySQL network protocol traffic over TCP/IP networks. mysqlsniffer is coded in C using the pcap library and works with MySQL version 4.0 and newer. mysqlsniffer is the only MySQL-specific network sniffer.

mk-query-digest also understands the MySQL protocol. It’s not a sniffer, though. It reads packet dumps from tcpdump like a slowlog. If you want to analyze queries from the wire (i.e. from network traffic), mk-query-digest is what you want.

For more information about the MySQL protocol read MySQL Internals ClientServer Protocol.

mysqlsniffer latest version Aug 18 2006 v1.2 (what’s new) via mysqlsniffer.tgz if you don’t want to follow the instructions below.

First, you need to install libpcap-dev (libpcap-devel for Redhat/Centos), which is the development library that allows an application to sniff network packets.

Ubuntu/Debian

sudo apt-get install libpcap-dev

Redhat/CentOS

sudo yum install libpcap-devel

Now let’s make a directory, download the source code and compile it

mkdir mysqlsniffer
cd mysqlsniffer
wget http://hackmysql.com/code/mysqlsniffer.tgz
tar xvfz mysqlsniffer.tgz
gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful)

To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it’s eth0.

sudo /path/to/mysqlsniffer eth0

Ah, now there we are… all sorts of query information, without having to restart MySQL.

Here are the full options for the command:

Usage: mysqlsniffer [OPTIONS] INTERFACE

OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this

mysqlsniffer Capture Example

MySQL network protocol traffic from the command “mysqladmin -u root -h 127.0.0.1 processlist”:

Note: The capture interface here was lo instead of eth0.

mysqlsniffer listening for MySQL on interface lo port 3306
server > 127.0.0.1.3051: ID 0 len 70 Handshake  (Caps: Get all column flags, Connect w/DB, Compression, )
127.0.0.1.3051 > server: ID 1 len 38 Handshake (new auth) <user root db (null) max pkt 1073741824> (Caps: Long password, Get all column flags, LOAD DATA LOCAL, 4.1 protocol, Transactions, 4.1 authentication, )
server > 127.0.0.1.3051: ID 2 len 7 OK  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 17 COM_QUERY: show processlist
server > 127.0.0.1.3051: ID 1 len 1 8 Fields
        ID 2 len 24 Field: ..Id
        ID 3 len 26 Field: ..User
        ID 4 len 26 Field: ..Host
        ID 5 len 24 Field: ..db
        ID 6 len 29 Field: ..Command
        ID 7 len 26 Field: ..Time
        ID 8 len 27 Field: ..State
        ID 9 len 26 Field: ..Info
        ID 10 len 5 End  (Status: Auto-commit, )
        ID 11 len 49 || 1 | root | localhost:3051 | NULL | Query | 0 | NULL | show processlist ||
        ID 12 len 5 End  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 1 COM_QUIT
mysqlsniffer exited cleanly.
17 MySQL packets captured (469 bytes)

Loads of stuff starts flying by… let’s filter it out a little more so we can just get the queries and not all the excess data.

$ sudo /path/to/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=”
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Original source code and more information at:

http://hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.

Leave a Comment

MySQL Tools (from http://mysqlhack.com).

Filed under: database, mysql, perl — lancevermilion @ 5:31 pm

MySQL Tools

The MySQL distros come with extra tools/scripts: mysqldumpslow, mysqldump, mysql_zap, etc. I’ve never known a DBA for whom these tools sufficed. The open-source community has created a sizable number of MySQL and MySQL-related tools which fill pretty much every niche and need. As a full-time developer of such tools, these projects are my speciality. All code listed here is free and open-source.

MySQL Forge is a repository for MySQL projects, tools, code snippets and other odds and ends. In my opinion, it’s not focused enough to be useful which is why I maintain this page. The tools here are ones that I know top MySQL professionals use regularly and recommend.

Hack MySQL Scripts

These are the scripts I created many years ago when no such (better) scripts existed. They have since been retired and replaced by Maatkit tools. I leave them here for historicity and because they are mentioned in several books (one of which was published in early 2010).

Tools

mysqlreport – Make easy-to-read MySQL status reports.
mysqlsla – Simple, lightweight MySQL log parsing.
mysqlidxchx – Checks tables for unused indexes.
mysqlsniffer – MySQL protocol sniffer.

Profile

Project: none, archived here
Status: retired, replaced by Maatkit tools
Tested: no
Documented: yes, online and included with releases
Language: Perl
Design: procedural, partly modular

Maatkit (mk)

Maatkit is a collection of 20+ scripts originally created by Baron Schwartz. These scripts include mk-table-sync, mk-query-digest, mk-archiver, mk-heart, etc. I am the full-time developer and maintainer for all the scripts. Baron and myself are employed by Percona which sponsors most Maatkit development.

Tools

mk-table-checksum – Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
mk-table-sync – Synchronize MySQL tables efficiently.
mk-query-digest – Parses logs and more. Analyze, transform, filter, review and report on queries.
mk-archiver, mk-find, mk-heartbeat, mk-kill, mk-parallel-dump, mk-parallel-restore, mk-slave-delay, mk-upgrade, …

Profile

Project: Maatkit
Status: active, full-time, professional development
Tested: yes, over 2,000 tests
Documented: yes, POD in each tool, POD reproduced online at each release
Language: Perl
Design: procedural, highly modular

boots

Boots is an extensible command line database client for Drizzle and MySQL. It looks and feels almost like the old, venerable mysql cli, but it has modern features like “lingos” and pipes. In my opinion, it’s the MySQL-esque cli of the future for the MySQL-esque RDMS of the future, Drizzle. It still works with MySQL, though.

Profile

Project: boots
Status: active, professional development
Tested: yes
Documented: in-program help, not really documented yet since it’s still in devel
Language: Python
Design: object-oriented, extensible

Percona Projects

Percona develops and sponsors various MySQL tools, programs, patches, etc.–including Maatkit. These range from backup programs to data recovery tools. There’s hardly a way to summarize them all, so just check out the project page.

Profile

Project: Percona Projects
Status: active, full-time, professional development
Tested: unknown
Documented: somewhat online, larger documentation effort currently in progress
Language: C, C++, Perl
Design: various

Aspersa

Maatkit once had a masse of “utilities” which didn’t really belong in Maatkit proper, so Baron moved them to separted project: aspersa. Most of the tools are not directly MySQL-related, but they’re in the ecosystem so I’ve included them here. (If nothing else, they were born from a pure MySQL project: Maatkit.)

Tools

bt-aggregate – Aggregate and print GDB backtraces.
iodump – Compute per-PID I/O stats for Linux when iotop/pidstat/iopp are not available.
mext2 – Columnize output of mysqladmin.
snoop-to-tcpdump – Formats ‘snoop’ output the same way as ‘tcpdump’.
More tools added as needed…

Profile

Project: aspersa
Status: developed as needed
Tested: no
Documented: minimal
Language: Perl, bash
Deisgn: whatever works

Kontrollkit

Kontrollkit is a collection of scripts to simplify daily MySQL DBA responsibilities. Like Maatkit, it is a suite of many tools backed by a business: Kontrollsoft.

Tools

kt-backup-parallel – runs mysql backups in parallel super fast, has lots of reporting features.
kt-backup-ai – runs mysql backups standard mysqldump method but adds nice features like email reporting and backup completion checking as well as compression, also adds disk space checking and size analysis before running the backup to ensure you don’t use up 100% partition space with a backup.
kt-check-replication – script to report on replication status for slave servers.
kt-connections-log – logs connections to mysql to disk, reports on threshold overages.
kt-flush-tables-sequence – runs through schema.tables to flush in sequence before global flush.
kt-mysql-systemcheck – generates a report for point-in-time system status that is useful for troubleshooting MySQL servers.
Several more tools…

Profile

Project: kontrollkit
Status: active
Tested: no
Documented: no
Language: Perl, Python
Design: procedural

MMM

MMM does monitoring/failover and management of MySQL master-master replication configurations.

Profile

Project: MMM 2.x
Status: semi-active, professional and volunteer development
Tested: no
Documented: yes, POD and online
Language: Perl
Design: procedural, highly modular

innotop

innotop is a ‘top’ clone for MySQL. It was created by Baron Schwartz.

Profile

Project: innotop
Status: no longer in development
Tested: no
Documented: yes, POD
Language: Perl
Design: procedural, partly modular

Schema Sync

Schema Sync will generate the SQL necessary to migrate the schema of a source database to a target database (patch script), as well as a the SQL necessary to undo the changes after you apply them (revert script).

Profile

Project: SchemaSync
Status: active development
Tested: yes
Documented: basics
Language: Python
Design: procedural, modular

rtime

rtime is a program that records and reports query response times by sniffing MySQL protocol traffic. It’s coded by another Percona employee, Ignacio Nin. It’s meant to be small and light-weight, filling a role somewhere between mysqlsniffer and mk-query-digest –type tcdpump.

Profile

Project: rtime
Status: active development
Tested: no
Documented: yes
Language: C
Design: procedural

openark kit

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand. The kit was created and is maintained by Shlomi Noach.

Tools

oak-apply-ri – apply referential integrity on two columns with parent-child relationship.
oak-block-account – block or release MySQL users accounts, disabling them or enabling them to login.
oak-chunk-update – Perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.
oak-kill-slow-queries – terminate long running queries.
oak-modify-charset – change the character set (and collation) of a textual column.
oak-online-alter-table – Perform a non-blocking ALTER TABLE operation.
oak-purge-master-logs – purge master logs, depending on the state of replicating slaves.
oak-security-audit – audit accounts, passwords, privileges and other security settings.
oak-show-limits – show AUTO_INCREMENT free space.
oak-show-replication-status – show how far behind are replicating slaves on a given master.

Profile

Project: openarkkit
Status: active, professional development
Tested: no
Documented: yes, online
Language: Python
Design: procedural

mycheckpoint

Shlomi’s other tool is mycheckpoint. mycheckpoint is an open source monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

Profile

Project: mycheckpoint
Status: active, professional development
Tested: no
Documented: yes, online
Language: Python
Design: procedural

MySQL Sandbox

MySQL Sandbox, created by Giuseppe Maxia, is a set of tools (and Perl modules) that allow you to create isolated instances (sandboxes) of MySQL servers in various configurations (standalone, replication, etc.). MySQL sandboxes are useful for testing because they setup and tear down quickly and are self-contained, not interfering with a default MySQL server and its data, port, socket, etc.

Profile

Project: MySQL Sandbox
Status: active, professional development
Tested: yes
Documented: yes
Language: Perl
Design: procedural, modular

MySQL Cacti Templates

Although I’ve never used and know nothing about Cacti, I know that these MySQL Cacti templates are used extensively. Based on questions and issues I see related to MySQL monitoring with Cacti graphs, it seems that Cacti templates can be difficult to config correctly/well so these templates help make it easier.

Profile

Project: mysql-cacti-templates
Status: active, professional development
Tested: yes
Documented: yes
Language: Perl, PHP
Design: procedural

Leave a Comment

August 12, 2011

Let’s begin the install now we have SQL Service Accounts.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

August 11, 2011

mysqlsniffer Capture Example

Original source code and more information at:

MySQL Tools

Hack MySQL Scripts

Tools

Profile

Maatkit (mk)

Tools

Profile

boots

Profile

Percona Projects

Profile

Aspersa

Tools

Profile

Kontrollkit

Tools

Profile

MMM

Profile

innotop

Profile

Schema Sync

Profile

rtime

Profile

openark kit

Tools

Profile

mycheckpoint

Profile

MySQL Sandbox

Profile

MySQL Cacti Templates

Profile

Recent Posts

Archives

Pages