GAPTHEGURU

Geek with special skills

sql 2008 r2 database mirroring – how to

Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)

This  example shows all the stages required to create a database
mirroring  session with a witness using Windows Authentication. The
examples in  this topic use Transact-SQL. Note that as an alternative to
using  Transact-SQL steps, you can use the Configure Database Mirroring
Security Wizard for database mirroring setup.

Prerequisite

The example uses the  AdventureWorks2008R2 sample database, which
uses the simple recovery  model by default. To use database mirroring
with this database, you must  alter it to use the full recovery model.
To do this in Transact-SQL,  use the ALTER DATABASE statement, as
follows:

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
Example

In this example, the two partners  and the witness are the default
server instances on three computer  systems. The three server instances
run the same Windows domain, but the  user account is different for the
example’s witness server instance.

The following table summarizes the values used in this example.

Initial mirroring role Host system Domain user account
Principal PARTNERHOST1 <Mydomain>\<dbousername>
Mirror PARTNERHOST5 <Mydomain>\<dbousername>
Witness WITNESSHOST4 <Somedomain>\<witnessuser>
    1. Create an endpoint on the principal server instance (default instance on PARTNERHOST1).
      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 Somedomain\witnessuser:
      USE master ;
      GO
      CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;
      GO
      -- Grant connect permissions on endpoint to login account of witness.
      GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];
      GO
    1. Create an endpoint on the mirror server instance (default instance on PARTNERHOST5).
      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 Somedomain\witnessuser:
      USE master ;
      GO
      CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;
      GO
      --Grant connect permissions on endpoint to login account of witness.
      GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];
      GO
    1. Create an endpoint on the witness server instance (default instance on WITNESSHOST4).
      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
    1. Create the mirror database
    2. On  the mirror server instance on PARTNERHOST5, set the server
      instance on  PARTNERHOST1 as the partner (making it the initial
      principal server  instance).

      ALTER DATABASE AdventureWorks2008R2
          SET PARTNER =
          'TCP://PARTNERHOST1.COM:7022';
      GO
    3. On the principal server instance on PARTNERHOST1, set  the server
      instance on PARTNERHOST5 as the partner (making it the  initial mirror
      server instance).

      ALTER DATABASE AdventureWorks2008R2
          SET PARTNER = 'TCP://PARTNERHOST5.COM:7022';
      GO
    4. On the principal server, set the witness (which is on WITNESSHOST4).
      ALTER DATABASE AdventureWorks2008R2
          SET WITNESS =
          'TCP://WITNESSHOST4.COM:7022';
      GO
      
      
Advertisements

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: