Configure Backup on Secondary Replica

Configure Backup on Secondary Replica.

AlwaysOn Hish Availability Groups provides Backup on Secondary database to overcome load on Primary Database.

As Secondary database is Read-Only replica so you can’t execute any code directly, you need to create Maintenance Plan for backup.

AlwaysOn High Availability Groups only Provides “Copy-Only Backup” at Secondary replica, it means differential backup is not available at secondary replica.

Configure backup on Server2.

Step1 : Expand “Management” and click on “New Maintenance Plan..”

1

 

Step2 : Apply proper name to maintenance plan and configure regular schedule.

Click on change will create a job same maintenance plan name and you can define a schedule to execute job and run it manually as per your need.

2

 

Step3 : On the select “Back Up Database  (Full)” on Select Maintenance Tasks windows and click on “Next”.

4

 

Step4 : Select “AGDatabase” on “Define Backup Database (Full) Task”.

6

 

Click on Check Box  “Copy-Only Backup” and provide backup folder path.

7

–> Select “Report Options” and click on Next.

–> Click on “Finish” to complete the wizard and close it.

 

Thanks & Regards,

Nirav Gajjar

 

AlwaysOn High Availability Manually Failover

AlwaysOn High Availability Manually Failover

In my previous blog i have written on AlwaysOn High Availability Automatic Failover (From Server1 to Server2) and now in this blog i will show you AlwaysOn High Availability Manually Failover (Back from Server2 to Server1)
Step1 : After Automatic Failover Server2 is working as Primary Replica and Server1 is Secondary Replica (Synchronous).

Expand Availability Groups at Server2 –> Right click on “AGSample” and click on “Failover”.

1
Step2 : You have 2 available Replica to make it as Primary Replica.

Here we are going back to Server1 as primary Replica.

2

You can select any of replica from Server1 and Server3 but Server3 is Asynchronous commit mode and it has possibility of data loss.
Step3 : Provide connection to Server1 to Connect Replica.

3
Step4 :  Check summary and click on “Finish”.

4
Step5 : Verify Result.

5
Step6 : Now Backup to Server1 as Primary.

6

 

Server1 is now again working as Primary Replica and Server2 is Secondary and server1 has now Automated Failover server Server2.

 

 

Thanks & Regards,

Nirav Gajjar

AlwaysOn High Availability Automatic Failover

AlwaysOn High Availability Automatic Failover

Always on High Availability provides automatic failover up to 2 secondary server while you configure Availability Group and Server2 is selected as Automatic Failover Replica without any data loss.

Note: If you are frequently restart your server or SQL Server service then this option is not advisable.

AG6

See the current scenario.

–>Server1 is Primary Replica.

–>Server2 is Secondary Replica (With Synchronous and automatic failover)

–> Server3 is Secondary Replica (With Asynchronous and manual failover with data loss)

1

–>Now stop SQL Server service of Server1 to test automatic failover.

2

–> Now check

3

Automatic failover has been occured by AlwayOn Availability Group and Server2 initiated as  Primary Server.

Server3 is now Secondary Replica of Server2 and Server1 seems to be down.

Now when you start SQL Server service of Server1,  Server1 will be Secondary Replica of Server2.

Thanks & Regards,

Nirav Gajjar

Configure Availability Group Listener

Configure Availability Group Listener.

Availability Group Listener is a Virtual Network Name which provides client connectivity for Availability Group database on primary or secondary available replica.

In our scenario Server1 is PrimaryServer, Server2 SecondaryServer with automatic failover.

Here i am going to create Availability Group listener with name “AGSample” which points to Server1, in any case if PrimaryServer fails and SecondaryServer will work as Primary this listener automatically point to Server2.

Step1 : Expand Availability Group right click on “Avalability Group Listner” and select “Add Listener”.

AG13

Step2 : Provide Listener DNS Name “AGSample”.

AG14

Step3 : Open your Management Studio and provide “AGSample” in ServerName.

AG15

Now type “Select @@SERVERNAME” which gives “Server1” as result.

For testing, stop the SQL Server Service of  Server1, by doing this Server1 fails and Server2 come up as primary server.

Again open your Management Studio and provide “AGSample” in ServerName.

Now type “Select @@SERVERNAME” which gives “Server2” as result.

Availability Group Listener Connection String for application

“Server=tcp: AGSample,1433;Database=AGDatabase;IntegratedSecurity=SSPI”

Thanks & Regards,

Nirav Gajjar

Configure AlwaysOn High Availability

Configure AlwaysOn High Availability

