Month: April 2012

SQL Server 2012 AlwaysOn with Hyper-v steps

Hardware used:

Processor: AMD FX 4100 (3.6 GHz) 4 core, 12MB Cache 
Mother board :  M5A88-M, Bios Ver 0801 
HDD: 1TB. 
OS: Windows server 2008-R2, with SP1. 

1. Change Name of computer to SERVER and Disable the firewall.
My Computer–>Properties–>Advanced system settings–>computer name–>change.
I have used a name ‘SERVER’. and I have set the IP of the physical machine as

2. Add role Hyper-v in Physical machine
‘ServerManager’ and add role, Select ‘Hyper-v’ and press ‘Install’

After successful installation expand the roles in Server management, now we have to create a new Virtual Machine.
Hold on….. before that we have to create one virtual network in Hyper-v as bellow

I named it as VM Network, and choose the ‘Private Virtual Machine Network’

4. Now we can go for Virtual machine creation

5. For setting up AlwaysOn, we need minimum 3 VM, out of which one should be Active Directory. First I am creating the VM for AD and so I am using the VM name as AD. Using this wizard we can keep the Virtual machine file in any location of the Physical machine. I am using a location in my E: drive.

6. I have allocated 1 GB of RAM to my first VM. You can allocate it as per the availability.

7. Select Private network, which we created. This is our Domain network.

8. Choose the virtual hard disk 

9. Now we have to say about the Operating system on this VM. I am using same OS as my physical machine which is Windows Server 2008. Here I have used the ‘Install an operating system from a boot CD’  and then press the finish button

10. One VM will be created and we can try to start the Virtual machine which named as ‘AD’
Expand the Roles and then under Hyper-v-manager you can see your machines name and in the Middle you can see the VM.  Right Click and ‘Start’. 

Oops !!  I got an error as ‘ The virtual machine could not be started because the hypervisor is not running’ , which seemed as little complex.  This is related to AMD processor and in this version of Hyper-v it is not handled. But we have a workaround to resolve this, and we have to disable one feature .

In command prompt we have to use a command as below

bcdedit /set xsavedisable 1

After this the physical machine has to restart. And now we can see the VM is running.

Once you connect to that VM, you can install OS from DVD drive. On first login Administrator password has to set. Then connect to AD as administrator, using Hyper-v manager, and Disable Firewall, and set the computer name as ‘AD’ and set the ‘VM Network’ IP as ‘’.

You can copy the Virtual hard disk and create Node1 and Node2. 
So now we have 3 VM with the below details 

