Share via

Using Database Mirroring with SQL Server Modeling Services

  • Article

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

SQL Server 2008 provides the option for database mirroring to improve availability. Database mirroring involves at least two SQL Server instances. One of the SQL Server instances services client connections and database operations. This instance is referred to as the principal server. All transactions on the principal server are sent to the other database instance and repeated on that instance, known as the mirror server. In high-safety mode, a third SQL Server instance is used to support automatic failover. This optional third instance is referred to as the witness. In the event of a failure on the principal server, database mirroring enables an automatic failover to the mirror server. In addition to increasing availability during unplanned server outages, database mirroring also supports upgrading the principal and mirror server separately. This type of rolling upgrade improves availability for planned outages due to server upgrades or update installations.

In general, you should follow the recommendations in the SQL Server 2008 documentation in regards to how to set up and manage database mirroring for the SQL Server Modeling Services database. For more information, see Database Mirroring. Although this documentation provides a thorough reference for managing database mirroring, there are a few setup choices for the database administrator. For the Modeling Services database, use the following guidelines:

  • Use high-safety mode with automatic failover.

  • Use different physical servers for the principal server, the mirror server, and the witness.

  • When setting up database mirroring, manually copy Modeling Services server objects to the mirror server. For more information, see the following section, Adding Modeling Services Server Objects to the Mirror Server.

Adding Modeling Services Server Objects to the Mirror Server

The Modeling Services database uses the following two server triggers that apply to the entire SQL Server instance:

  • Trigger_Repository_Repository_Repository.Item_Logon_SetSecurityClaims

  • Trigger_Repository_Repository_Drop

To set up database mirroring, you back up the Modeling Services database on the principal server and restore it on the mirror server. This step only moves the database and does not move the server triggers that the Modeling Services database depends on. The server triggers must be copied manually. The steps below describe how to create a T-SQL script that will create the necessary server triggers. This script can be run on the mirror server as an additional step in setting up database mirroring for the Modeling Services database.

Note

The example below assumes that the Modeling Services database is named Repository. If you are setting up mirroring for a Modeling Services database with a different name, the server objects will vary accordingly. For example, a Modeling Services database named Repository2 would have two server triggers named Trigger_Repository_Repository2_Repository.Item_Logon_SetLogonSessionsSecurityClaims and Trigger_Repository_Repository2_Drop.

To create a script of server objects on the principal server

  1. On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.

  2. In the Connect to Server dialog, type the name of the principal server in the Server name combo box. This server should already have the SQL Server Modeling Services database installed.

  3. Specify the Authentication properties, and then click the Connect button.

    Note

    Managing server triggers requires CONTROL SERVER permission on the server.

  4. Press the F8 key to access the Object Explorer window.

  5. In Object Explorer, expand the server node.

  6. Expand the Server Objects node, and then expand the Triggers node.

  7. Right-click the Trigger_Repository_Repository_Repository.Item_Logon_SetSecurityClaims trigger. Select Script Server Trigger as, Create To, and then click New Query Editor Window. This will open a new query editor window with a CREATE statement for the server trigger.

  8. On the File menu, click Save. Save this script to later run on the mirror server.

  9. Follow the same steps to save a script for the Trigger_Repository_Repository_Drop server trigger.

To create the server objects on the mirror server

  1. On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.

  2. In the Connect to Server dialog, type the name of the mirror server in the Server name combo box.

  3. Specify the Authentication properties, and then click the Connect button.

    Note

    Managing server triggers requires CONTROL SERVER permission on the server.

  4. On the file menu, click Open, and then click File.

  5. Open the previously saved script that creates the Trigger_Repository_Repository_Repository.Item_Logon_SetSecurityClaims trigger.

  6. Press the F5 key to run the script.

    Note

    If you have not yet copied the Modeling Services database to the mirror server, you will get the following message in the Results window: The module 'Trigger_Repository_Repository_Repository.Item_Logon_SetSecurityClaims' depends on the missing object 'Repository.Repository.Item.SetLogonSessionsSecurityClaims'. The module will still be created; however, it cannot run successfully until the object exists. This is not an error, and the trigger will work correctly after the Modeling Services database is copied to the mirror server.

  7. Open the previously saved script that creates the Trigger_Repository_Repository_Drop trigger.

  8. Press the F5 key to run the script.

See Also

Concepts

Availability and Scalability Considerations (Modeling Services)
SQL Server Modeling Services Administration