Category Archives: Database Mirroring

Shrink or truncate Mirrored Database Log File in SQL Server 2005


Here is script to truncate all your database. We run it using SQL Server Agent job. Works like a charm. We use it to truncate transaction logs when database running with SQL mirroring.

——————————————————————————————————————–

DECLARE @sql VARCHAR(MAX)
SET @sql = ”
SELECT @sql = @sql+

USE [‘ + name + ‘]
BACKUP LOG [‘ + name + ‘] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘_log.bak”
DBCC SHRINKFILE (”’ + name + ‘_log”, 1, TRUNCATEONLY)’
FROM sys.databases
WHERE name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘pubs’, ‘Northwind’, ‘ReportServer$SQL2005′, ‘ReportServer$SQL2005TempDB’)
AND user_access_desc = ‘MULTI_USER’
AND is_read_only = 0
AND state_desc = ‘ONLINE’

–PRINT @sql
EXEC(@sql)

———————————————————————————————————-

Advertisements

Database Mirroring in SQL Server 2005


Introduction

Database mirroring is a new SQL Server 2005 technology available for review for increasing database availability. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. You can code client applications to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database. Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage.

Database Mirroring Overview

In database mirroring, an originating SQL Server 2005 instance continuously sends a database’s transaction log records to a copy of the database on another standby SQL Server instance. The originating database and server have the role of principal, and the receiving database and server have the role of mirror. The principal and mirror servers must be separate instances of SQL Server 2005.

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.

When the mirror server receives a block of log records, it places the log records first into the mirror database’s log buffer and then hardens them to disk as quickly as possible. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal’s transaction log records, it duplicates the database changes on the principal database.

Some important items to note about database mirroring:

  • The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.
  • The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
  • The mirror database must have the same name as the principal database.
  • Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time.

 

Database Mirroring Operating Modes

Operating Mode Transaction safety Transfer mechanism Quorum required Witness server Failover Type
High Availability FULL Synchronous Y Y Automatic or Manual
High Protection FULL Synchronous Y N Manual only
High Performance OFF Asynchronous N N/A Forced only

 

High Availability Operating Mode

The High Availability operating mode supports maximum database availability with automatic failover to the mirror database if the principal database fails. It requires that you set safety to FULL and define a witness server as part of the database mirroring session.

The High Availability mode is best used where you have fast and very reliable communication paths between the servers and you require automatic failover for a single database. When safety is FULL, the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. Because a single database failure will cause an automatic failover, if you have multi-database applications you want to consider other operating modes. (See “Multi-Database Issues” in the Implementation section later in this paper.)

In the High Availability mode, database mirroring is self-monitoring. If the principal database suddenly becomes unavailable, or the principal’s server is down, then the witness and the mirror will form a quorum of two and the mirror SQL Server will perform an automatic failover. At that point, the mirror server instance will change its role to become the new principal and recover the database. The mirror server can become available quickly because the mirror has been replaying the principal’s transaction logs and its transaction log has been synchronized with the principal’s.

Also, SQL Server 2005 can make a database available to users earlier in the recovery process. SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. In SQL Server 2005, a newly recovered database can become available for use as soon as the redo phase is finished. Therefore if a database mirroring failover occurs, the recovered new principal database can become available for use as soon as it finishes the redo phase. Because the mirror database has been replaying transaction log records all along, all the mirror serves has to do is finish the redo process, which normally can be accomplished in seconds.

High Protection Operating Mode

The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database does not need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness to fill the tie-breaker role. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.

Since there is no witness server defined, automatic failover cannot occur and a principal server which suddenly loses its quorum with the mirror does not take its database out of service.

High Performance Operating Mode

In the High Performance operating mode, transactional safety is OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror that all transaction log records have been recorded on the mirror. The mirror does its best to keep up with the principal, but it is not guaranteed at any point in time that all the most recent transactions from the principal will have been hardened in the mirror’s transaction log.

Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. If the witness is set, a quorum is required. If the witness is not set, then a quorum is not required. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation:

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

Database Snapshots and the Mirror Database

Because the mirror database is in a recovering state, it is not accessible and not readable. With SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time. Database snapshots provide a read-only view of a database, exposing data that is consistent at the point of the snapshot creation.

