Docstoc

_User Guide Template_ - Intranet DASHBOARD

Document Sample
_User Guide Template_ - Intranet DASHBOARD Powered By Docstoc
					                        Intranet DASHBOARD
                       SQL Database Mirroring




INTRANET. EXTRANET. PORTAL.
   www.intranetdashboard.com
Table of Contents


1     INTRODUCTION AND PURPOSE ......................................................................................................................4

2     REQUIREMENTS ................................................................................................................................................5

    2.1     General Requirements ..................................................................................................................................5
    2.2     iD Version .....................................................................................................................................................5
    2.3     Difficulty ........................................................................................................................................................5
3     SERVER PREPARATION ...................................................................................................................................6

    3.1     Configuring Service Accounts .......................................................................................................................6
    3.2     Configuring SQL User Accounts ...................................................................................................................8
4     CONFIGURING ENDPOINTS..............................................................................................................................9

    4.1     Creating Endpoints .......................................................................................................................................9
    4.2     Primary Server Endpoint ...............................................................................................................................9
    4.3     Mirror Server Endpoint ..................................................................................................................................9
    4.4     Witness Server Endpoint (Optional) .............................................................................................................9
5     BACKING UP AND RESTORING THE ID DATABASES .................................................................................10

    5.1     Important Information ..................................................................................................................................10
    5.2     Taking Backups ..........................................................................................................................................10
    5.3     Restoring Backups ......................................................................................................................................10
6     MIRRORING ......................................................................................................................................................12

    6.1     Start Mirroring .............................................................................................................................................12
    6.2     Mirror Server ...............................................................................................................................................12
    6.3     Primary Server ............................................................................................................................................12
    6.4     Witness Server (Optional) ...........................................................................................................................12
7     CONFIGURING ID SQL USERS .......................................................................................................................13

    7.1     SQL Users on the Mirror SQL Server .........................................................................................................13
    7.2     Getting Primary Server SQL Users SID......................................................................................................13
    7.3     Creating SQL Users on the Mirror SQL Server ..........................................................................................13
8     ALLOW MANAGED CODE FOR SEARCH ......................................................................................................14

    8.1     Failover to Mirror SQL Server .....................................................................................................................14
    8.2     Allow Managed Code ..................................................................................................................................14
    8.3     Failover to Primary SQL Server ..................................................................................................................14
9     INTRANET DASHBOARD CONFIGURATION .................................................................................................15

    9.1     Configuration Changes in The Admin .........................................................................................................15


                                                 INTRANET. EXTRANET. PORTAL.
                                                           www.intranetdashboard.com
                                                                              Page 2 of 19
     9.2     Web.Config Changes ..................................................................................................................................15
10     FAILOVER CHECKING, TESTING AND OUTAGES .......................................................................................16

     10.1    Checking your Mirroring Configuration .......................................................................................................16
     10.2    Testing your Mirroring Configuration ..........................................................................................................16
     10.3    In the Event of an Outage ...........................................................................................................................18
11     CONCLUSION ...................................................................................................................................................19




                                                INTRANET. EXTRANET. PORTAL.
                                                          www.intranetdashboard.com
                                                                           Page 3 of 19
1    Introduction and Purpose
The purpose of this document is to demonstrate how to setup database mirroring between SQL Servers to allow for
the ability to failover to a secondary database server in the case of a database servers failure. This guide will take
you through a step by step process required to successfully setup database mirroring for your Intranet
DASHBOARD Installation.




                                   INTRANET. EXTRANET. PORTAL.
                                          www.intranetdashboard.com
                                                      Page 4 of 19
2     Requirements
2.1    General Requirements

1x SQL Server 2008 Standard Edition or Greater for Primary
1x SQL Server 2008 Standard Edition or Greater for Mirror
1x SQL Server 2008 Express or Greater for Witness (Optional)

All versions of SQL Server must be the same.

All database servers must be 64bit and meet or exceed our Database Server Requirements:
http://help.intranetdashboard.com/extras/tech_specs/TechnicalSpecifications.html
http://help.intranetdashboard.com/extras/tech_specs/iDSQLServerRequirements.html

Active Directory (with each SQL Server joined to the domain)
iD Using a Database Filestore

Please Note: Each SQL Server instance should be installed on a separate Physical Server. Running more than
one instance of SQL Server on the same physical server which is involved in the Mirroring Configuration can be
fatal in the event of an outage.

2.2    iD Version

iD 3.x or Later

2.3    Difficulty

This guide is intended for users with a moderate technical knowledge, most importantly in SQL Server.




                                   INTRANET. EXTRANET. PORTAL.
                                        www.intranetdashboard.com
                                                   Page 5 of 19
