Hosting a Report Server Database in a SQL Server Failover Cluster

SQL Server provides failover clustering support so that you can use multiple disks for one or more SQL Server instances. Failover clustering is supported only for the report server database; you cannot run the Report Server service as part of a failover cluster.

To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database in the Database Setup page of the Reporting Services Configuration tool.

Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.

=======================================================================
So on a Active passive node follow below steps,

1) On active node we already selected reporting services and installed
2) On passive node there is no Reporting services installed as its not a clustered feature
3) Install Reporting services as a named instance
4) configure RS with clustered name of SQL Server, by using existing URL and Report Server DB
5) Take a backup of encryption key and restore on passive node.
6) Now we can access the reports on Fail over.

Reporting Services under Cluster environment

1. Reporting Services under Cluster environment

http://www.microsoft.com/dynamics/crm/using/deploy/clusteringssrs.mspx

2. Deployment Topology.

http://msdn.microsoft.com/en-us/library/ms157293.aspx

3. Edition and Features supported from SQL Server.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 5)

This is the last part of the series. Once you have configured windows cluster and MSDTC, installing SQL is piece of cake. I already mentioned in part 1 that you have to download ISO (SQLEVAL.ISO) which we would need to attached as CD drive which would have setup files. I have copied the image on E:\Virtual Server\SQLEVAL.ISO

We also need to create domain account and domain groups in domain controller. So, logon to domain controller. Go to Start > Run > DSA.msc and follow below screen-shots.

AD Users and Computers

Give the details of account (i.e. SQLSVC)

SQLSVC Account

Set the password after clicking next button. Make sure that "Password never expires" is checked and "User cannot change password" is checked.

SQLSVC Account Password

One account is created, lets add a Group and add SQLSVC into the group.

SQLSVC New Group

Give Group Name "SQL Admin Groups"

SQLSVC Group Name

Right click on "SQL Admin Groups" properties and add "members"

SQLSVC New Group Members

Now you are done with Domain controller.

Now, let's mount ISO image to MyNode1 and MyNode2. To do this, go to Virtual server console.

CD_DVD

Click on CD/DVD and fill details as below

CD_DVD_Properties

Above has to be done on both the nodes. Once you do that, you can see SQL Installation CD mounted on MyNode1 and MyNode2.

Lets Start SQL setup now.

1. Log off from MyNode2. This is required to avoid running into known bug.
2. Log on to MyNode1.
3. Move all the resource groups to MyNode1 using Cluster AdminMyNode1 Active Groups
4. Go to DVD drive and double click on setup.exe from Servers folder
5. Accept EULA. Next.
6. Click 'Install' and wait for SQL Server to configure components and setup the installation. Once the required components (.NET Framework 2.0, MS SQL Native Client and the SQL Server 2005 setup support files) have been installed click 'Next'
7. Click 'next' when prompted with the screen 'Welcome to the Microsoft SQL Server Installation Wizard. The installation will then proceed through the 'System Configuration Check'- click 'next' to proceed.
8. Enter Registration Information, click 'next'
9. Components to Install- Select all components (SQL Server Database Services, Workstation components) , then click 'next' components to install
10. Since this is the first installation of a new database instance for SQL server, select 'Default Instance'.
11. Provide Virtual Server Name (VirtualSQL) VirtualSQL
12. Provide Virtual SQL Server IP Address (192.1.1.6) in Next screen and click on Add. Then Next
13. VirtualSQLIP
14. Provide SQL Group in Next screen as shown below Cluster Group Selection
15. Click Next on Below Screen
16. Select Nodes
17. Provide Administrator Password in next screen and click Next
18. Provide SQL Service Account created earlier on domain controller(sqlsvc) SQLSVC Provide
19. Provide Domain Group which we created earlier (SQL Admin Groups) SQLSVC Group Provide
20. Authentication Mode. Choose Mixed Mode (Windows Authentication and SQL Server Authentication), provide sa password and click 'next'
21. Keep the default settings (SQL Collations- Dictionary Order, case-insensitive, 1252 character set) and click next
22. Error and Usage Report Settings. Leave defaults (none checked). Click 'next'
23. Check installation Settings, click 'install'.
24. During the install process you can always see what is happening on other node.
25. Once the installation process is completed, click 'next'
26. And finally... Finish.

