Create SQL Server 2005 failover cluster
You must read the blog of "Cluster and Shared SCSI Bus" first.


Install SQL Server 2005


Components to Install
SQL Server Database Services
Create a SQL Server failover cluster (selected)
Workstation components, books online and development tools
Default Instance
Virtual Server Name:MYSQL
Virtual Server Configuration:
Network to use: Public
IP address: 24.80.99.181 255.255.252.0
Cluster Group Selection—Select the cluster group where the virtual server resources will be installed.
Cluster Group
QuorumGroup

Data file will be created in Y:\Microsoft SQL Server\

Cluster Node Configuration
--Selected nodes:Node2
--Required Node:Node1

Service Account for SQL server, SQL Agent, browser: VIP\SQLserver

VIP\SQLserverGroup, VIP\SQLagentGroup, and VIP\SQLfulltextGroup are used.

When you do SQL Server 2005 standalone installation, the local group SQLServer2005MSSQLUser$$, etc., is created and granted necessary permissions.
Within a cluster, SQL Server instance can run on any physical machine. Local Windows groups do not have a valid SID across machine. When you install a SQL server failover, the installation does not assume that you have the authority to create groups in the domain. So, you must create the three domain Windows groups before you install failover SQL server. During the installation, when you specify the three groups, the necessary permissions are granted to the three groups and the service account will be assigned as a member of the corresponding group.

I found that SQL Server was installed on both nodes. That is different from installation of exchange server 2003, which needs to be installed twice.

Problem:
Drive Y is not accessible from SQL Server Management Studio. Why? From Disk Management Console, I notice that there was no drive letter assigned to the drive. After I assign the letter Y, the drive Y can fail over and fail back. However, SQL Server Management Studio still can not access it. I uninstall SQL Server and reinstall it. Drive Y is available during the installation procedure.

Install a named instance in the cluster

Turn off both Node1 and Node2;

Create a fixed virtual Hard Disk, named.vhd (800MB);

Create a virtual SCSI adapter and connect it to named.vhd on node1 computer;

create a virtual SCSI adapter and connect it to named.vhd on node2 computer;