3     Server Preparation
3.1    Configuring Service Accounts

The first step which needs undertaking is to configure the SQL Server Services to run underneath of an
Administrative Active Directory user account. All SQL Servers involved in the mirroring process will need to be
configured to run under the same account so they can successfully connect to each other.

To change the service account SQL runs under, navigate to Control Panel > Administrative Tools > Services and
navigate through the list of services until you find SQL Server




                                   INTRANET. EXTRANET. PORTAL.
                                         www.intranetdashboard.com
                                                      Page 6 of 19
Next right click on the SQL Service Service and select Properties from the list of items to bring up the properties for
the SQL Service Service and select the Log On tab. By default the radio button Log on as will be set to the Local
System Account. We need to change this to use This Account and here we can specify an Active Directory
Domain Administrative account.




Once the details have been entered, apply the changes and restart the SQL Server Service. Repeat these steps
for each server.




                                   INTRANET. EXTRANET. PORTAL.
                                          www.intranetdashboard.com
                                                      Page 7 of 19
3.2      Configuring SQL User Accounts

To ensure the SQL Servers can authenticate successfully into each other, the same Administrative Active Directory
Account should also be granted login to each SQL Server. To allow the user to authenticate, navigate to and open
SQL Management Studio, connect to your server instance and then Navigate in the Object Explorer to
Security > Logins and open up the New Login diagram by right clicking on the Logins folder and selecting New
Login.

Enter in the Administrative Users details:




Then select the Sever Roles tab and make the user a sysadmin by checking the checkbox:




Once completed, click OK to create the user and save the changes. Repeat these steps for each server.




                                   INTRANET. EXTRANET. PORTAL.
                                         www.intranetdashboard.com
                                                   Page 8 of 19
4     Configuring Endpoints
4.1    Creating Endpoints

Before the databases can be mirrored, the next step is to create Endpoints on each of the SQL Servers. Endpoints
allow the mirrors to communicate with each other in the Mirroring Configuration.

4.2    Primary Server Endpoint

The first server to create an endpoint on is the Primary SQL Server. This can be done by running the following
command in SQL Management Studio:

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

4.3    Mirror Server Endpoint

The second endpoint to create is the one on the Mirror SQL Server. The command for this is similar to the mirror
server command; however the port number is slightly different:

        CREATE ENDPOINT Database_Mirroring
                 STATE=STARTED
                 AS TCP (LISTENER_PORT=7023)
                 FOR DATABASE_MIRRORING (ROLE=PARTNER)
        GO

4.4    Witness Server Endpoint (Optional)

If a Witness SQL Server is being implemented in your configuration, this command will also need to be run. A
Witness SQL Server can be implemented at a later stage and does not need to be included in the initial
configuration:

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

It is important that Endpoints are setup on separate servers. Running endpoints on the same server can cause
detrimental effects to the configuration should a server fail.

Once the endpoints have been created, reboot each server for the endpoint setup to complete.




                                    INTRANET. EXTRANET. PORTAL.
                                           www.intranetdashboard.com
                                                       Page 9 of 19
5     Backing up and Restoring the iD Databases
5.1    Important Information

It is important that the following set of instructions is followed closely as they are critical to the success of mirroring
your databases.

5.2    Taking Backups

On the Primary SQL Server (where the iD Databases should be installed), the databases need to be prepared for
backup. Because Mirroring requires not only the database backup files, it also requires a copy of the transaction
logs. To allow this, first set the databases to be in Full recovery mode. This can be achieved by running the
following commands (replacing IntranetDASHBOARD with the name of your databases):

ALTER DATABASE [IntranetDASHBOARD] SET RECOVERY FULL
ALTER DATABASE [IntranetDASHBOARD-Filestore] SET RECOVERY FULL
ALTER DATABASE [IntranetDASHBOARD-Search] SET RECOVERY FULL

Next step is to take the backups of both the databases and also the transaction logs by running the following
command, also remembering to substitute IntranetDASHBOARD for the name of your database and C:\Backup\ for
the location you would like to backup to (this directory needs to exist prior to starting the backup):

BACKUP DATABASE [IntranetDASHBOARD]
TO DISK='C:\Backup\IntranetDASHBOARD.bak' WITH INIT
BACKUP LOG [IntranetDASHBOARD]
TO DISK='C:\Backup\IntranetDASHBOARD.trn' WITH INIT

BACKUP DATABASE [IntranetDASHBOARD-Filestore]
TO DISK='C:\Backup\IntranetDASHBOARD-Filestore.bak' WITH INIT
BACKUP LOG [IntranetDASHBOARD-Filestore]
TO DISK='C:\Backup\IntranetDASHBOARD-Filestore.trn' WITH INIT