Now we are done with the setup and we should see the resources in cluster admin under SQL Group.

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 4)

In previous parts(Part 1, Part 2 and Part 3), we have completed windows clustering. Now, before installing SQL Cluster we have to configure MSDTC. We also need to make some shared drive which we would use to keep SQL Server databases and MSDTC. We are going to use Disk M for MSDTC and Disk X for SQL Server. We have already done the steps for Disk Q in part 2 (Create Quorum Disk section). I will reproduce the steps for Disk M and X here.
Configure Shared Disks

1. Turn off MyNode1 and MyNode2

2. Choose Virtual Disks > Create > Fixed Size Virtual Hard Disks

Create Virtual Disk(M Drive)

3. Provide Details for M Drive (250 MB) and hit "Create" button

Create Virtual Disk(M Drive) Details

4. Same way we would create X drive (2 GB) and Hit "Create" button.

Create Virtual Disk(X Drive) Details

Create Virtual Disk(Y Drive) Details

5. Below is what you would see in Windows Explorer

All Disks

6. Lets attach the disks to MyNode1 and MyNode2 (They are off at this moment) I am showing for MyNode1. Please follow same for MyNode2 also.

MyNode1_Edit Configuration

MyNode1 Edit hard disk

Click on Hard Disks

MyNode1 HardDisks Properties

Hit OK and it would attach the disks to MyNode1. Follow the same for MyNode2

7. Turn on ONLY MyNode1.

8. Login to MyNode1 and Start > Run > DiskMgmt.msc. Cancel on the screen if you get any.

9. You should see two new Disks which are Not Initialized. Right Click and choose Initialize Disk. Then you create partition by Right Click New Partition. Below is final screen shot which you should see.

MyNode1 Disk management

10. Now Turn Off MyNode1 and Turn On MyNode2

11. Go to DiskMgmt.msc on MyNode2 and assign drive letters (M for 250 MB drive and X for 2 GB Drive)

12. Open Cluster Administrator (Start > Run > CluAdmin)

13. Create New Group called MSDTC (File > New > Group) and provide details

New Group MSDTC

14. Choose Both Nodes as preferred owner. You might get an error at last screen but that's OK because MyNode1 is offline.

15. Create a new "Disk" resource under MSDTC Group (File > New > Resource) Choose details as below screen shot.

New Resource M Drive

17. Choose M Drive in "Disk Parameters" screen. and finish.

Disk Parameters

18. Now you should see a Disk resource under MSDTC Group. Right click and choose "Bring Online"

19. Follow 13 to 18 again to Create new groups called "SQL Group" and add "Disk X"

SQL Group Online

20. Up to here we have completed shared disk configuration for MyNode1 and MyNode2. Let's start MyNode1 and test fail over for new groups.

21. Before we install SQL Server, we have to configure MSDTC
Configure MSDTC as Resource

1. On MyNode1 and MyNode2 follow below steps
* Open Control Panel on the cluster node
* Double-click Add/Remove Programs
* On the left side of the screen, double click Add/Remove Windows Components
* Under Components, highlight Application Server and click Details
* Select Enable network DTC access, and then click OK
* Click Next
* Click Finish
2. Add "MSDTC IP Address resource" in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC IP Address
* In Resource Type, choose IP Address
* Click Next
* Click Next on Possible Owners screen
* Click Next on Dependencies Screen
* Give IP Address as 192.1.1.5
* Click Finish
* Right click the MSDTC IP Address resource and select Bring Online.
3. Add "MSDTC Network Name" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC NN
* In Resource Type, choose Network Name
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, move MSDTC IP Address and click on Add to bring on Right window. Now hit Next.
* Give Name as MSDTC
* Click Finish
* Right click the MSDTC NN resource and select Bring Online.
4. Add "MSDTC" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC
* In Resource Type, choose Distributed Transaction Coordinator
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, choose "MSDTC NN" and "M Drive" and click on Add to bring on Right window.

MSDTC Dependencies

* Click Finish
* Right click the MSDTC resource and select Bring Online.

MSDTC Group Online