Turn on Node1 and open Disk Management console to initialize the newly created disk (don't convert it to dynamic); Format it as NTFS and assign Drive letter W;

Turn off Node1 and turn on Node2; Open DISK MANAGEMENT console to check the newly installed disk; nothing else to be done;

Turn on Node1 (both nodes are on)

At Node1, Cluster Administrator console

create a resource group: AcctGroup

Create a resource: AcctDisk (Physical Disk type) with the association to the drive W;

Install SQL Server 2005 as a named instance--ACCT

Virtual Server Name: YourSQL

Public IP: 24.80.99.182

Pay attention to the Cluster Group Selection, ACCTGROUP and QUORUMgroup are listed in available cluster groups; whereas the unavailable cluster group includes the CLUSTER GROUP--because the CLUSTER GROUP already has a virtual SQL server;

Select ACCTGROUP, the data file has the path of W:\Microsoft SQL Server\

YourSQL\ACCT named instance is installed.

Test the connection to two instances

Inside the cluster (node1 hosts both instances),

SQLCMD -S mySQL --Success

SQLCMD -S YourSQL\Acct --Success

From Host Computer (with Workstation Components installed),

Node1 hosts the two instances

• SQLCMD -S MySQL --Success


• SQLCMD -S YourSQL\Acct --Success

Create an alias for connection to YourSQL\ACCT:SQLCMD -S xxx ---OK

Summary:

• If resource A depends on resource B, both A and B must be in same resource group.
• Resource is a logical representation of components, software and hardware, in a system. A virtual server builds on those resources.
• In a computer, many things can go wrong. For example, If antivirus software can prevent a system from starting quickly, another node in the cluster will take over.

• We talk about RAID 1, 10, 01, and 5 a lot. They are hardware fault tolerance strategies. You can implement them for quorum (RAID 1) and Data (RAID 5 or RAID 10). The Cluster implementation is a software fault tolerance strategy. If a disk (attached on Shared SCSI BUS and which the virtual server relies on) goes wrong, no node can run the virtual server.
• Cluster uses share-nothing strategy.
  
  

Cluster and Shared SCSI BUS

Cluster and Shared SCSI BUS

Host computer and three virtual machines are with the following configuration;

Host Machine:

NIC (Internet)--24.80.99.180 255.255.252.0 (gateway) 24.80.96.1

Memory: 2GB

HD:40GB

Complete the creation and installation of domain VIP.COM

VMachine3:

Public NIC--24.80.99.180 255.255.252.0 preferred DNS: 127.0.0.1

You must make sure that NIC is attached to the Public Network.

Virtual IDE HD:6GB

Memory: 256MB

Install Windows Server 2003 and Active Directory(VIP.COM domain)

Computer Name:DC

Create a user: ClusterAdministrator

Leave the DC on all the time

---

For two-node cluster creation, you must follow the Turn On/Off sequence. If both nodes run at same time before the cluster is created, the shared disks will be corrupted.

---

complete the creation and installation of Node1

VMachine1:

Memory: 512MB

Hard Disks: 4 virtual hard disks installed

Virtual Hard Disk1: primary channel (0):vdisk1.vhd:6GB;

Virtual hard disk2:SCSI 0 ID 0:quorum.vhd:1GB;

Virtual hard disk3:SCSI 0 ID 1:data.vhd:1GB;

Virtual hard disk4:SCSI 0 ID 2:msdtc.vhd:200MB;

SCSI Adapters

Virtual SCSI Adapter1, virtual SCSI adapter2 and virtual SCSI adapter3 are attached a shared SCSI Bus;

Network Adapters

Virtual Network Adapter 1 is attached to External Network (can access physical adapter);

Virtual Network Adapter 2 is attached to Internal Network

Install Windows Server 2003 Enterprise Edition(Local IDE drive)

Computer Name:Node1

rename Virtual NIC 1 to public:24.80.99.175 255.255.252 Preferred DNS:24.80.99.180

rename Virtual NIC 2 to Private:10.237.0.19 255.255.0.0

Private NIC properties:

uncheck "client for Microsoft Network"

uncheck "File & Printer Sharing For Microsoft Network"

uncheck "Register this connection address in DNS"

uncheck "Enable LMHOSTS lookup"

Join it to VIP.COM domain;
Open Disk Management Console, follow the wizard to initialize the shared SCSI drives (3), don't convert them to dynamic (Cluster supports only basic disks), create one primary partition for each disk with the whole disk space, and format them as NTFS. I use the drive letters: X, Y, Z (Microsoft recommends the higher drive letters should be used.).

Users and Computers console and add the VIP\ClusterAdministrator to Local Administrators group;

Backup SystemState; When you configure and test clustering, it is possible that you cannot open the existing cluster and cannot create a new cluster.

Turn off the vMachine1 machine;

Creating vMachine2 and Installing Windows Server 2003 Enterprise

Hardware configuration is same as vMachine1.

Memory: 512MB
Hard Disks: 4 virtual hard disks installed
Virtual Hard Disk1: primary channel (0):vdisk2.vhd:6GB;
Virtual hard disk2:SCSI 0 ID 0:quorum.vhd:1GB;
Virtual hard disk3:SCSI 0 ID 1:data.vhd:1GB;
Virtual hard disk4:SCSI 0 ID 2:msdtc.vhd:200MB;
SCSI Adapters
Virtual SCSI Adapter1, virtual SCSI adapter2 and virtual SCSI adapter3 are attached a shared SCSI Bus;
Network Adapters
Virtual Network Adapter 1 is attached to External Network (can access physical adapter);
Virtual Network Adapter 2 is attached to Internal NetworkInstall Windows Server 2003 Enterprise Edition(Local IDE drive)

Computer Name:Node2
rename Virtual NIC 1 to public:24.80.99.176 255.255.252 Preferred DNS:24.80.99.180
rename Virtual NIC 2 to Private:10.237.0.20 255.255.0.0
Private NIC properties:
uncheck "client for Microsoft Network"
uncheck "File & Printer Sharing For Microsoft Network"
uncheck "Register this connection address in DNS"
uncheck "Enable LMHOSTS lookup"

Join it to VIP.COM domain;

Add VIP\ClusterAdministrator to Local Administrators group;

Open Disk Management console to initialize the SCSI drives; this time, you don't need to create partitions and format them. Even though there are no drive letters for the drives, they are in SCSI drives.

turn off Node2;

Turn on Node1;

CLuster Administrator to create a new cluster:MyCluster

Cluster IP:24.80.99.200

Cluster Service Account: VIP\ClusterAdministrator

Quorum:drive X

Start Node2 (node1 is running);

Cluster Administrator--Add a new node (Node2) to MyCluster;

The warning message does not matter. This is because all the shared disks are currently owned by Node1. Microsoft Cluster uses the share-nothing architecture. Node2 does not have access to the shared storage at the moment.

Set the preferred Owner for both Cluster Group, Group 0 and group 1.

Backup system states of both Node1 and Node2.

Test the clustering

Stop the Cluster Service at Node1; you will find both Cluster Group and group o and group 1 are moved to Node2.

Group 0:Disk Y failed to move to the other node.

I delete the group 0 and create a new physical disk resource under Cluster Group for Disk Y. The failover to the other node works.

--------------

Microsoft Distributed Transaction Coordinator (MS DTC)

MS DTC must be added to a Cluster. Microsoft recommends that MS DTC use a disk different from the quorum disk or any disk used by SQL server or other applications.

Using Virtual Server 2005 R2
===========================================
Host computer: two NICs

One IDE hard drive with 3 partitions (C:, E:, F:). Host Operating System is installed in C: drive.

Memory: 2GB

Windows Server 2003 installed

======================

Install Virtual Server 2005 R2

Access Virtual Server Administration Site

Because I have three partitions (C:, E:, F:) in my system, I add the search path (E: and F:) as follows:

Create three virtual disks:


Vmachine1, Vmachine2, and Vmachine3 are created. Each has one Virtual Hard Disk attached to Primary Channel (0). Memory for Vmachine1 and Vmachine2 are 512 Mb. Memory of Vmachine3 is 256 Mb.

Install Windows Server 2003, Enterprise Edition into Vmachine1

----------------------

Insert Windows Server 2003 CD into CD/DVD drive

Make sure that the Virtual Machine has its CD/DVD bound to the Physical CD/DVD.

Once the virtual machine is turned on, point to the virtual machine name, and then click Remote Control.
Once you are connected to the virtual machine, follow the instructions provided by the operating system to complete the installation.

--------------

To install Virtual Machine Additions

Administration Website

Start Vmachine1 and log onto it. (Press right ALT key) Get back to Base OS.

Click "Configure Vmachine1";
Select "Virtual Machine Additions "; Status --Remote Control--get into the Vmachine1 to complete the Virtual Machine Additions installation.

Restart the Vmachine1 and log on. Then you can move mouse between the virtual machine and base OS without pressing the right ALT key.

-------------------

Turn off the vMachine1; Make sure your Windows Server 2003 CD is still in CD/DVD drive;

Turn on the vMachine2; install Windows Server 2003;

Turn off vMachine2; turn on vMachine3 and install Windows Server 2003;

=========

Understanding the "Internal Network" Virtual Network

"External Network" Virtual Network is connected to the Physical NIC. Virtual machine can use this network to talk to Host Machine.

---------------------

HTTP ENDPOINT

Both KingPC and Queen computers are Windows Server 2003. They are in the same workgroup: VIP.

Account: Administrator with passsword (password) exists in both computers.

Make sure that both SQL Server 2005 with SP1 installed. If SP1 is not installed, the test will not work from network.

Queen computer has the Visual Studio 2005 installed.
============
Summary
You can create many HTTP ENDPOINT. Each ENDPOINT can have one or more WEB METHOD.
============
To understand the HTTP ENDPOINT, you must be familiar with IIS web hosting. First, ask yourself that how the clients access your web site? I want my clients access my web site as http://queen/sql?wsdl, then you create the following code block:

Work at QUEEN computer:
USE MASTER
sp_reserve_http_namespace N'http://queen:80/sql'
GO
Create ENDPOINT [sql]
STATE=STARTED
AS HTTP (PATH=N'/sql',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'queen',
CLEAR_PORT = 80)
FOR SOAP (
WEBMETHOD 'DayAsNumber'(
NAME=N'[master].[sys].[fn_MSdayasnumber]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
WEBMETHOD 'AddUser'( NAME=N'master.sys.sp_adduser'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS),
WEBMETHOD 'mgr'(
NAME=N'[AdventureWorks].[dbo].[uspGetEmployeeManagers]'
,SCHEMA=DEFAULT
,FORMAT=ALL_RESULTS),
BATCHES=DISABLED,
WSDL=Default,
DATABASE=N'master',
NAMESPACE=N'http://tempUri.org/',
SCHEMA=STANDARD
)

========

If you want your clients access the HTTP ENDPOINT as http://queen:888/sql?wdsl:

USE MASTER
sp_reserve_http_namespace N'http://queen:888/sql'
GO
Create ENDPOINT [sql888]
STATE=STARTED
AS HTTP (PATH=N'/sql',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'queen',
CLEAR_PORT = 888)
FOR SOAP (
WEBMETHOD 'DayAsNumber'(
NAME=N'[master].[sys].[fn_MSdayasnumber]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
WEBMETHOD 'AddUser'( NAME=N'master.sys.sp_adduser'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS),
WEBMETHOD 'mgr'(
NAME=N'[AdventureWorks].[dbo].[uspGetEmployeeManagers]'
,SCHEMA=DEFAULT
,FORMAT=ALL_RESULTS),
BATCHES=DISABLED,
WSDL=Default,
DATABASE=N'master',
NAMESPACE=N'http://tempUri.org/',
SCHEMA=STANDARD
)

==========
Work at KingPC:
===========

To access http endpoint at KingPC as http://myServer.vip.com/sql?wdsl

Create a website with the advanced setting as:



Make sure there is a host record in DNS server for myserver.vip.com.USE MASTER

sp_reserve_http_namespace N'http://myserver.vip.com:80/sql'
GO
CREATE ENDPOINT [sql_endpoint1]
STATE=STARTED
AS HTTP (PATH=N'/sql',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'myserver.vip.com',
CLEAR_PORT = 80)
FOR SOAP (
WEBMETHOD 'DayAsNumber'( NAME=N'[master].[sys].[fn_MSdayasnumber]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
WEBMETHOD 'GetMaterials'( NAME=N'AdventureWorks.dbo.uspGetBillOfMaterials'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS),
BATCHES=DISABLED,
WSDL=Default,
DATABASE=N'master',
NAMESPACE=N'http://tempUri.org/',
SCHEMA=STANDARD)
=======
Test the http endpoints from Visual Studio 2005:

Right Click your project: add web reference.

Test from Internet Explorer 6/7:

However, if you test the http endpoint from Firefox 2.0, you get the following error:

To delete a namespace: http://queen:888/sql
sp_delete_http_namespace_reservation N'http://queen:888/sql

I tried to expose the Extended Stored Procedure but unsuccessful. For example, the master.sys.msver is a extended stored procedure. When you include it in CREATE ENDPOINT, there is no error, but it will not show in IE7 or visual studio 2005.

Merge publication -- Web Synchronization

DC:Server and Member:KingPC

It's better both SQL Servers are in Member server. I got problem with scenario: One is DC and the other is Member. I have to use the Domain Administrator with the Merge Agent. The similar problem occurs when I configure PEER-to-PEER replication. Agent security gave me real headache.

Work at DC:Server computer


Install Certificate Authority component



create an alias: WWW to match the common name in DNS




Make sure http://www.vip.com/ will be resolved to IIS web server.

IIS:
Default Web Site--properties --Directory Security -- Server Certificate

Make sure that the same name is used in the above two screenshots.

The web clients (browsers, etc.) will access your web site by the http://www.vip.com/.
Enable the web site to request 128-bit encryption.

Create a folder--C:\InetPub\wwwroot\REPLICATION

Share Name: REPLICATION with access permission as:

Security:VIP\Agent WRITE and Share permission: EVERYONE CHANGE

Configuring a local distributor

Distributor Properties --Publisher --Default Snapshot Folder--\\Server\REPLICATION

VIP\Agent will be a member of db_owner role in distribution and AdventureWorks databases.

Creating a Merge Publication

Check the Default Snapshot Folder: the snapshot files are created.

Work at KingPC computer:

After a subcription is created and selects View Synchronization Status, "The system cannot find the file specified" message shows as follows:



I troubleshot as follows:


https://www.vip.com/certsrv/

https://www.vip.com/certsrv/certcarc.asp


Click on --install this CA certificate chain-- to install CA in the trust root certificate store.


https://www.vip.com/replication/replisapi.dll?diag


Because Merge agent security uses the regular user account VIP\agent, I use the VIP\agent to run the diagnostic. However, access denies message shows.


I use the Administrator to run replisapi.dll?diag as shown below:





It gets the desired result.


So, at Subscriber (KingPC), a Certificate with Administrator account is created. And then a Replication Merge proxy is created. After Changing the Merge Agent job proxy to the Administrator proxy, the Web Synchronization works.

I add the VIP\Agent to IIS_WGP group. Somehow, I can run replisapi.dll?diag test. But I still cannot use the VIP\agent to retrieve data from Web site unless I add the VIP\agent to the Administrators group.

Delivering snapshots through FTP and Web Synchronizing the merge publication

For network replication, the Default Snapshot Folder name must be shared, so UNC pathname can be used. SNAPSHOTS will be accessed by UNC path.

Snapshots can be delivered through FTP protocol.

Work at DC:Server computer
Install the FTP service;
FTP site configuration;




Local Distributor:

VIP\Agent is a member of db_owner role of AdventureWorks and distribution databases.

Publisher Properties:
Default Snapshot Folder--\\SERVER\ftproot\snapshots

Creating a publication --AdventureWorks:Document

PAL: VIP\Agent

As shown above, \snapshots\ftp must be specified. When a publication is created, the snapshot files will be automatically put --\\SERVER\ftproot\snapshots\ftp folder.

It is better to specify a login for FTP access.

"Put files in the following folder" -- if specified, a copy of snapshot files will be put under the folder. If the snapshot is compressed or the PUBLISHER is running Microsoft SQL Server 2000, you must specify the property.

After FTP publishing is configured, when subscription is created by the New Subscription Wizard, the Subscriber will use the FTP method to retrieve snapshot files.

Work at Member:KingPC computer
Subscription database: Kdata
USE MASTER
CREATE LOGIN [VIP\Agent] FROM WINDOWS
GO
USE Kdata
CREATE USER [VIP\Agent] FOR LOGIN [VIP\Agent]
GO
EXEC sp_addrolemember N'db_owner', N'VIP\Agent'
GO
Creating a pull subscription
The DOCUMENT table will be created.

Distribution Agent retrieves the snapshot files through FTP.

Drawback using FTP

FTP passwords is sent from the Subscriber to the FTP server in plain text. A single account controls access to the snapshot share.

Filtered merge publication has difficult to be implemented.

===============