BACKUP DATABASE [IntranetDASHBOARD-Search]
TO DISK='C:\Backup\IntranetDASHBOARD-Search.bak' WITH INIT
BACKUP LOG [IntranetDASHBOARD-Search]
TO DISK='C:\Backup\IntranetDASHBOARD-Search.trn' WITH INIT

Now the backups are complete, copy them over to the Mirror SQL Server in preparation for the next step.

5.3    Restoring Backups

The database backups which were just created need to be restored to the Mirror SQL Server. Restoring the
databases has to be done in a particular way to ensure they are in the correct state to starting the mirroring
process.

First, a new database is to be created to restore each of the backups to. Run the following commands to create
the new databases (again substituting IntranetDASHBOARD for the iD Database Name):

CREATE DATABASE [IntranetDASHBOARD]
CREATE DATABASE [IntranetDASHBOARD-Filestore]
CREATE DATABASE [IntranetDASHBOARD-Search]


                                    INTRANET. EXTRANET. PORTAL.
                                           www.intranetdashboard.com
                                                        Page 10 of 19
To restore the databases, run these commands, substituting IntranetDASHBOARD for your iD Database Name and
ensure to specify the database backup and restore locations:

RESTORE DATABASE [IntranetDASHBOARD]
FROM DISK='C:\Backup\IntranetDASHBOARD.bak' WITH NORECOVERY, REPLACE,
MOVE 'IntranetDASHBOARD' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD.mdf',
MOVE 'IntranetDASHBOARD_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD_1.ldf'

RESTORE DATABASE [IntranetDASHBOARD-Filestore]
FROM DISK='C:\Backup\IntranetDASHBOARD-Filestore.bak' WITH NORECOVERY, REPLACE,
MOVE 'IntranetDASHBOARD-Filestore' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Filestore.mdf',
MOVE 'IntranetDASHBOARD-Filestore_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Filestore_1.ldf'

RESTORE DATABASE [IntranetDASHBOARD-Search]
FROM DISK='C:\Backup\IntranetDASHBOARD-Search.bak' WITH NORECOVERY, REPLACE,
MOVE 'IntranetDASHBOARD-Search' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Search.mdf',
MOVE 'IntranetDASHBOARD-Search_Queuing' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Search_Queuing.ndf',
MOVE 'IntranetDASHBOARD-Search_Staging' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Search_Staging.ndf',
MOVE 'IntranetDASHBOARD-Search_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\IntranetDASHBOARD-Search_log.ldf'


The transaction logs which were also backed up need to be restored along with the databases. Run the following
commands, substituting IntranetDASHBOARD for your iD Database Name and ensure to specify the database
backup locations:

RESTORE LOG [IntranetDASHBOARD]
FROM DISK='C:\Backup\IntranetDASHBOARD.trn' WITH NORECOVERY, REPLACE

RESTORE LOG [IntranetDASHBOARD-Filestore]
FROM DISK='C:\Backup\IntranetDASHBOARD-Filestore.trn' WITH NORECOVERY, REPLACE

RESTORE LOG [IntranetDASHBOARD-Search]
FROM DISK='C:\Backup\IntranetDASHBOARD-Search.trn' WITH NORECOVERY, REPLACE

The databases if restored correctly should look like this in Object Explorer in SQL Management Studio on the
Mirror SQL Server.




                                 INTRANET. EXTRANET. PORTAL.
                                        www.intranetdashboard.com
                                                   Page 11 of 19
6     Mirroring
6.1    Start Mirroring

Now that all databases and transaction logs have been restored, the databases and SQL Servers should be ready
to begin mirroring. Please take note of the order the below commands need to be run in as this is critical to the
success of mirroring the databases.

6.2    Mirror Server

To begin mirroring, run the below command on the Mirror SQL Server, substituting IntranetDASHBOARD for your
iD Database Name and PrimarySQL.domain.local with the Fully Qualified Domain Name or IP Address of your
server:

ALTER DATABASE [IntranetDASHBOARD] SET PARTNER = 'TCP://PrimarySQL.domain.local:7022'

6.3    Primary Server

Next, run the below command on the Primary SQL Server, substituting IntranetDASHBOARD for your iD Database
Name and MirrorSQL.domain.local with the Fully Qualified Domain Name or IP Address of your server:

ALTER DATABASE [IntranetDASHBOARD] SET PARTNER = 'TCP://MirrorSQL.domain.local:7023'

6.4    Witness Server (Optional)

If you have a Witness SQL Server in your configuration, you will also need to run this command on the Primary
SQL Server, substituting IntranetDASHBOARD for your iD Database Name and WitnessSQL.domain.local with the
Fully Qualified Domain Name or IP Address of your server:
ALTER DATABASE [IntranetDASHBOARD] SET WITNESS = 'TCP://WitnessSQL.domain.local:7022'



The database once mirrored should look like this in Object Explorer in SQL Management Studio on the Primary
SQL Server:




And on the Mirror SQL Server:




                                  INTRANET. EXTRANET. PORTAL.
                                         www.intranetdashboard.com
                                                     Page 12 of 19
7     Configuring iD SQL Users
7.1    SQL Users on the Mirror SQL Server

For iD to be able to access the mirrored databases on the Mirror SQL Server, the SQL User accounts must be
configured here along with your Primary SQL Server (the iD installer will have setup the User Accounts on your
Primary SQL Server). Before continuing on in this section, it is important that you know the SQL User Password
configured on the Primary SQL Server.

7.2    Getting Primary Server SQL Users SID

The first step is to get the sid from both the iDUser and iDUser-Search accounts (these account usernames may
vary if they were changed during installation, you can check this in your site’s web.config).

To get the sid, on the Primary SQL Server, run the following command (replacing iDUser with the SQL User name
created during setup):
SELECT name, sid FROM sys.sql_logins WHERE name LIKE '%iDUser%'

A set of results similar to below should be returned:




Take note of both the name and the corresponding sid for both user accounts returned.

7.3    Creating SQL Users on the Mirror SQL Server

Using the sid obtained in the previous step and SQL User Name and Password for each user account, next the
create login commands need to be run on the Mirror SQL Server, replacing the Users, Passwords and sids with
your own:

CREATE LOGIN [iDUser] WITH PASSWORD = 'UserPassword',
CHECK_POLICY = OFF,
SID = 0xD6200AAFCD8F1543A39A8BE65E9C2CCC

CREATE LOGIN [iDUser-Search] WITH PASSWORD = 'SearchUserPassword',
CHECK_POLICY = OFF,
SID = 0x9808CBCA764E524CA1D456554E039E3D




                                   INTRANET. EXTRANET. PORTAL.
                                          www.intranetdashboard.com
                                                        Page 13 of 19
8     Allow Managed Code for Search
8.1    Failover to Mirror SQL Server

As SQL Server does not allow queries to be executed against databases in the restoring state, the first step is to
failover to the Mirror SQL Server. To do this the command below needs to be issued to the Primary SQL Server,
remembering to substitute IntranetDASHBOARD for your iD Database Name:
ALTER DATABASE [IntranetDASHBOARD-Search] SET PARTNER FAILOVER

8.2    Allow Managed Code

Now that the Mirror SQL Server is active for the iD Search Database, the commands to allow managed code can
be run on the Mirror SQL Server, remembering to substitute IntranetDASHBOARD for your iD Database Name:

USE [master]
ALTER DATABASE [IntranetDASHBOARD-Search]
SET TRUSTWORTHY ON

USE [IntranetDASHBOARD-Search]
EXEC sp_changedbowner 'sa'


8.3    Failover to Primary SQL Server

Once again run the command as outlined in step 8.1, but this time on the Mirror SQL Server to restore the servers
back to their correct mirroring roles.




                                    INTRANET. EXTRANET. PORTAL.
                                         www.intranetdashboard.com
                                                    Page 14 of 19
9     Intranet DASHBOARD Configuration
9.1       Configuration Changes in The Admin

Each of the database connection strings in Intranet DASHBOARD need to be updated to tell iD there is a Failover
SQL Server should the Primary SQL Server fail. The first change to make is by altering the database connection
string in the iD Admin.

Once in the iD Admin, navigate to Utilities > Config Settings and scroll down until you locate the setting “Filesystem
Database Connection”. Append to the connection string Failover Partner=’MirrorSQL’; (replacing MirrorSQL with
the SQL Server Name and Instance). Once added, the connection string should look similar to the following:

Database='IntranetDASHBOARD-Filestore';Server='PrimarySQL';User
Id='iDUser';Password='fyCnL1iaVWd8j5QpQj0mCQ==';Failover Partner='MirrorSQL';

9.2       Web.Config Changes

Next the Failover Partner needs to be specified in the iD web.config files. There are two web.config files which
require changing. On your webserver(s) these are:

          iDInstallationDirectory\wwwroot\web.config

          iDInstallationDirectory\search\wwwroot\web.config

In each web.config file, append the Failover Partner to each of the connection strings (there should be three in total
between the two files). After the Failover Partner has been added to each connection string, they should look
similar to the following:

Database='IntranetDASHBOARD';Server='PrimarySQL';User
Id='iDUser410';Password='fyCnL1iaVWd8j5QpQj0mCQ==';Failover Partner='MirrorSQL';




                                    INTRANET. EXTRANET. PORTAL.
                                          www.intranetdashboard.com
                                                        Page 15 of 19
10 Failover Checking, Testing and Outages
10.1 Checking your Mirroring Configuration

The Database Mirroring Monitor* can provide you with a way to check the status of all the SQL Servers involved in
the mirroring configuration similar to below:




      *More information on the Database Mirroring Monitor can be found on Microsoft’s website: http://msdn.microsoft.com/en-us/library/ms365786.aspx

10.2 Testing your Mirroring Configuration

There are various ways to test your Mirroring Configuration to check that it fails over correctly.

Manual Failover

A Manual Failover can be initiated by executing the following commands on your Primary SQL server (replacing
IntranetDASHBOARD with your iD Database Name):

ALTER DATABASE [IntranetDASHBOARD] SET PARTNER FAILOVER
ALTER DATABASE [IntranetDASHBOARD-Filestore] SET PARTNER FAILOVER
ALTER DATABASE [IntranetDASHBOARD-Search] SET PARTNER FAILOVER

This will reverse the roles of the SQL Servers making the Mirror SQL Server Active and start accepting requests.
After issuing these commands, access your Intranet to check that it is still working and is not throwing any errors
for general usage.

Once you have finished testing, issue the above commands again but this time on the Mirror SQL Server to
restore the roles back to default.

                                            INTRANET. EXTRANET. PORTAL.
                                                     www.intranetdashboard.com
                                                                     Page 16 of 19
Automatic Failover (where a Witness has been implemented)

If a Witness has been implemented in your Mirroring configuration, Automatic Failover can be tested by doing one
of the following:

       Turning off the SQL Instance on the Primary SQL Server

       Shutting Down the Primary SQL Server

       Disconnecting the Primary SQL Server from your network

Within 10 seconds the Witness should detect the Primary SQL Server has gone down and enable the Mirror SQL
Server to take over all database requests. If failover has succeeded, iD should continue to work as if the Primary
SQL Server never went offline.

After testing has been completed and the Primary SQL Server has been brought back online, the earlier
commands used for Manual Failover should be executed on the Mirror SQL Server to resume the correct roles.




                                  INTRANET. EXTRANET. PORTAL.
                                        www.intranetdashboard.com
                                                    Page 17 of 19
10.3 In the Event of an Outage

Configurations without a Witness SQL Server

In configurations without a Witness SQL Server, the commands to failover the databases will need to be executed
manually on the Mirror SQL Server. These commands are different from the earlier commands (which cannot be
executed on the Mirror should the Primary SQL Server go offline). Execute these commands, replacing
IntranetDASHBOARD with the name of your iD Database:

ALTER DATABASE [IntranetDASHBOARD]
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE [IntranetDASHBOARD-Filestore]
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE [IntranetDASHBOARD-Search]
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Once the above commands have been issued, check to see if the Intranet has come back online. If you receive an
error message, recycling the iD Application Pools on the webserver and try to load the site again.

Configurations with a Witness SQL Server

Where a Witness has been implemented Failover will occur automatically and no commands need to be issued.
Recycling of the application pool is generally not required, however should error messages occur after failover an
application pool recycle is the first thing which should be performed to see if it fixes the errors.

Resuming normal operation after an outage has been resolved

After an outage has been resolve and the Primary SQL Server has been brought back online, to resume Normal
Operation, issue the follow commands to the Mirror SQL Server, replacing IntranetDASHBOARD with the name of
your iD Database:

ALTER DATABASE [IntranetDASHBOARD] SET PARTNER FAILOVER
ALTER DATABASE [IntranetDASHBOARD-Filestore] SET PARTNER FAILOVER
ALTER DATABASE [IntranetDASHBOARD-Search] SET PARTNER FAILOVER




                                    INTRANET. EXTRANET. PORTAL.
                                           www.intranetdashboard.com
                                                       Page 18 of 19
11 Conclusion
This concludes the guide. For further information or assistance with your Mirroring Configuring, please contact
support@intranetdashboard.com

Microsoft has additional information available on their website related to SQL Mirroring:
http://msdn.microsoft.com/en-us/library/ms189852.aspx




                                   INTRANET. EXTRANET. PORTAL.
                                         www.intranetdashboard.com
                                                     Page 19 of 19

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:14
posted:11/13/2012
language:Unknown
pages:19