Above is the screen shot of "MSDTC group". Make sure you have matching "Resource Type" as shown in the screen.

Test fail over for MSDTC group. This finishes all Prerequisites to install SQL Server on Cluster. Get ready to install SQL Server in last part of the series.

You can follow http://support.microsoft.com/kb/899191 to make changes to MSDTC Services.

Lets move on to last part of the series

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 3)

In Part 1 and Part 2, we configured till setting up Quorum drive on MyNode1.

First we need to change the IP address of heartbeat network which we added to MyNode1 and MyNode2.

Below is the screen shot of heartbeat IP on MyNode1. We need to give IP as 10.10.10.1 (once you hit tab, subnet would come automatically, 255.0.0.0)

This would be displayed as "Local Area Network 2", you can rename it to private or heartbeat for easy identification.

MyNode1 Heartbeat IP

Follow the same thing on MyNode2. Provide heartbeat IP address as 10.10.10.2

Once IP Setup is done, "Turn Off" MyNode2 and Login to MyNode1. First we are going to install One node cluster and later add MyNode2 in this cluster (we will call this as SQLCluster)

1, Go to Start>Run>Cluadmin

Start.run.Cluadmin

2. Select "Create New Cluster" from the options.

Create New Cluster

3. Hit OK

4. on "Cluster Name and Domain" screen, provide the domain name as MyCluster.com and Cluster Name as "SQLCluster" and Hit "Next"

5. In Next screen, Provide Name as MyNode1 and Also click on Advanced button to select "Advanced (minimum) configuration"

Select Computer

6. Next screen will analyze the settings, this may take a while to finish, You may get one warning since we selected "minimum configuration", just ignore that and hit next.

Analyzing Cluster

7. In Next screen, you will be prompted for IP Address. Give it as 192.1.1.4

Cluster IP Address

8. Next screen is for Cluster Service Account. For easiness of this lab, I am using Administrator account. In real world, you should create a separate account in domain.

Cluster Service Account

9. Hitting Next will show proposed cluster configuration.

Proposed Cluster Config

Make sure that you hit on Quorum and select Q: as shown in above screen shot.

10. Once above step is complete, we have successfully installed, one node cluster. Screen Below...

One Node Cluster

11. Now, remember that MyNode2 was Turned off by this time.At this point, Turn On MyNode2 and let it boot up completely. Don't Login to the node.

12. Come back to MyNode1 and Select File > New > Node

Add MyNode2

13. In below screen, provide MyNode2

Add MyNode2 Wizard 1

14. Keep Moving forward and you will get below screen.

Add MyNode2 Wizard 2

15. Keep going....

Add MyNode2 Wizard 3

16. Finally.... you are done with Windows clustering

Two Nodes Windows Cluster

Click for Part 4

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 1)

After delivering SQL Training, I realized that there is a need to write a blog which talks about Installing SQL Server 2005 cluster on Virtual Environment. Many DBA wants to see how Cluster looks like and they don't have capability to buy/see actual cluster.

There is an easy solution to the problem by using Virtual Server 2005.

I will try to be as descriptive as possible. In case something is not clear, please provide feedback.

Prerequisites