This configuration has three servers Server1 (Primary Replica), Server2 (Secondary Replica Synchronous), Server3 (Secondary Replica Asynchronous)

Step1: Enable AlwaysOn High Availability Group on SQL Serer Instance on which you want to configure.
Open SQL Server configuration right click on your SQL Server Instance and select “AlwaysOn High Availabilty” tab.
this tab will show you “Windows failover cluster name”, here i have configured with “AGTesting”.
this option only available if you have created cluster if you have not configured windows clustering then follow this link to configure.Validate and Create Windows Cluster

EnableAlwaysON
Step2 : Connect primary server (here Server1) Right click on “AlwaysOn High Availability” and select “New Availability Group Wizard”.

 1
Step3 : On New Availability Group specify availability group name.

AG3
Step4 : On “Select Databases”  You can select number of databases here but you must have to take Full backup of database for which you want to add in Availability Group.
This wizard will show you all databases and we are going to configure “AGDatabase”
It shows database select is disabled in below screen as i have not taken any full bakup, so take a full backup to enable database selection.

AG4
Step5 : After taking full backup database is ready to select. click on checkbox and go to “Next”.

AG5
Step6 : On “Specify Replica” specify list of primary and secondary instance by clicking on “Add Replica” Button.

AG6
–> Server1 is primary server.
–> Server2 is secondary server with Synchronous commit and automated failover (this means Server2 automatically converts to primary server if  Server1 is down).
–> Server3 is secondary server with Asynchronous commit and this is server is available as readonly mode(You can execute select query on this database).
Step7 : On “Specify Replicas” window select “Enpoints” tab. AlwaysOn High Availability uses endpoint “5022” and it must be enabled in firewall on all 2 servers.

AG7
Step8 : On “Specify Replicas” window select “Backup Preferences”.

AG8

AlwaysOn Availability provides 4 type of backup options to overcome the load of primary server.
–>Prefer Secondary : this option will perform backup on secondary replica on first priority if no secondary available then backup will perform on primary replica.
–>Secondary Only : this option will perform backup on only secondary replica.
–>Primary : Backup occurs on primary replica. this option has last priority as this option will created load on primary server.
–>Any Replica : Backup can occur on any replica.
“Prefer Secondary” option is most deserving option in AlwaysOn, so here we are going with “Prefer Secondary” option.
Leave “Listener” tab configuration for now will configure it later and click on “Next”
Step9 : Provide shared location for initial data synchronization.

AG9
Step10 : Just observe “Validation” and ignore warning for listener.

AG10
Step11 : Result and close.

AG11
Step12 : Now you have completed “SQL Server 2012 AlwaysOn High Availability Configuration” on your system.

Expand AlwaysOn Availability Group and you will find “AGSample” is created as Availability Group.

AG12

Now connect all 3 servers and check database status.

AG13

Thanks & Regards,

Nirav Gajjar

Create tables schema to another database using SMO

Create tables schema to another database using SMO.

I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Collector;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Collections.Generic;
#endregion

namespace ST_ab378c7f30204c94a2f49a2b0f7ac500
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            try
            {
                string TableName;
                TableName = (Dts.Variables["User::TableName"].Value.ToString());
                Server server = new Server();
                Database source_database = new Database();
                Database destination_database = new Database();
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "reliancesp";
                server.ConnectionContext.ServerInstance = "User8\\SQL";
                source_database = server.Databases["Adventureworks2012"];
                destination_database = server.Databases["ReceiverDB"];

                Table table = source_database.Tables[TableName];
                string tables = table.Name.ToString();

                //Define properties in scriptingOptions
                ScriptingOptions scriptingOptions = new ScriptingOptions();
                scriptingOptions.ClusteredIndexes = true;
                scriptingOptions.NonClusteredIndexes = true;
                scriptingOptions.Default = true;
                scriptingOptions.DriAll = true;
                scriptingOptions.Indexes = true;
                scriptingOptions.IncludeHeaders = true;
                scriptingOptions.ScriptSchema = true;
                scriptingOptions.ScriptDrops = false;
                scriptingOptions.Triggers = true;

                StringCollection result = table.Script(scriptingOptions);

                var script = "";
                foreach (var line in result)
                {
                    script += line + "\r\n";
                }

                System.IO.StreamWriter fs = System.IO.File.CreateText(@"D:\data\" + TableName + ".sql");
                fs.Write(script);
                fs.Close();
                destination_database.ExecuteNonQuery(script);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Script to Shrink Database

Script to Shrink Database.

You need to use Database Console Commands of SQL Server to get back the free space.

DBCC ShrinkDatabase : Shrink all files from database.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, 10)
Go

It will shrink 90% free space and remains 10% free for database files.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, TruncateOnly)
Go