1. AD (ip
2. Node1 (ip
3. Node2 (ip

11. Now we have to Setup Active Directory in the first virtual machine -AD.

Go to ‘server manager’ and add the feature .Net Framework 3.5 

Finally I got the below screen

12. After that add the Role ‘Active Directory domain services

Finally I got a screen like this (below)

So installation is over. 
13 Now we have to configure the Domain Controller for that we have use a command.
Start–>Run–>type ‘dcpromox.exe’, then you can see a screen like this, which is doing its checking

We can go to the next screen, press next

Now we have to create a new domain,
 I used a name for my domain as ‘’
It started its verification
Select Forest Functional Level as Windows Server 2008

Now we need to restart the virtual machine AD

 Shutdown AD machine in hyper-v.

14. Now we have to work with two of the Node virtual machines. We have to add those machines to the domain.For that we have to add a role “DNS Server”. But if we want to add ‘DNS Server’ role, “.Net framework 3.5” should be installed. 
So now we are going to add the .Net framework 3.5

15. Set IP as and computer name as Node1
After that you need to restart the machine

15. Add Feature .Net Framework 3.5 in Node1 virtual machine.
Follow the same step as 11, in Node1 virtual machine

16. Add role DNS Server in Node1
Server Manager –> AddRole–>DSN Server

17. Log-in in AD and create a domain user, 
Administrative Tools –>Active directory users and computers –>Users

18.I have created a domain user ‘manupradeepmanu’ . After creation of this user you need to add this user in Administrator.

For that click ‘Administrator’ group in the same window, then add the users.

19 You can disable the firewall in all the VM as this is a demonstration. In actual case we have to create rules in Firewall for Inbound and outbound connection.
Now you have to make sure you are able to ping to each machine. If you are not able to ping them, something wrong in the setup and then you will not be able to setup your domain network

20. Now we have to add Node1 virtual machine to domain the created domain.  In my case domain is
For that connect to Node1, using built in administrator(this is the first username which is created automatically and yo will be feeding the password at the time of OS installation)
Go to Network connections and in ‘IP set page’ check the DNS ip as
<<I will upload the screen shot>>

Go to “Server Manager”–>Computer Name–>Change–>Domain. 

21 It will be asking for the authentication, then you can use the domain userid and its password which you created just now. (step 17)

 Finally you will get a confirmation

22. Similarly Add Node2 to domain after chaning the name and ip to Node2, and
(Follow the same step as 20, in Node2)

23. Steps 23 to 26 are optional, and not mandatory for AlwaysOn. Uusing these steps i have configured one more network, and using which we can copy some data between physical machine and virtual machine. You can’t copy any information from physical machine (server) to AD,Node1 and Node2 with out a network

24. Add Internal network to all vm, after stopping them in Hyper-v.
25. Disable all the firewalls, and set ip of physical machine to
26. Make shared folders in Node1 and Node2, and copy SQL server setup to Node1-shared and Node2-Shared

27. Add feature ‘Failover Clustering’ in both the node.
Server Manager–>Add Feature–>Failover Clustering

You can restart the machine if it asking for that. 
Now you can see one more item in the Administrative Tools as ‘Failover Cluster Manager’
28. Set domain user as Administrator of Node1

Now we are going to set up Failover Cluster for high availability

29.Enable “AlwaysOn High Availability Group” on both the Nodes and restart SQL Service
  Right click on ‘SQL server’ and select properties, Go to AlwaysOn High Availability Group  and enable the checkbox

30. Log in with manupradeepmanu to Node1, Admin tools–>Failover cluster manager–>create a cluster. 

31. I have selected cluser name as cluster_manu, and selected as ip of the cluser.

32. In SQL server configuration manager, Select ‘SQL server Services’
 Start the mssql service if it not running.

Now we have to go for High Availability Group in SQL.
Double check step 29 by Right click on ‘SQL server’ and select properties, Go to AlwaysOn High Availability Group  and enable the check-box

33. Log in to Node1, SQL server Management studio
34. Change the log on userid for SQL server service on all the Node to the domain user. 
35. Create a userdatabase, DB_MANU and created a table, inserted 10 records
36. Take a full backup of the database.

37. SQL server management studio –>Node1–> Alwayson High Availability–>Create new Availability group.

I have used the name “AVG_manu” as my availability group name.

In the replica tab we have to add both primary and secondary as below.
check the endpoint tab

Check the backup preferance option tab

38. Here we have to add the Listener for Availability group. I have used the name “AVG_Listener” and have used the IP “”.

Oops I got an issue, the reason I have done this activity earlier and removed the cluster.

If you are doing first time you will not get this. If you are getting this, here is the method to resolve it.
Go to SQL -Configuration manager –>SQL Service–>properties–>AlwaysOn–>Disable the check box, and then enable it

After that use the ‘Re-Run’ validation button, then I got the screen like this.

39. Restarted the same wizard
So we have done with the setup, once you check the databases in Management Studio you can see the below screen

Here you have to double check the service account of SQL service, and it is better to use the domain user, and the shared location which we mentioned in the Cluster creation should be accessible to this account. If there is any mismatch , the database synchronisation will not work

40. Now we are going to test “Manual failover”
For this i have created a table in primary and inserted few records. Then open the secondary and see the values. As we have selected “Synchronous” both the nodes will get updated.

If you check the primary and secondary nodes we can understand its roles are changing once we manually fail-over to the other node.

SQL Server Service Broker one usage

Service Broker where we used;

Service broker in SQL server is a messaging system. This can be used to pass messages from different servers and can be routed thru the defined path. We can use Service Broker as a Queue system as well, obviously in First In First Serve logic.

          In one of the project we have implemented this only for queuing purpose. As per technical requirement we had to communicate some information between some modules in a project (system). These modules in the system can be treated as ‘Stand alone’ modules.

Here is the requirement: There is some daily data process which is part of one module. These data process are heavy and may take hours to get complete.  But during this process we have to do some communication to another system thru WCF, which will finally reach to SSRS subscription. The message receiving system is designed to do many functionality routing.

So what we have done is, we have created one Service Broker Queue in the database and the data-process activities will put a row to the SB-Queue. For messaging we have used a template called xslt. So which ever activity (procedures) need to send a message to other system have to put its own message in the pre-defined format (xslt) to SB-Queue.

To listen this SB-Queue, we have developed a windows-service application. This service will read the SB-Queue within a specified frequency (lets say 40 seconds), and will receive the message. Once this service received the message, it will be removed from the SB-Q. Based on the data in the message, this functionality-routing application will call the required WCF service and will pass the required parameter. Then the application flow will be continuing.

The major step to start work with Service Broker is

1.    Service Broker should be enabled in the database. By default this will be off.
To check Service broker status we can do a query
SELECT is_broker_enabled FROM master.sys.databases where name = DB_NAME()
2.    To enable Service Broker
use TestDB
ALTER DATABASE TestDB  SET ENABLE_BROKERwith rollback immediate
If it is throwing any master key related error, you need to create the master key
Create MasterKey Encryption by password = ‘password@123’

Now again try to enable it. If you want to check whether it is enabled or not use the below query
SELECT is_broker_enabled FROM master.sys.databases where name = DB_NAME()
use TestDB
–select is_broker_enabled from sys.databases where name=DB_NAME()
–alter database Testdb set enable_broker with rollback immediate
N'<xs:schema elementFormDefault=”qualified” targetNamespace=”” xmlns=”” xmlns:xs=””>
  <xs:element name=”Start_action”>
        <xs:element name=”request” type=”RequestData” minOccurs=”0″ nillable=”true” />
  <xs:complexType name=”RequestData”>
      <xs:element name=”TemplateName” type=”xs:string” nillable=”true” />
      <xs:element name=”CurrentUser” type=”xs:string” nillable=”true” />
      <xs:element name=”NamedArguments” minOccurs=”0″ nillable=”true”>
            <xs:element name=”NamedArgument” type=”NamedArgument” maxOccurs=”unbounded” minOccurs=”0″ />
  <xs:complexType name=”NamedArgument”>
      <xs:element name=”Key” type=”xs:string” nillable=”true” />
      <xs:element name=”Value” type=”ClrValue” nillable=”true” />
  <xs:element name=”StartResponse”>
        <xs:element name=”StartResult” type=”ResponseData” minOccurs=”0″ nillable=”true” />
  <xs:complexType name=”ResponseData”>
      <xs:element name=”WorkflowId” type=”ClrValue” minOccurs=”0″ />
  <xs:complexType name=”ClrValue”>
      <xs:extension base=”xs:string”>
        <xs:attribute name=”typeName” type=”xs:string” use=”required” />
create MESSAGE TYPETest_Messagetype
create MESSAGE TYPETest_ResponseMessagetype
Test_Messagetype SENT BY INITIATOR,
Test_ResponseMessagetype SENT BY TARGET
CREATE PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]
  DECLARE@RecvReqMsgName sysname;
  WHILE (1=1)
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg =message_body,
        @RecvReqMsgName = message_type_name
      FROM dbo.SpRightWorkFlowSendQueue
    ), TIMEOUT 5000;
    IF (@@ROWCOUNT = 0)
    IF@RecvReqMsgName =
       END CONVERSATION @RecvReqDlgHandle;
    ELSE IF @RecvReqMsgName =
         VALUES (@RecvReqDlgHandle, @RecvReqMsg)
       END CONVERSATION @RecvReqDlgHandle;
            STATUS = ON,           
            PROCEDURE_NAME = Test_HandleSubmitWorkflowResult,
            MAX_QUEUE_READERS = 1,
            EXECUTE AS N’dbo’
CREATE QUEUE Test_ReceiveQueue;
CREATE SERVICE Test_SendService ONQUEUE Test_SendQueue (Test_Contract);
CREATE SERVICE Test_ReceiveService ONQUEUE Test_ReceiveQueue(Test_Contract);
CREATE TYPE [dbo].[SB_Parameters] AS TABLE(
      [PARAMETER_NAME] [varchar](100) NOT NULL,
      [PARAMETER_CLR_TYPE] [varchar](20) NULL,
      [PARAMETER_VALUE] [varchar](4000) NULL,
CREATE FUNCTION [dbo].[SB_CreateXMLMessage] 
 @templateName nvarchar(100), 
 @CurrentUser varchar(50),
 @namedArguments [dbo].[SB_Parameters] READONLY 
RETURNS nvarchar(max) 
 DECLARE@message nvarchar(max); 
   @message = 
     @templateName AS“request/TemplateName”,
     @CurrentUser AS“request/CurrentUser”, 
       PARAMETER_CLR_TYPE AS “Value/@typeName”, 
      FOR XML PATH (‘NamedArgument’), type 
     ) AS “request/NamedArguments” 
    FOR XML PATH (‘Start_action’) 
CREATE PROCEDURE [dbo].[SB_SendActionRequest]
       SENDON CONVERSATION@Conversation MESSAGE TYPETest_Messagetype (@Message);            


declare@ParameterValues dbo.SB_Parameters
declare@Conversation UNIQUEIDENTIFIER
declare @Message NVARCHAR(4000)
(‘key1’, ‘System.String’, ‘Key1 value’)
,(‘key2’, ‘System.String’, ‘Key2 value’)
,(‘key3’, ‘System.String’, ‘Key3 value’)
EXEC  @message =dbo.SB_CreateXMLMessage‘Action Template1’,‘userid’,@ParameterValues
select cast(@message as xml)       
EXEC dbo.SB_SendActionRequest @Message , @Conversation OUTPUT
select * fromTest_ReceiveQueue
receive * fromTest_ReceiveQueue
To remove all the objects for this test

use TestDB
drop PROCEDURE [dbo].[SB_SendActionRequest]
drop function  [dbo].[SB_CreateXMLMessage]
drop type [SB_Parameters]
drop SERVICE Test_ReceiveService
drop SERVICE Test_SendService
drop QUEUE Test_SendQueue
drop QUEUE Test_ReceiveQueue
drop PROCEDURE [dbo].[Test_HandleSubmitWorkflowResult]
drop CONTRACT Test_Contract
drop MESSAGE TYPETest_ResponseMessagetype
drop MESSAGE TYPETest_Messagetype
DROP XML SCHEMA COLLECTION [Test_Schemacollection]

SSRS RDL deployment for integrated mode

Here is some mechanism to deploy RDL files to Sharepoint -integrated mode as bulk.
1. Created  “rss” file which can take two parameter

1.1 Parameter 1 : ReportFile path

1.2. Parameter2 : Actual Report Name in share point

2. Using dynamic variable switch (-v) we have to call RS utility (rs.exe) and pass the physical location where the RDL is available and destination location (URL).


rs.exe -i deployreport.rss -s http://xxx:2222/ReportServer -e Mgmt2006 -v ReportSource=%1-v ReportName=%2

3. Created a VB script file which generate a BAT file with as many lines as RDL number in the source folderEg:cscript generate.vbs ReportDeployment

4. One wrapper BAT file will call the VB script file and pass the source location-folder name which contain the RDLs to deploy.