You also have to download Microsoft Virtual Server (http://technet.microsoft.com/hi-in/bb738033(en-us).aspx)

You also need to download ISO image for SQL Server 2005 evaluation edition (http://www.microsoft.com/downloads/details.aspx?FamilyID=6931fa7f-c094-49a2-a050-2d07993566ec&DisplayLang=en)

You should also have virtual hard disk of operating system. (Download available http://www.microsoft.com/downloads/details.aspx?FamilyID=77f24c9d-b4b8-4f73-99e3-c66f80e415b6&DisplayLang=en)

Above link has three files to download. You have to download all files. Once you download content from above link, you need to extract to a folder in local machine using WIN2K3R2EESP2.part1.exe. I extracted in (E:\Virtual Server)

Extracted Files

Please refer the readme file in above folder after you download.

NOTE: THIS SETUP WILL WORK ONLY FOR 30 days(depends on Read Me)

One you extracted the files, make three copies of virtual hard disk. One for Domain Controller, second for node1 and third for node2. I have created files as below:

E:\Virtual Server\MyDC\MyDC.vhd

E:\Virtual Server\MyNode1\MyNode1.vhd

E:\Virtual Server\MyNode2\MyNode2.vhd



Folders

Once you have downloaded and installed Virtual Server you should see new menu item in Programs.

Virtual Server Startup



Now, we will configure the Virtual Servers. First we need to Create Virtual Machine so that we have three separate virtual servers available. Make sure you set the Search Path at below screen.

On Left Side Menu choose Virtual Server > Server Properties > Search Path

Search Path


Create Domain Controller



below is the screen shot of "MyDC" setting. Information which I filled in is










Virtual Machine Name MyDC
Memory 256
Use Existing hard disk Use the location from drop down (E:\Virtual Server\MyDC\MyDC.vhd)
Connected To Internal Network







My DC Create Virtual Server

Click on Create.

Now, Go to Master Status and try to "Turn ON" the domain controller (I know its not yet a DC but in next step we are going to make it)

One you log in to MyDC, rename the machine to MyDC (by right click on My Computer > Properties) You need to restart the computer after renaming.

Next step is to run "dcpromo" command to make this machine as domain controller. Start>Run>dcpromo

dcpromo

Accept all settings as default, below are few which you need to modify.

Install DNS on this computer

MyCluster

During setup if it asks for windows installation CD, please point it to C:\WindowsInstallationFiles\I386 folder. Also, when prompted, please provide IP address as 192.1.1.1. You will be prompted to restart the machine.

When you can logon screen after reboot, "Log on to" should have MyCluster selected.

LogOn After Making DC

Now we have one machine ready which will act as domain controller for our cluster setup.
Create Member Servers

You are now having one Domain Controller in your Virtual Network. Now, lets add two member servers in the domain. As we did earlier, we have to add rest two vhd to server. Below screen shot is for Node2.



Node 2 Create Virtual Server

After configuration on Virtual Servers, follow below steps

1. Rename the nodes to MyNode1 and MyNode2 (still in Workgroup).

My Computer > Right Click > Properties > Computer Name

Rename Nodes

Then Restart MyNode1 and MyNode2

2. Disable the Firewall on all three machines.

Control Panel > Windows Firewall

Firewall

Select "Off" from the UI.

3. Change the IP address of machines.

MyNode1 - 192.1.1.2 (DNS Server 192.1.1.1)

MyNode2 - 192.1.1.3 (DNS Server 192.1.1.1)

4. Join the machine to domain (MyCluster.com) Below screen shot.

Join MyCluster

This finishes setup of one domain controller and two member servers.

Now we have to make MyNode1 and MyNode2 as single cluster.

lets move on to Part 2

How to install Clustered SQL Server 2008 on Windows 2008 Cluster using Hyper-V Part – 2

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/04/28/how-to-install-clustered-sql-server-2008-on-windows-2008-cluster-using-hyper-v-guest-clustering-part-2.aspx

OR

Thanks for overwhelming response of part 1 of the series. As a part of this series, we have created a Domain Controller (Win2k8MyDC). Let’s go ahead and follow the steps below to add two more machines into my domain (blakhani.com). For this you need two more VHDs extracted. Here is the folder structure I have followed:

F Drive

I have kept two more VHDs Win2k8MyNode1.vhd and Win2k8MyNode2.vhd in F:\Win2k8MyNode1 and F:\Win2k8MyNode2 folders respectively. We are going to build two machines on Hyper-V now. I would show you steps for Win2k8MyNode1 and same should be followed for the second node. Let’s Open Hyper-V manager, Right Click on Server Name and Choose New > Virtual Machine

New Virtual Mahine 01

Below are few self-explanatory screen shots so I am not going to spend time in explaining what they are.

New Virtual Mahine 02

Put Name of the machine.

New Virtual Mahine 03



We have configured Physical Memory for this machine to be 1 GB.

New Virtual Mahine 04

Choose Private VM Network

New Virtual Mahine 05

Here, we have to give the file which we have extracted earlier.

New Virtual Mahine 06

Finish the Wizard and Machine 2 is ready to use.

New Virtual Mahine 07

Once machine is started, it may ask you to restart. Once you are logged in, Below is the first screen you would get

First LogOn_00

As you can see, machine is has a randomly assigned name, it is in a workgroup with no IP Address and is not activated etc.. I am going to make some changes below. I would not spend much time in demonstrating “how to change computer name”, “how to change IP”, “How to join a machine to domain” etc. You can bing them and find steps easily. Finally below is the screenshot for Win2k8MyNode2

First LogOn_01

Okay. So all three machines are ready. Quick Summary
Machine Name Role IP Address
Win2k8MyDC Domain Controller 192.1.1.1
Win2k8MyNode1 Member Server 192.1.1.2
Win2k8MyNode1 Member Server 192.1.1.3

Just to be on safer side, make sure that you are able to Ping them from each other.

Now, Next step is to add new drive to the Domain controller and use iSCSI to connect them from Node1 and Node2.

But wait… before you do that, you may need to transfer some data from the “host” to the “guest”. I would spend some time in explaining how to do that.

Do you remember that we have created two network in Hyper V (Refer Part 1, Step 1). Lets go ahead and add second network to DC, Node1 and Node2. To avoid confusion, rename the current network adaptors on “guests” to “Domain Network” as shown below

Domain Network

Lets stop All three machines using Hyper V manager and add “Internal” network for all machines. Open Hyper V manager, Right Click on machine name, choose settings. On “Add Hardware” tab, choose “Network Adapter”

Add Hardware_Network Adapter

Click on Add and choose “Internal” as shown below.

Network Adapter_Internal

Click on Apply on the previous screen. Follow the same steps for the rest of the machines as well. Go ahead and start the machines. Once you login, you would see the new network coming up. Rename that to Internal and give IP addresses as 192.168.10.11 (DC), 192.168.10.12(Win2k8MyNode1), 192.168.10.13(Win2k8MyNode2). Now one host machine would have two networks. Please provide the IP address as 192.168.10.10. Here is the quick screenshot of all the Summary screens (notice IP Addresses)

Win2k8MyNode1_SummaryWin2k8MyNode2_SummaryWin2k8MyDC_Summary

Now, you should be able to ping Guest machine(s) from the host machine and we are all set to copy files from host to guest. You may wonder that its so simple. But believe me, with my limited knowledge of networking , it took me a day to figure this out.

To Configure shared drive you have to use iSCSI initiator and iSCSI Target. Initiator would be available on your VMs but you need to get the Target (its not free). There are few free iSCSI target software such as Starwind that can also be used for this setup. I am using internal version of Microsoft iSCSI Target.

Since I am going to add a disk to Win2k8MyDC which would be exposed to the rest of the nodes, I have created a disk using below steps

VirtualHardDisk_00

Click Next on Welcome Screen

VirtualHardDisk_01

Choose Fixed Size

VirtualHardDisk_02

I have created folder “F:\Shared disks” and would place file there.

VirtualHardDisk_03

Size.. umm… 80 GB

VirtualHardDisk_04

Complete the wizard

VirtualHardDisk_05

This completes creation of a big disk (80 GB) which would be used to make smaller drive later. Attach the disk to storage server. I'm using my Windows Server 2008 R2 Domain Controller for my storage server as well, this saves me one VM.

* Turn off Win2k8MyDC.
* Right Click on Server and choose Setting and follow below

Add_NewHDD_01

* Choose Add Hardware, Select SCSI Controller and “Add”

Add_NewHDD_00

* Click on Add again

Add_NewHDD_02

* Choose the hard drive created earlier.

Add_NewHDD_03

* Click on Apply / OK

Let’s go ahead and start the VM (Win2k8MyDC). Once you login to server, go to start > Run and type DiskMgmt.msc and hit OK. You would see 80 GB Hard Disk and all you need is “Right Click” and “online”

Add_NewHDD_05



if there is no drive letter, the go ahead and assign a drive letter, say S: in this case. Now you should be able to see the drive in My Computer (Of course, Your Computer) :)

Add_NewHDD_06

I guess there are too many images on this blog, so I am going to write next post to demonstrate the configuration of shared disk inside a “guest” using iSCSI target.

Stay Tuned and Happy Clustering !!!