You access the database snapshot just as though it were another database. When you query a database snapshot, you read original versions of any database data that has been changed after the snapshot’s creation from the database snapshot’s file, and you read unchanged data from the original database. The end effect is that you see database data current at the point in time that you created the snapshot. (See the topic “Using Database Snapshots with Database Mirroring” in SQL Server Books Online for more information.)

Because database snapshots do require some overhead on the mirror server, you need to be careful about how they might impact database mirroring performance.

Database Mirroring and SQL Server 2005 Editions

Table

A few database mirroring features require SQL Server 2005 Enterprise or Developer Editions:

  • High Performance mode with safety OFF (asynchronous data transfer);
  • database snapshots
  • Use of multiple threads for replaying the transaction log on the mirror database (parallel REDO).

SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring.

If you are setting up database mirroring on a domain, and all SQL Server instances use the same service login and password, you do not need to create logins on each server. Similarly, on a workgroup, if all SQL Server instances use the same service login and password, you do not need to create logins on the servers. Just leave the logins blank on the Configure Database Mirroring Security Wizard when setting up endpoints.

Each database endpoint must specify a unique port on the server. When working with SQL Server instances on separate machines, these port numbers can all be the same and the Configure Database Mirroring Security Wizard will automatically suggest port 5022 as the port. If any of the SQL Server instances are on the same machine, each instance must have a distinct port and the port numbers must be unique.

Suppose you want to have three servers in a High Availability mirroring session. Server A will be the principal, server B the mirror, and server W the witness. For server A, the following command will create an endpoint on port 5022:

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);

Note that the role has been specified as PARTNER, so that this server may take on the role of principal or mirror for any given database mirroring database. The same command is issued on server B. Since server B is a SQL Server instance on a distinct physical machine, the port number is the same. Then for server W, you can issue

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = WITNESS, ENCRYPTION = ENABLED);

Note that for server W, the role is specified as WITNESS.

By default, the endpoint is not started. You can next start each endpoint using the following query on each server:

ALTER ENDPOINT [Mirroring] STATE = STARTED;

Optionally, you can insert the STATE option in the CREATE ENDPOINT command. This process is repeated on each server.

When you create an endpoint using CREATE ENDPOINT, you can restrict access by IP address using the protocol specific arguments. You can restrict access to a particular set of IP addresses by combining the RESTRICT_IP with ALL option, and the EXCEPT_IP with the list of just those special IP addresses you want. (See “CREATE ENDPOINT” in SQL Server Books Online.)

You can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:

SELECT *
FROM sys.database_mirroring_endpoints;
	
  • To start database mirroring, you next specify the partners and witness. You need database owner permissions to start and administer a given database mirroring session. On server A, the intended principal server, you tell SQL Server to give a particular database the principal role and what its partner (mirror) server is:
  • -- Specify the partner from the principal server
    ALTER DATABASE [AdventureWorks] SET PARTNER =
    N'TCP://B.corp.mycompany.com:5022';

    The partner name must be the fully qualified computer name of the partner. Finding fully qualified names can be a challenge, but the Configure Database Mirroring Security Wizard will find them automatically when establishing endpoints.

    The fully qualified computer name of each server can also be found running the following from the command prompt:

    IPCONFIG /ALL
  • Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:Host Name . . . . . . . . . . . . : A
    Primary Dns Suffix . . . . . . . : corp.mycompany.com
  • Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':<port number>' and you then have the partner name.On the mirror server, you would just repeat the same command, but with the principal server named:-- Specify the partner from the mirror server
  •  
  •  
  •  
  •  
  •  
  • ALTER DATABASE [AdventureWorks] SET PARTNER = N'TCP://A.corp.mycompany.com:5022';
  •  
  •  
  •  
  •  
  • On the principal server, you next specify the witness server:
  • -- Specify the witness from the principal server
    ALTER DATABASE [AdventureWorks] SET WITNESS =
    N'TCP://W.corp.mycompany.com:5026';

    You do not need to execute any additional commands on the witness server after the initial CREATE ENDPOINT.

    Finally, you specify the safety level of the session, on the principal server:

  • -- Set the safety level from the principal server
    ALTER DATABASE [AdventureWorks] SET SAFETY FULL;

     

  • At this point, mirroring will start automatically, and the principal and mirror servers will synchronize.You can adjust the timeout value for determining partner outage, using the TIMEOUT parameter to ALTER DATABASE. For example, to change the TIMEOUT value to 20 seconds (the default is 10), on the principal server issue:
  • -- Issue from the principal server
    ALTER DATABASE [AdventureWorks] SET PARTNER TIMEOUT 20;

    Finally, you can adjust the size of the redo queue on the mirror by issuing the ALTER DATABASE with the REDO_QUEUE option on the principal server. The following query will set the redo queue to 100 megabytes on the mirror:

  • -- Issue from the principal server
    ALTER DATABASE [AdventureWorks] SET PARTNER REDO_QUEUE 100MB;

    Once you have specified the partners, mirroring will start immediately.

  • ____________________________________________________________________________________________

    SQL Server Database Mirroring and simple steps:

     

    We have to specify the endpoint’s role in the Database mirroring option.Role can be Partner,Witness or All.Using the ALL keyword as the role specifies that the mirroring endpoint can be used for witness as well as for a partner in the database mirroring scenario.

    We can inspect the database mirroring endpoints on a server by querying the sys.database_mirroring_endpoints catalog view:
    SELECT *
    FROM sys.database_mirroring_endpoints;

    2.Creating the Mirror Database

    To create a mirror database,we have to restore the full backup of a principal including all other types of backup(transactional logs) created on the principal before establishing a session.The NORECOVERY option has to be used when restoring from backup so that the mirrored database will remain in nonusable state.The mirror database needs to have the same name as the principal database.

    3.Establishing a mirror session
    The next step in setting up database mirroring is to set up the mirror session on the database by identifying the mirroring partners.We have to identify the partners involved in the mirroring process on the principal database and on the mirror database.

    Let us consider an example.

    We will take AdventureWorks as the sample database.This database has simple recovery model by default. To use database mirroring with this database, we must alter it to use the full recovery model.

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

    We have two server instances which act as partners(Principal and Mirror) and one server instance which acts as witness.These three instances are located on different computers. The three server instances run the same Windows domain, but the user account is different for the example’s witness server instance.

    1.Create an endpoint on the principal server instance

    CREATE ENDPOINT Endpoint_Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=7022)
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO

    –Partners under same domain user; login already exists in master.
    –Create a login for the witness server instance,
    –which is running as XYZ\witnessuser:
    USE master ;
    GO
    CREATE LOGIN [XYZ\witnessuser] FROM WINDOWS ;
    GO

    — Grant connect permissions on endpoint to login account of witness.
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];
    GO

    2.Create an endpoint on the mirror server instance

     CREATE ENDPOINT Endpoint_Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=7022)
    FOR DATABASE_MIRRORING (ROLE=ALL)
    GO

    –Partners under same domain user; login already exists in master.
    –Create a login for the witness server instance,
    –which is running as XYZ\witnessuser:
    USE master ;
    GO
    CREATE LOGIN [XYZ\witnessuser] FROM WINDOWS ;
    GO

    –Grant connect permissions on endpoint to login account of witness.
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [XYZ\witnessuser];
    GO

    3.Create an endpoint on the witness server instance

     CREATE ENDPOINT Endpoint_Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=7022)
    FOR DATABASE_MIRRORING (ROLE=WITNESS)
    GO

    –Create a login for the partner server instances,
    –which are both running as Mydomain\dbousername:
    USE master ;
    GO
    CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;
    GO

    –Grant connect permissions on endpoint to login account of partners.
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];
    GO

    4.Create the mirror database. Refer step 2 in the “Preparing for Mirroring” block.

    5.Configure the principal as the partner on the mirror.

    ALTER DATABASE <Database_Name>
    SET PARTNER =
    <server_network_address>
    GO

    The syntax for a server network address is of the form:
    TCP :// < system-address> : < port>

    where,
    < system-address> is a string that unambiguously identifies the destination computer system. Typically, the server address is a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address.

    < port> is the port number used by the mirroring endpoint of the partner server instance.

    A database mirroring endpoint can use any available port on the computer system. Each port number on a computer system must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer.

    Example:

    ALTER DATABASE AdventureWorks
    SET PARTNER =
    ‘TCP://PARTNERHOST1.COM:7022’
    GO

    6. Configure the mirror as the partner on the principal.

    ALTER DATABASE AdventureWorks
    SET PARTNER = ‘TCP://PARTNERHOST5.COM:7022’
    GO

    7.On the principal server, set the witness

     ALTER DATABASE AdventureWorks
    SET WITNESS =
    ‘TCP://WITNESSHOST4.COM:7022’
    GO

     

     

      

    Switching Roles

    When the principal server fails,we have to switch roles over to the mirror and from then on specify that the mirror should become the principal database.This concept is called role switching.The three options for role switching are:

    1.Automatic failover :- When the witness server is present in the database mirroring session,automatic failover will occur when the principal database becomes unavailable and
    when the witness server confirms this.During the automatic failover,the mirror will be automatically promoted to principal,and whenever the principal comes back on,it will automatically take the role of mirror.

    2.Manual Failover :- The user can perform manual failover only if both the principal and mirror are alive and in synchronized status.DBAs use this operation most frequently to perform maintenance tasks on the principal.The failover is initiated from the principal and later the roles are reverted after the database maintenance job is done.

    The statement used to switch database roles(manual failover) is shown below:

    ALTER DATABASE AdventureWorks SET PARTNER FAILOVER

    3.Forced Service :- When the witness server is not used and if the principal database goes down unexpectedly,then the user has to initiate manual failover to the mirror.In asynchronous mode of operation,user does not have any idea whether the transaction that have got commited on the principal have made it to the mirror or not.In this scenario,when the user wants to switch roles,there is possibility of losing data.

    To achieve this,we need to invoke an ALTER DATABASE statement as shown below:

    ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    _____________________________________________________________________________________________

    Step-by-Step setup

    I hope to provide the cleanest step-by-step for setting up mirroring with a witness. Please feel free to edit anything I missed.
    We are going to do this entire thing using the GUI. No TSQL necessary.

    1. Make sure all your Servers are running under the same account under Services
    2. Go to your primary server’s Microsoft SQL Management Studio.
    3. Right click the database you wish to mirror and click backup (This is a two part step. Pay close attention).
    4. Do a FULL backup of your database to any place. Wait for this to finish.
    5. Do a Transaction log backup. (I tried it without this and was part of my failure)>
    6. Go to your mirror server’s Microsoft SQL Management Studio.
    7. The database should not exist here yet. Right click Databases and click Restore Database…
    8. Type the database name in the “To Database” text box.
    9. Click From Device and get add your main bak file.
    10. Check the Restore box and then click Options at the top left.
    11. On the Recovery state set RESTORE WITH NORECOVERY (The second radio button).
    12. Once that is done. Repeat the Recovery steps for the transaction file backup. (It also needs NORECOVERY on options).
    13. Go back to the Primary Server’s Management Studio and right click the database to be mirrored.
    14. Select Tasks and then Mirroring.
    15. Configure Security. Make sure Encrypt data is off on all of the server connections (Another issue I had during setup. My witness server had “Encrypt data sent through this endpoint” set to on and it broke. See the note below on how to remove Encrypt data if it is prepopulated and you can’t uncheck it.
    16. Leave the Security boxes blank.
    17. Click Start Mirroring and watch your mirror with the mirroring monitor (Screenshot below).

    Note

    • If you plan on using SQL Express for the Witness (A great choice), make sure you go into it’s SQL Server Configuration Manager and enable TCP/IP under Protocols.
    • To clear an endpoint’s Encryption flag, write down or remember the EndPoint name where the box is greyed out. Cancel out of the Security wizard. Connect to the witness server and execute the following command. DROP ENDPOINT <Endpoint Name>
    • If you are getting an error you cannot figure out (eg 1418) then remember to check the Server’s Application Event Log as it may provide a clue.
    • Please edit this WIKI if you had any issues.
    • Getting Sql 2005 Mirroring EndPoints:
    SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
            t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
            e.connection_auth_desc
    FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
    ON     e.endpoint_id = t.endpoint_id
    • Connection String Change (Cdyne uses trusted authentication. That way there is no user/password in the code):
    Data Source=MyPrimary.domain.net;Failover Partner=MyFailover.domain.net;Integrated Security=SSPI;Initial Catalog=MyDB;Connect Timeout=20

    %d bloggers like this: