HOWTO setup outsystems database for database mirroring [SQL Server]

HOWTO setup outsystems database for database mirroring [SQL Server]

  
Below is a guide on how to setup outsystems database for database mirroring

You will need two database server running MS SQL Server.
1 x Primary database server (Princible)
1 x Secondary database server (Mirror)
 

Backup Primary server with full recovery mode:
PRIMARY SERVER:
1 -  Make sure your database is in full recovery mode
 
ALTER DATABASE Outsystems
SET RECOVERY FULL
GO
 
2 - Backup the database

BACKUP DATABASE Outsystems
TO DISK = 'C:\SQLBackup\Outsystems.bak'
GO
 
3 - Backup the database log
 
BACKUP LOG Outsystems
TO DISK = 'C:\SQLBackup\Outsystemslog.bak'
GO
 
Restore backup to Mirror server:
MIRROR SERVER
4 - Restore to the mirror database with no recovery option
(Copy the outsystems.bak and outsystemslog.bak to your mirror server, C:\SQLBackup)
 
RESTORE DATABASE Outsystems
FROM DISK = 'C:\SQLBackup\Outsystems.bak'
WITH NORECOVERY,
 MOVE 'Outsystems' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08MIRROR\MSSQL\Data\Outsystems_Data.mdf'
 MOVE 'Outsystems_Log' 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08MIRROR\MSSQL\Data\Outsystems_log.ldf'
GO
 
5 - Restore the mirror database logs with no recovery
 
RESTORE LOG Outsystems
FROM DISK = 'C\Outsystemslog.bak'
WITH NORECOVERY
GO
 
---------------
Setup the Endpoint
PRIMARY SERVER
6 - Setup the endpoint
 
CREATE ENDPOINT Endpoint_Principal
 STATE = STARTED
 AS TCP (LISTENER_PORT = 5022)
 FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
 
MIRROR SERVER
7 - Setup the endpoint
 
CREATE ENDPOINT Endpoint_Mirror
 STATE = STARTED
 AS TCP (LISTENER_PORT = 5023)
 FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
 
Setup Partner
MIRROR SERVER

8 (on mirror server)
 
ALTER DATABASE Outsystems
 SET PARTNER = 'TCP://MYPRIMARTSERVERNAME:5022'
GO
 
PRIMARY SERVER
9 (on primary server)
 
ALTER DATABASE Outsystems
 SET PARTNER = 'TCP://MYMIRRORSERVERNAME:5023'
GO
 
Your primary server status should now read “Principal, Synchronized”
 
Check if everything is setup properly
10 Check if everything is setup properly
 
Right click the outsystems database
TASKS --> Launch Database Mirroring Monitor
Refresh
 
See it in action
11 Force a failover and see it in action!

 

Database Mirroring Setup

By Brady Upton, 2011/04/12


Here's a basic look at how to setup database mirroring. My environmenthas two separate VM's running in VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain admins (DOMAIN/ADMINISTRATOR). Windows Firewall is OFF.

I created a database on Principal and named it TestMirror and set the recovery model to FULL RECOVERY.

 

Object explorer

1st step: Take full backup of Database:


BACKUP DATABASE TestMirror
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak'

 



2nd step: Take log backup
	BACKUP LOG TestMirror
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn'

That will give you the following backup files:

 

Backup Files

 

3rd step: Assuming you have the backup folder shared where you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option:

RESTORE DATABASE TestMirror
 FROM DISK = N'\\Principal\Backup\Backup.bak' WITH FILE = 1, MOVE N'TestMirror_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', NORECOVERY, NOUNLOAD, STATS = 10

4th step: Restore log backup also with the NORECOVERY option:
RESTORE LOG TestMirror
FROM DISK = N'\\Principal\Backup\Backup.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10



database restoring

Now it’s time to dig down and configure the mirroring. From the Principal server, right click the database and choose Tasks > Mirror or choose Properties, Mirroring.

 

database properties

 

Click Configure Security and click next if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:

 

witness server

This is where you would configure a witness server for your mirroring but since we’re just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover.

 

Select No, then Click Next…

 

The next screen will give you options to configure the Principal Server Instance:

 

Principal Config

 

Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.

Click Next…

The next screen will give you options to configure the Mirror Server Instance:

 

Mirror config

 

To connect to the Mirror server instance we will need to click the Connect… button and select the mirror server and provide the correct credentials:

 

Connect to SQL Server

 

Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.

Click Next and you’ll see the Service Accounts screen.

 

Mirroring Service Accounts

 

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains). If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.

Since my service accounts are using the same domain account, I’ll leave this blank.

Click Finish and you’ll see a Complete the Wizard screen that summarizes what we just configured. Click Finish one more time.

 

Config screen
 

If you see the big green check mark that means database mirroring has been configured correctly. However, just because it is configured correctly doesn’t mean that database mirroring is going to start...

Next screen that pops up should be the Start/Do Not Start Mirroring screen:

 

Start mirroring dialog

 

We’re going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:

 

Choose mirroring operating mode

 

Since we didn’t specify a witness server we will not get the High Safety with automatic failover option but we still get the High Performance and High Safety without automatic failover options.

For this example, we’ll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.

Next, click Start Mirroring

 

start mirroring

 

If everything turned out right, database mirroring has been started successfully and we are fully synchronized.

 

mirroring status

 

Here is the principal

 

principal server

 

and here is the secondary

 

Secondary server

 

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot (from MSDN)

Both servers should be listening on the same port. To verify this, run the following:

SELECT type_desc, port FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:

 

mirror status?

 

To start an Endpoint, run the following:

ALTER ENDPOINT <Endpoint Name>
 STATE = STARTED
 AS TCP (LISTENER_PORT = <port number>)
 FOR database_mirroring (ROLE= ALL);

ROLES should be the same on both the Principal and Mirror Server, to verify this run:
SELECTrole FROM sys.database_mirroring_endpoints;

 

mirroring roles

 

To verify the login from the other server has CONNECT permissions run the following:

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38),suser_name(SP.grantor_principal_id)) 
AS GRANTOR,
 SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
AS GRANTEE 
FROMsys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDERBY Permission,grantor, grantee;

Permissions for logins

 

You can see here from the State and Permissions column that the user has been Granted Connect permissions.



SOURCE: http://www.sqlservercentral.com/articles/Database+Mirroring/72774/