Citrix Xen Application DataStore Move

To move SQL database from the old server to the new server:

• Stop IMA service on all Citrix servers
• Take a full backup of the data store DB from the old server
• Restore the backup to the new SQL server
• Edit the MF20.DSN ODBC file on Zone data collector server
• Change the old server to the new server
• Run the following command

C:> dsmaint config /user:sql-username /pwd:sql-password /dsn:"C:\Program Files\Citrix\Independent Management Architecture\MF20.dsn"

C:> dsmaint recreatelhc

• Start the IMA service
• Do the same thing for the rest of the servers

Passed 70-444 TodaY!!

Woooohooo~~ I passed 70-444 exam today which means I am officially MCITP Database Administrator (SQL 2005)! Don't really like to be a DBA but this helps me getting more details understanding of SQL 2005

How exciting after a loooooong night study for the last 3 months :)

MCITP One More to GO!!

After a long journey of studying, finally I finished my study of SQL 2005 and took the 70-443 exam today with the score 970.

This is part of my goal to get the MCITP: Database Administrator which I need to sit on one more exam 70-444 to finalize this.

As per my prev post, I am working with SQL 2005 servers and it gets me excited to know more

View SQL 2005 Table Fragmentation

To view how fragmented your table is, run the following query:

USE dbname
GO
SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
INDEX_ID,
CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('dbname'),OBJECT_ID('dbo.tablename'),NULL,NULL,NULL );
GO

Microsoft SQL 2005 Failover Cluster

Today, I'd like to share creating SQL 2005 Failover Cluster with Microsoft Virtual Server 2005. This involves creating 3x virtual Windows 2003 Enterprise Edition on Virtual Server, creating Quorum disks, configuring cluster and installing SQL 2005

To start, download the Microsoft Virtual Server 2005 here, Virtual Machine Remote Control Client Plus here. Install the Virtual Server 2005, but don't install the Web Server Administration part - we are going to manage it with the VMRCplus. Install the VMRCplus once the Virtual Server 2005 installation finished.

Create a Windows 2003 Enterprise Edition Template

Create a new virtual machine template with the following specs:
1x Dynamic HDD (IDE) - size: 10GB
1x NIC - connects to the Internal Network
512MB RAM



Boot the VM, install Windows 2003 Ent edition, install the VM additional tools and sysprep the machine, shut down

Create a Domain Controller

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB



1x NIC - connects to the Internal Network

start the virtual machine and do the following:

• rename the machine to: DC
• give an IP address: 192.168.0.1
• install DNS
• dcpromo
• reboot
• create 2x service accounts: _cluster and _sql
• create domain group for SQL cluster: SQL Cluster

Create a Virtual Heartbeat Network

Create a new virtual network to be used by the cluster for heartbeat. From VM manager, click on Virtual Networks and Create a new network, with physical adapter: None (Guests Only)



Create The First Node (NODE1)

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB
3x SCSI Adapters - all with Share SCSI bus and using SCSI adapter ID: 7
2x NIC - connects to the Internal Network and Heartbeat Network
3x Fixed HDD (SCSI) - size: 1GB (cluster quorum), 10GB (SQL data shared drive) and 1GB (MSDTC shared drive)





start the virtual machine and do the following:

• rename the machine to: NODE1
• change the IP address: NIC1: 192.168.0.10/24, DNS: 192.168.0.1; NIC2: 10.0.0.1/8
• join the computer to the domain
• reboot
• activate 3x SCSI drives, primary partition: cluster quorum -> Q:, SQL data -> S:, MSDTC -> M:
• install .net framework 2
• install 'Enable network DTC access'
• shutdown

Create The Second Node (NODE2)

Create a new virtual machine with the following specs:
Parent: Template
1x Differencing HDD (IDE) - size: 10 GB
2x NIC - connects to the Internal Network and Heartbeat Network
3x SCSI Adapters - all with Share SCSI bus and using SCSI adapter ID: 7. Assign the HDD (.vhd) files to the NODE1 vhd files according to its purposes (e.g. SCSI 0:0 -> cluster quorum, SCSI 1:0 -> SQL data shared drive, SCSI 3:0 -> MSDTC drive)



start the virtual machine and do the following:

• rename the machine to: NODE2
• change the IP address: NIC1: 192.168.0.2024, DNS: 192.168.0.1; NIC2: 10.0.0.2/8
• join the computer to the domain
• reboot
• assign drive letters to 3x SCSI drives: cluster quorum -> Q:, SQL data -> S:, MSDTC -> M:
• install .net framework 2
• install 'Enable network DTC access'
• shutdown

Create a Cluster

Start NODE1 virtual machine (make sure NODE2 is off)
start the cluster administratorr
create a new cluster, named: SQL
cluster IP address: 192.168.0.30
cluster account: _cluster
choose Q: as the quorum drive

Start NODE2

On NODE1 do the followings:
add NODE2 to the cluster
change the preference of the private network to use the second NIC card

Create Resource Group For MSDTC

on NODE1, start the cluster administrator and do the followings:

• rename the Group1 groups to MSDTC
• create a new resource, IP address; no dependencies, both NODE1 and NODE2 as possible owners and IP address: 192.168.0.40/24
• create a new resource, Network Name; depends on IP address, both NODE1 and NODE2 as possible owners and Network Name: MSDTC
• create a new resource, Distributed Transaction Coordinator; depends on Disk M: and Network Name

To enable MSDTC, do the followings:

• In Control Panel, open "Component services".
• Expand Component Services, expand Computers, right-click My Computer, and then click Properties.
• On the MSDTC tab, click Secuity Configuration under Transaction Configuration, click to select the Network DTC Access check box under Secuity Settings, and then click to select the following check boxes under Transaction Manager Communication:• Allow Inbound• Allow Outbound
• You cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:• Incoming Caller Authentication Required• No Authentication RequiredNote For more information about these options, click the following article number to view the article in the Microsoft Knowledge Base:899191 (http://support.microsoft.com/kb/899191/) New functionality in the Distributed Transaction Coordinator service in Windows Server 2003 Service Pack 1 and in Windows XP Service Pack 2
• Make sure that the Logon Account is set to NTAUTHORITY\NetworkService.
• Click OK. A message box explains that the MS DTC Service will be stopped and restarted, and that all dependent services will also be stopped and restarted. Click Yes.
  Note If this is a Majority Node Set (MNS) cluster, do not use the MNS resource as the storage device for MS DTC. MS DTC requires a storage resource such as a physical disk.
• Restart NODE1.

once NODE1 restarted, restart NODE2

Create Resource Group For SQL

on NODE1, start the cluster administrator and do the followings:

• rename Group0 to SQL
• make sure NODE1 is the active node for S: drive, cluster and MSDTC

SQL 2005 Installation

run the SQL 2005 setup
on 'Components to Install' setup page, make sure the 'failover cluster' option is selected



virtual servername: sql-cluster
network use: publicIP address: 192.168.0.50

on 'Cluster Group Selection' setup page, select the SQL cluster group for the virtual server resources
make sure NODE2 is selected on the nodes to be included in the virtual server
use the _sql user account for all the services
specify 'NET\SQL Cluster ' as the domain group

Testing

once the SQL 2005 installation finished, you can try to failover the cluster from one node to the other by 'Move Group' from the SQL resource group option

you can also try install the SQL Management Studio on the other computer (e.g. DC) and try to connect to the clustered name of the SQL - sql-cluster.domain.com



References:
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
http://msdn2.microsoft.com/en-us/library/ms189910.aspx
http://support.microsoft.com/kb/301600
http://support.microsoft.com/kb/817064
http://support.microsoft.com/kb/258750

MCTS: SQL Server 2005

After about 2 months studying the SQL Server 2005 books, today I took the 70-431 exam and pass with the score 940! I am now Microsoft Certified Technology Specialist (MCTS): SQL Server 2005

It was a great experience to know how great is SQL Server 2005. It has all the capabilities and capacity to host enterprise level data.

High availability is very important these days and SQL server 2005 has the technology to make sure the data is available 24/7/365 through either mirroring, replication, log shipping or snapshot.

The reason for me learning SQL Server 2005 is because the working environment that I am with is full of SQL Server 2005. I need to be able to support them and I need the necessary skills to do the job for mission critical database