It will shrink data and log files to the last allocated extent, while you use TruncateOnly the percentage parameter will be ignored.

DBCC ShrinkFile : Shrink only single file.

You have a choice to which data file or log file you want to shrink.

–> “AdventureWorks_MDF” and “AdventureWorks_LDF” is a logical name of data files and log files.


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_MDF,10)
Go


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_LDF,10)
Go

Regards,
Nirav Gajjar

SQL Server Mirroring with Certificate

SQL Server Mirroring with Certificate.

Step1 : Take a backup of Database and then backup of log as well, of the database which you want mirrored.
On Principal Server


USE [master]
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.TRN'

Step2 : Copy this both database and log backup from principal server to mirroring server and restore it in specific drive.

On Mirroring Server


USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.TRN' WITH  FILE = 1,  NORECOVERY

Step3 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the principal server. Copy certificate backup to mirroring server.

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO

Step4 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the mirroring server. Copy certificate backup to principal server.

On Mirrored Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://PrincipalServerName.DomainName:7024';
GO

On Principal Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://MirringServer.DomainName:7024';
GO

To Failover Partner

ALTER DATABASE AdventureWorks2012
    SET PARTNER FAILOVER;

To Stop the mirroring.

--Run below script on Principal server
ALTER DATABASE AdventureWorks2012
    SET PARTNER OFF;
--Run below script on Mirroring server
Restore Database AdventureWorks2012 With NoRecovery

Notes:
1) Windows user must have an access to both server sharing folder.
2) Port number 7024 must me exceptional port number in firewall.

Identity reseed in SQL Server 2012

Identity reseed in SQL Server 2012

In SQL Server 2012, there is one issue regarding Identity column.

I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service.

You can find gaps in identity while you restart your sql server service, restart of server or failover.

Please review below code to try this.

USE ADVENTUREWORKS2012
GO
CREATE TABLE TestIdentJump
    (
         Id   INT IDENTITY (1, 1),
         Name VARCHAR(20)
    )
INSERT INTO TestIdentJump VALUES     ('testing1')
GO 5

SELECT *
FROM   TestIdentJump

IdentJump

Now restart your sql server service and run below code again and see the result.

INSERT INTO TestIdentJump VALUES     ('AfterRestart')
SELECT *
FROM   TestIdentJump

IdentJump1

Have you seen SQL server has started Identity column from 1002 instead of 6.

To resolve the problem there is two options

1) Run the checkpoint before you restart the sql server and check again.

This technique will solve the problem of jump in identity.

This technique has limitations, this will helps you out only in manual failover.

2) We need to open one trace flag to avoid this issue when server crisis and we don’t have a chance to run checkpoint.

Need to Open trace flag -t272

Right click on sql server service and click on property.

Select “Startup Parameters” tab.

Specify -t272 in textbox an click on add button and restart the service.

IdentJump3

Note : You need to this activity on principle server as well as mirror server.

Thanks & Regards,

Nirav Gajjar

Configure Service Broker in SQL Server 2012

Configure Service Broker in SQL Server 2012.
Initiator Script

Step1 : Create Database, Master Key and Initiator Certificate

USE MASTER
GO
CREATE DATABASE InitiatorDB
GO
ALTER AUTHORIZATION ON DATABASE::InitiatorDB TO SA
Go
ALTER DATABASE InitiatorDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
Go
ALTER DATABASE InitiatorDB SET TRUSTWORTHY ON
GO

--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE InitiatorBrokerCertificate
	WITH SUBJECT = 'broker.InitiatorCertificate',
	START_DATE = '2012-12-01',
	EXPIRY_DATE = '2099-12-31'
	ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE InitiatorBrokerCertificate
	TO FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO

Step2 : Create Target Certificate, Login and Endpoint

--Create Target Certificate from backup
CREATE CERTIFICATE TargetBrokerCertificate
	FROM FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO

CREATE LOGIN [TargetBrokerLogin]
	FROM CERTIFICATE TargetBrokerCertificate
GO

CREATE ENDPOINT ServiceBrokerEndPoint
	STATE = STARTED AS TCP (LISTENER_PORT = 4022)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InitiatorBrokerCertificate,
	ENCRYPTION = SUPPORTED);
GO

GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [TargetBrokerLogin]
GO

Step3 : Create User, Master Key, Initiator and Target Certificate

USE [InitiatorDB]
GO
--Create Broker User to Access Target Service
CREATE USER [TargetBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE Certificate InitiatorUserCertificate
 WITH Subject = 'user.InitiatorCertificate',
    START_DATE = '2012-12-01',
    EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO

BACKUP CERTIFICATE InitiatorUserCertificate
TO FILE = 'D:\Certificates\InitiatorUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate TargetUserCertificate
	AUTHORIZATION TargetBrokerUser
 FROM FILE = 'D:\Certificates\TargetUserCertificate.cer';
GO

GRANT CONNECT TO [TargetBrokerUser]

Step4 : Create Message Type, Contract, Queue, Service, Route and Binding

CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO

CREATE QUEUE [dbo].[InitiatorQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]
GO

CREATE SERVICE [Service://Sample/Initiator]
	ON QUEUE [dbo].[InitiatorQueue] ([CON://Sample/Contract])
GO
GRANT SEND ON SERVICE::[Service://Sample/Initiator] To [TargetBrokerUser]

CREATE ROUTE [TargetRequestRoute]
	WITH  SERVICE_NAME  = N'Service://Sample/Target' ,
	BROKER_INSTANCE  = N'106775EA-80AC-48ED-AEFB-92AE1F9A0736' ,  --Broker Instance of Target Databases
	ADDRESS  = N'TCP://User9.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [InitiatorRemoteServiceBinding]
	TO SERVICE N'Service://Sample/Target'
	WITH USER = [TargetBrokerUser] ,  ANONYMOUS = OFF
GO

Target Script

Step1 : Create Database, Master Key and Target Certificate

USE MASTER
GO
CREATE DATABASE TargetDB
GO
ALTER DATABASE TargetDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET TRUSTWORTHY ON
GO

--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetBrokerCertificate
	WITH SUBJECT = 'broker.TargetCertificate',
	START_DATE = '2012-12-01',
	EXPIRY_DATE = '2099-12-31'
	ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE TargetBrokerCertificate
	TO FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO

Step2 : Create Initiator Certificate, Login and Endpoint

--Create Target Certificate from backup
CREATE CERTIFICATE InitiatorBrokerCertificate
	FROM FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO

CREATE LOGIN [InitiatorBrokerLogin]
	FROM CERTIFICATE InitiatorBrokerCertificate
GO

CREATE ENDPOINT ServiceBrokerEndPoint
	STATE = STARTED AS TCP (LISTENER_PORT = 4022)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TargetBrokerCertificate,
	ENCRYPTION = SUPPORTED);
GO

GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [InitiatorBrokerLogin]
GO

Step3 : Create User, Master Key, Initiator and Target Certificate


USE [TargetDB]
GO
--Create Broker User to Access Target Service
CREATE USER [InitiatorBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetUserCertificate
 WITH SUBJECT = 'user.TargetCertificate',
    START_DATE = '2012-12-01',
    EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO

Backup Certificate TargetUserCertificate
TO File = 'd:\certificates\TargetUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate InitiatorUserCertificate
	AUTHORIZATION InitiatorBrokerUser
 FROM FILE = 'd:\certificates\InitiatorUserCertificate.cer';
GO

GRANT CONNECT TO [InitiatorBrokerUser]

Step4 : Create Message Type, Contract, Queue, Service, Route and Binding

CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO

CREATE QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]
GO

CREATE SERVICE [Service://Sample/Target]
	ON QUEUE [dbo].[TargetQueue] ([CON://Sample/Contract])
GO

GRANT SEND ON SERVICE::[Service://Sample/Target] To [InitiatorBrokerUser]

CREATE ROUTE [InitiatorResponseRoute]
	WITH  SERVICE_NAME  = N'Service://Sample/Initiator' ,
	BROKER_INSTANCE  = N'533AE34F-FD12-4C27-9545-1EEAD080404C' ,  --Broker Instance of Initiator Databases
	ADDRESS  = N'TCP://User8.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [TargetRemoteServiceBinding]
	TO SERVICE N'Service://Sample/Initiator'
	WITH USER = [InitiatorBrokerUser] ,  ANONYMOUS = OFF
GO

GRANT RECEIVE ON [TargetQueue] TO [InitiatorBrokerUser]

Send Message From Initiator Instance

Use InitiatorDB
Go
--Send Message to TargetQueue.
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg XML;

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [Service://Sample/Initiator]
     TO SERVICE N'Service://Sample/Target'
     ON CONTRACT [CON://Sample/Contract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg = N'Message for initiator.';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [MSG://Sample/Request]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION

Receive Message at Target Instance

Use TargetDB
Go

--Get Message From Initiator
SELECT CAST(MESSAGE_BODY AS XML),* FROM [TARGETQUEUE]

Regards,
Nirav Gajjar