How to manage SQL 2012 with powershell

The graphical management tools provide just about everything you need to work with SQL Server. Still, there are many times when you might want to work from the command line, such as when you are working on a Windows Server 2008 R2 Core installation. To help with all your command-line needs, SQL Server 2012 includes the SQL Server provider for Windows PowerShell (also known as “SQL Server PowerShell”). To work with SQL Server via Windows PowerShell, you must first open a Command Prompt window or Windows PowerShell prompt and then start SQL Server PowerShell by typing sqlps at the command line.

Windows PowerShell introduces the concept of a cmdlet (pronounced “commandlet”). A cmdlet is the smallest unit of functionality in Windows PowerShell. Cmdlet names are not case-sensitive. SQL Server PowerShell cmdlets include the following:

– Backup-SQLDatabase Performs backup operations on SQL Server databases.

– Convert-UrnToPath Converts a SQL Server Management Object Uniform Resource Name (URN) to a SQL Server provider path. The URN indicates
a management object’s location within the SQL Server object hierarchy. If the URN path has characters not supported by Windows PowerShell, the characters are encoded automatically.

– Decode-SQLName Returns an unencoded SQL Server identifier when given an identifier that has been encoded.

– Encode-SQLName Encodes special characters in SQL Server identifiers and name paths to formats that are usable in Windows PowerShell paths. The characters encoded by this cmdlet include :/%<>*?[]|. If you don’t encode these characters, you must escape them by using the single quotation mark (‘) character.

– Invoke-PolicyEvaluation Evaluates management policies applied to SQL Server instances. By default, this cmdlet reports compliance but does not enforce compliance. To enforce compliance, set –AdHocPolicyEvaluationMode to Configure.

– Invoke-Sqlcmd Runs a Transact-SQL (T-SQL) or XQuery script containing commands supported by the SQLCMD utility. By default, this cmdlet doesn’t set any SQLCMD variables or return message output; only a subset of SQLCMD commands can be used.

– Restore-SQLDatabase Performs restore operations on SQL Server databases.

To get detailed information about a cmdlet, type get-help cmdletname –detailed, where cmdletname is the name of the cmdlet you want to examine. To get detailed information about the SQL Server provider, which provides SQL Server functionality for Windows PowerShell, type get-help sqlserver | more.

 

You can use the sqlps utility on any computer where you’ve installed SQL Server or the command-line management tools. The sqlps utility starts a Windows PowerShell session with the SQL Server PowerShell provider imported so that you can use its cmdlets and work with instances of SQL Server. When you are working with Windows PowerShell or scripts, you can import the SQLPS module to load the SQL Server provider, which automatically loads the required assemblies and initializes the environment. While you previously needed to use an initialization script, this is no longer required so long as you import the SQLPS module prior to trying to access the SQL Server instance. For best results, import the SQLPS module using the following command:

   Import-Module "sqlps" –DisableNameChecking

You can work with cmdlets by executing commands directly at the shell prompt or by running commands from scripts. You can enter any command or cmdlet that you can run at the Windows PowerShell command prompt into a script by copying the related command text to a file and saving the file with the .ps1 extension. You can then run the script in the same way that you would any other command or cmdlet. However, when you are working with Windows PowerShell, the current directory might not be part of the environment path. For this reason, you might need to use the ./ notation when you run a script in the current directory, such as the following:

./runtasks

The current execution policy for SQL Server PowerShell controls whether and how you can run scripts. Although the default configuration depends on which operating system and edition you’ve installed, you can quickly determine the execution policy by entering get-executionpolicy at the Windows PowerShell prompt.

To set the execution policy to require that all scripts have a trusted signature to execute, enter the following command:

set-executionpolicy allsigned

To set the execution policy so that scripts downloaded from the web execute only if they are signed by a trusted source, enter:

set-executionpolicy remotesigned

To set the execution policy to run scripts regardless of whether they have a digital signature and work in an unrestricted environment, you can enter the following command:

set-executionpolicy unrestricted

For administration at the Windows PowerShell prompt, you use Invoke-Sqlcmd to run T-SQL or XQuery scripts containing commands supported by the SQLCMD utility. Invoke-Sqlcmd fully supports T-SQL and the XQuery syntax supported
by the Database Engine, but it does not set any scripting variables by default. Invoke-Sqlcmd also accepts the SQLCMD commands listed in Table 1-3, later in this chapter. By default, results are formatted as a table, with the first result set displayed automatically and subsequent result sets displayed only if they have the same column list as the first result set.

The basic syntax you use most often with Invoke-Sqlcmd follows:

Invoke-Sqlcmd [-ServerInstance ServerStringOrObject]
[-Database DatabaseName] [-EncryptConnection ]
[-Username UserName] [-Password Password] [[-Query] QueryString] [-DedicatedAdministratorConnection]

[-InputFile FilePath] [ | Out-File –filepath FilePath]

The command’s parameters are used as follows:

–Database Specifies the name of the database that you want to work with. If you don’t use this parameter, the database that is used depends
on whether the current path specifies both the SQLSERVER:SQL folder
and a database name. If both are specified, Invoke-Sqlcmd connects to the database that is specified in the path. Otherwise, Invoke-Sqlcmd connects to the default database for the current login ID.

NOTE Use–IgnoreProviderContext to force a connection to the database that is defined as the default for the current login ID.

–DedicatedAdministratorConnection Ensures that a dedicated administrator connection (DAC) is used to force a connection when one might not be possible otherwise.

–EncryptConnection Enables Secure Sockets Layer (SSL) encryption for the connection.

–InputFile Provides the full path to a file that should be used as the query input. The file can contain T-SQL statements, XQuery statements, SQLCMD commands, and scripting variables. Spaces are not allowed in the file path or file name.

 –Password Sets the password for the SQL Server Authentication login ID that is specified in –Username.

–Query Defines one or more queries to be run. The queries can be T-SQL queries, XQuery statements, or SQLCMD commands. Separate multiple queries with semicolons.

TIP You do not need to use the SQLCMD GO command. Escape any double quotation marks included in the string and consider using bracketed identifiers such as [EmpTable] instead of quoted identifiers such as “EmpTable”. To ensure that the message output is returned, add the –Verbose parameter. –Verbose is a parameter common to all cmdlets.

–ServerInstance Specifies the name of an instance of the Database Engine that you want to work with. For default instances, specify only the computer name, such as DbServer23. For named instances, use the format “ComputerNameInstanceName”, such as DbServer23EmployeeDb.

–Username Sets the login ID for making a SQL Server authentication connection to an instance of the Database Engine. You also must set the password for the login ID.

NOTE By default, Invoke-Sqlcmd attempts a Windows authentication connection
by using the Windows account running the Windows PowerShell session. Windows authentication connections are preferred. To use a SQL Server authentication connection instead, specify the user name and password for the SQL login ID that you want to use.

With this in mind, you could replace the following T-SQL statements:

USE OrderSystem;
GO
SELECT * FROM Inventory.Product
ORDER BY Name ASC
GO

with the following Windows PowerShell command:

Invoke-Sqlcmd -Query "SELECT * FROM Inventory.Product; ORDER BY Name ASC"
-ServerInstance "DbServer23OrderSystem"

You also could read the commands from a script, as shown in Sample 1-1. SAMPLE 1-1 Example SQL Command Script.

Contents of SqlCmd.sql Script.

 SELECT * FROM Inventory.Product
 ORDER BY Name ASC

Command to Run the Script

 Invoke-Sqlcmd -InputFile "C:ScriptsSqlCmd.sql"

22 PART I Microsoft SQL Server 2012 Essentials

When you work with Windows PowerShell, don’t overlook the importance of SQL Server support being implemented through a provider. The data that providers expose appears as a drive that you can browse. One way to browse is to get or set the location with respect to the SqlServer: provider drive. The top of the hierarchy exposed is represented by the SQL folder, then there is a folder for the machine name, and finally, there is a folder for the instance name. Following this, you could navigate to the top-level folder for the default instance by entering

Set-Location SQLSERVER:SQLDbServer23Default

You could then determine the available database structures by entering Get-ChildItem (or one of its aliases, such as ls or dir). To navigate logins, triggers, endpoints, databases, and any other structures, you set the location to the name
of the related folder. For example, you could use Set-Location Databases and then enter Get-ChildItem to list available databases for the selected instance. Of course, if you know the full path you want to work with in the first place, you also can access it directly, as shown in the following example:

Set-Location SQLSERVER:SQLDbServer23DefaultDatabasesOrderSystem

Here, you navigate to the structures for the OrderSystem database on DbServer23’s default instance. If you then want to determine what tables are available for this database, you could enter:

Get-ChildItem Tables

Or you could enter:

Set-location Tables
Get-ChildItem

To manage SQL Server 2012 from a computer that isn’t running SQL Server, you need to install the management tools. In the SQL Server Installation Center, select Installation, and then click the New Installation Or Add Features To An Existing Installation option. When the wizard starts, follow the prompts. On the Feature Selection page, select the Management Tools—Basic option to install Management Studio, SQLCMD, and the SQL Server provider for Windows PowerShell.

For remote management via Windows PowerShell, you need to ensure that Windows Remote Management (WinRM) and Windows PowerShell are both installed and made available by using the Add Features Wizard. You also need to enable remote commands on both your management computer and the server running SQL Server.

You can verify the availability of WinRM and configure Windows PowerShell for remoting by following these steps:

1. Click Start, All Programs, Accessories, and Windows PowerShell. Then start Windows PowerShell as an administrator by right-clicking the Windows PowerShell shortcut and selecting Run As Administrator.

2. The WinRM service is configured for manual startup by default. You
   must change the startup type to Automatic and start the service on each computer you want to work with. At the PowerShell prompt, you can verify that the WinRM service is running by using the following command:

         get-service winrm

0. f the service is stopped, enter the following command to start the service and configure it to start automatically in the future:

         set-service –name winrm –startuptype automatic –status running

1. To configure Windows PowerShell for remoting, type the following command:

         Enable-PSRemoting –force

   You can enable remoting only when your computer is connected to a domain or private network. If your computer is connected to a public network, you need to disconnect from the public network and connect to a domain or private network and then repeat this step. If one or more of your computer’s connections has the Public connection type but you are actually connected to a domain or private network, you need to change the network connection type in the Network And Sharing Center and then repeat this step.

In many cases, you can work with remote computers in other domains. However, if the remote computer is not in a trusted domain, the remote computer might not be able to authenticate your credentials. To enable authentication, you need to add the remote computer to the list of trusted hosts for the local computer in WinRM. To do so, type the following:

winrm s winrm/config/client ‘@{TrustedHosts=”RemoteComputer”}’ where RemoteComputer is the name of the remote computer, such as winrm s winrm/config/client ‘@{TrustedHosts=”DbServer23″}’

When you are working with computers in workgroups or homegroups, you must use HTTPS as the transport or add the remote machine to the TrustedHosts configuration settings. If you cannot connect to a remote host, you can verify that the service on the remote host is running and is accepting requests by running the following command on the remote host:

winrm quickconfig

Status   Name
------   ----
Running  WinRM

DisplayName
-----------
Windows Remote Management

24 PART I Microsoft SQL Server 2012 Essentials

This command analyzes and configures the WinRM service. If the WinRM service is set up correctly, you see output similar to the following:

WinRM already is set up to receive requests on this machine.
WinRM already is set up for remote management on this machine.

If the WinRM service is not set up correctly, you see errors and need to respond affirmatively to several prompts that allow you to configure remote management automatically. When this process is complete, WinRM should be set up correctly. Don’t forget that you need to enable remote management on the database server as well as your management computer.

Best practices for SQL Clustering

SQL Server clustering is a high-availability technology for SQL Server instances. It involves the sharing of server resources between one or more nodes (or servers), which have one or more shared disks grouped into logical units called resource groups. A resource group containing at least one IP address, network name and disk resource is called a virtual server. The cluster service arbitrates ownership of the resource groups. A single node can own a resource group and its associated resources at any given time.

Clustering basics

Each virtual server appears on the network as a complete system. When the virtual server contains SQL Server resources, clients connected to the virtual server access resources on its current host node. While the terms “active” and “passive” are often used in this context, they are not fixed roles, as all nodes in a cluster are interchangeable. Should the current host, sometimes designated as the primary, fail, the resource group will be transferred to another node (secondary node) in the cluster. With clusters having more than two nodes or two instances, it is important to set failover order by choosing the preferred node ownership order for each instance. The secondary will become the primary and host the virtual server. Active client connections will be broken during failover, but they can reconnect to the virtual server now hosted by the new node. The clients will have to reconnect manually, and work in progress will be lost during the failover. Most commercial applications now handle this reconnection task seamlessly.

The goal of clustering is to provide increased availability to clients by having a hot standby system with an automatic failover mechanism. SQL Server clustering is not a load-sharing or scale-out technology. On all clusters during a failure there will be a brief database server interruption. On large clusters with multiple nodes and instances, clients may experience degraded performance during a failure event but they will not lose database availability.

Clustering topologies

There are four types of cluster topologies — or arrangements of nodes in a cluster:

• Single instance
• Multi-instance
• N+1
• N+M

Single instance:

In this case, one node in a cluster owns all resource groups at any one time and the other nodes are offline. Should the primary node owning the resources fail, the resource groups will be transferred to the secondary node, which comes online. While the secondary node comes online, it will assume ownership of the resource groups, which typically consist of disks containing your database files and transaction logs. The secondary node comes online (starts up), and SQL Server will start up on the virtual server and roll uncommitted transactions in the transaction log backward or forward as it recovers the database.

This topology was formerly called active-passive. Single-instance clustering is most frequently used for mission-critical applications, where the cost of downtime far outweighs the cost of the wasted hardware resources of the secondary node sitting idle while offline.

Multiple instance:

In this situation, one virtual server in a cluster owns some of the resource groups and another virtual server owns other resource groups. At any one time, the virtual servers themselves can be hosted by a single node or different nodes and would appear to clients as named instances of a single server. In that case, they are named instances of a virtual server, hence the name multiple instance. With multiple-instance clustering, previously called active-active, the hardware requirements of each individual node are greater as each node may at any one time be hosting two (or more) virtual servers.

You should consider multiple-instance clusters to be more cost effective than single-instance clusters as there are no nodes offline or waiting. However, should one node host more than one virtual server, performance for clients is typically degraded. Your best bet is to use multiple instances when you require high availability but not high performance.

N+1:

This is a modification of multiple-instance clustering topologies where two or more nodes share the same failover node. The secondary node will need enough hardware capabilities to support the load of all N servers at any one time should they all fail over simultaneously. You can achieve cost savings if multiple clusters use the same failover node. However, the cost of an individual

node tends to be small in comparison to other related clustering costs, such as storage.

Many people consider N+1 to be more cost effective than multiple-instance clustering because there is only one secondary node offline (or waiting) for several active nodes. However, depending on the hardware configuration of the failover node, it does not offer the performance of multiple-instance clustering. Use N+1 in environments where cost constraints force you to reduce the number of failover nodes and you need high availability but not high performance.

N+M:

In a situation where you have two or more working nodes in a cluster along with two or more standby nodes, it is typically configured in eight-node clusters with six working nodes for every two standby, or five working nodes for every three standby.

N+M offers some of the cost benefits of N+1, but it has a lower chance of performance degradation during a multiple failure event than N+1 since the failover node(s) do not have to support the entire load of the failed nodes. Use N+M in environments where cost constraints force you to reduce the number of failover nodes and at the same time provide a high level of performance.

Clustering dependencies

SQL Server clustering has several dependencies:

• Network
• Hardware
• Software

Network dependencies:

Clustering requires a private network among all nodes in a cluster. Clustering services use a private communication channel on each node to keep in sync with each other. This allows the cluster to communicate and act appropriately even if the public network is offline. Looks-Alive and Is-Alive checks — used by cluster services to determine if a cluster resource group is “up” — connect over the public networks to best emulate a client connection process.

Hardware dependencies:

Clustering requires specialized hardware and software. And to share resources between nodes, you need specialized disk controllers. Clustering hardware must be certified by Microsoft to meet the requirements of clustering. And, you must have a second set of network cards to provide the private network between cluster nodes.

Software dependencies:

To benefit from clustering services, you need specialized versions of the operating system (Windows 2000 and 2003 Enterprise or Data Center editions). You will also need SQL Server 2000 Enterprise Edition, SQL Server 2005 Standard Edition (up to two nodes) or SQL Server 2005 Enterprise Edition (up to eight nodes).

Clustering best practices

What follows is a list of clustering best practices. I have broken these down according to dependencies.

Network best practices

There are two different and contradictory settings required for the public network and the private network in clustering.

Private

Ensure the private network is private. Clustering requires a 150-ms ping response time. If your private network is saturated or congested with other network traffic, you may find your clusters failing over unexpectedly. On your private network, consider isolating traffic by implementing a VLAN (virtual LAN), a separate subnet or use a crossover cable for Single-instance clusters. The actual traffic generated by cluster communication is small, so high-bandwidth networks are unnecessary. However, they must still be low latency and reliable. Make sure the following points are established on the private network:

• Use TCP/IP as the only protocol bound to the NIC.
• No default gateway is configured.
• No DNS servers are configured unless the cluster nodes are DNS servers, in which case 127.0.0.1 should be configured.
• No DNS registration or DNS suffix is configured.
• No WINS servers are configured.
• Static IP addresses are used for all nodes.
• NetBIOS over TCP/IP is disabled.
• No NIC teaming is used, where two network interface cards are aggregated together to act as a single NIC card.

Public

For your public network, use at least two WINS or DNS servers on your cluster network segment or VLAN. While installing your cluster you will have to resolve cluster, DC (domain controller) and virtual server names. You must have a name server on your network for this. You can decrease the time required for a node to fail over by providing a name server on your network as well.

Use at least two DCs on your network. Clustering requires DCs not only during setup but also for normal functioning and failover.

If you use NIC teaming for greater bandwidth throughput and reliability, do not configure it while building the cluster. Add NIC teaming as a last step before final testing. Be prepared to “undo” NIC teaming as an early step in troubleshooting. Microsoft Customer Support Services (CSS) will likely direct you to disable teaming as a first diagnostic step, so be ready.

Both

Ensure that your network card settings are identical for every server in your cluster and that they are not configured to automatically detect network settings.

Software best practices

Ensure applications are cluster aware and will not lose work or fail to meet the SLA during a cluster failover.

Ensure transactions are as small as possible in your application and on any jobs that may run on your clustered SQL Servers. Long-running transactions increase the length of time required to apply the transaction log on the failover node and consequently increase the amount of time for failover.

Do not run antivirus software on cluster nodes. If you must run antivirus software, be sure the quorum disk and database files are excluded from the scans. Even in this configuration, there have been reports of antivirus drivers interfering with cluster disk resource failover. Test your setup and make sure it fails as expected. Select another antivirus product if yours causes problems.

Make sure there are no password expiration policies in use for any of the cluster-related accounts. Cluster accounts should:

• be the same for all nodes in the cluster;
• have domain accounts (but not domain admin accounts) and have local administrative rights on each node in the cluster. SQL Server 2005 forces you to set up domain-level groups for these accounts and then grants appropriate rights to the groups.
• have the least security privileges to minimize damage that could be done to the node or other servers on your network should the password be compromised or the account be hijacked by a buffer overflow.

Ensure all software components are the same version (i.e., SQL Server 2005 Standard), same architecture (i.e., 64 bit for all OS and SQL Server components) and at the same service pack and hot fix level. The exception is that individual SQL Server instances can be at different releases, editions and hotfix levels.

Ensure all external software dependencies (COM components, file paths, binaries) are either cluster aware or installed on all nodes in a cluster. MSDTC (Microsoft Distributed Transaction Coordinator) is the most common external dependency in a cluster. While it is not necessary, many people install it before installing SQL Server because installing it later is much harder.

When installing a cluster, consider installing a single-node cluster and adding nodes to the cluster as required. This way, if the cluster setup fails while adding a single node, you are left with a working cluster (although it could be a single-node cluster).

While applying hot fixes or service packs that require a system reboot, apply it to the primary (current instance host), fail over to the secondary, reboot the primary, fail back to the primary and reboot the secondary. Typically hot fixes and service packs are cluster aware and install on all cluster nodes simultaneously.

Hardware

Ensure that your cluster is approved by the vendor and that it is part of the Microsoft Windows Catalog with a specific endorsement for clustering.

Ensure each node in your cluster has identical hardware and components.

Regularly check vendor Web sites for potential hardware problems, fixes and BIOS patches for each component in your cluster.

Use the appropriate RAID technology to ensure that your disk array is fault tolerant. Be as proactive as possible in replacing failed or marginal disks. A disk failure will put a greater load on the remaining disks in an array and may cause other marginal disks to fail. Depending on your RAID technology, your RAID array may not be tolerant to more than one disk failure per array.

Ensure you have properly conditioned or charged batteries on any array controlle. It prevents data loss or corruption in the event of a power failure.

Use uninterrupted power supplies and be sure you have redundancy in your power supplies.

Use Hot-Add Memory if it’s supported by your SQL Server version, operating system and hardware. Hot-Add Memory is a hardware technology that allows you to add memory to a running system; the OS detects and uses the additional memory. Windows Server 2003, Enterprise and Data Center Editions, as well as SQL Server 2005 Enterprise Edition can take advantage of Hot-Add Memory. Read about Hot-Add Memory Support in Windows Server 2003.

Use ECC (Error Correction Code) memory chips, which store parity information used to reconstruct original data when errors are detected in data held in memory.

Use fault-tolerant NICs and network devices (switches).

Summary

Clustering is a relatively new technology and has a reputation for being fragile. SQL Server 2000 clustering is far simpler than the earlier versions and has proven to be much more reliable. Today, clustering on SQL Server 2000 and SQL Server 2005 is a highly reliable technology, but it still has many dependencies that prevent it from meeting your high-availability goals. Foremost among these dependencies is a staff that is trained and knowledgeable. Running a close second is having operating processes and procedures that are designed to work specifically with a SQL Server cluster. Ensure that you address all of your clustering dependencies to deliver high availability with SQL Server clustering.

Installing SQL Server 2008 on a Windows Server 2008 Cluster – Part1

There have been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008. It took quite a lot of effort for us to build a cluster in Windows Server 2003 – from making sure that the server hardware for all nodes are cluster-compatible to creating resource groups. Microsoft has redefined clustering with Windows Server 2008, making it simpler and easier to implement. Now that both SQL Server 2008 and Windows Server 2008 are out in the market for quite some time, it would be a must to prepare ourselves to be able to setup and deploy a clustered environment running both. Installing SQL Server on a stand-alone server or member server in the domain is pretty straight-forward. Dealing with clustering is a totally different story. The goal of this series of tips is to be able to help DBAs who may be charged with installing SQL Server on a Windows Server 2008 cluster.

Prepare the cluster nodes

I will be working on a 2-node cluster throughout the series and you can extend it by adding nodes later on. You can do these steps on a physical hardware or a virtual environment. I opted to do this on a virtual environment running VMWare. To start with, download and install a copy of the evaluation version of Windows Server 2008 Enterprise Edition. This is pretty straight-forward and does not even require any product key or activation. Evaluation period runs for 60 days and can be extended up to 240 days so you have more than enough time to play around with it. Just make sure that you select at least the Enterprise Edition during the installation process and have at least 12GB of disk space for your local disks. This is to make sure you have enough space for both Windows Server 2008 and the binaries for SQL Server 2008. A key thing to note here is that you should already have a domain on which to join these servers and that both have at least 2 network cards – one for the public network and the other for the heartbeat. Although you can run a cluster with a single network card, it isn’t recommend at all. I’ll lay out the details of the network configuration as we go along. After the installation, my recommendation is to immediately install .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 (the one for Windows Server 2008 x86 is named Windows6.0-KB942288-v2-x86.msu). These two are prerequisites for SQL Server 2008 and would speed up the installation process later on.

Carve out your shared disks

We had a lot of challenges in Windows Server 2003 when it comes to shared disks that we will use for our clusters. For one, the 2TB limit which has a lot to do with the master boot record (MBR) has been overcome by having the GUID Partition Table (GPT) support in Windows Server 2008. This allows you to have 16 Exabytes for a partition. Another has been the use of directly attached SCSI storage. This is no longer supported for Failover Clustering in Windows Server 2008. The only supported ones will be Serially Attached Storage (SAS), Fiber Channel and iSCSI. For this example, we will be using an iSCSI storage with the help of an iSCSI Software Initiator to connect to a software-based target. I am using StarWind’s iSCSI SAN to emulate a disk image that my cluster will use as shared disks. In preparation for running SQL Server 2008 on this cluster, I recommend creating at least 4 disks – one for the quorum disk, one for MSDTC, one for the SQL Server system databases and one for the user databases. Your quorum and MSDTC disks can be as small as 1GB, although Microsoft TechNet specifies a 512MB minimum for the quorum disk. If you decide to use iSCSI as your shared storage in a production environment, a dedicated network should be used so as to isolate it from all other network traffic. This also means having a dedicated network card on your cluster nodes to access the iSCSI storage.

Present your shared disks to the cluster nodes

Windows Server 2008 comes with iSCSI Initiator software that enables connection of a Windows host to an external iSCSI storage array using network adapters. This differs from previous versions of Microsoft Windows where you need to download and install this software prior to connecting to an iSCSI storage. You can launch the tool from Administrative Tools and select iSCSI Initiator.

To connect to the iSCSI target:

1. In the iSCSI Initiator Properties page, click on the Discovery tab.
2. Under the Target Portals section, click on the Add Portal button.
3. In the Add Target Portal dialog, enter the DNS name or IP address of your iSCSI Target and click OK. If you are hosting the target on another Windows host as an image file, make sure that you have your Windows Firewall configured to enable inbound traffic to port 3260. Otherwise, this should be okay.
4. Back in the iSCSI Initiator Properties page, click on the Targets tab. You should see a list of the iSCSI Targets that we have defined earlier
5. Select one of the targets and click on the Log on button.
6. In the Log On to Target dialog, select the Automatically restore this connection when the computer starts checkbox. Click OK.
7. Once you are done, you should see the status of the target change to Connected. Repeat this process for all the target disks we initially created on both of the servers that will become nodes of your cluster.

Once the targets have been defined using the iSCSI Initiator tool, you can now bring the disks online, initialize them, and create new volumes using the Server Manager console. I won’t go into much detail on this process as it is similar to how we used to do it in Windows Server 2003, except for the new management console. After the disks have been initialized and volumes created, you can try logging in to the other server and verify that you can see the disks there as well. You can rescan the disks if they haven’t yet appeared.

Adding Windows Server 2008 Application Server Role

Since we will be installing SQL Server 2008 later on, we will have to add the Application Server role on both of the nodes. A server role is a program that allows Windows Server 2008 to perform a specific function for multiple clients within a network. To add the Application Server role,

1. Open the Server Manager console and select Roles.
2. Click the Add Roles link.  This will run the Add Roles Wizard
3. In the Select Server Roles dialog box, select the Application Server checkbox. This will prompt you to add features required for Application Server role. Click Next.
4. In the Application Server dialog box, click Next.
5. In the Select Role Services dialog box, select Incoming Remote Transactions and Outgoing Remote Transactions checkboxes. These options will be used by MSDTC. Click Next
6. In the Confirm Installation Selections dialog box, click Install. This will go thru the process of installing the Application Server role
7. In the Installation Results dialog box, click Close. This completes the installation of the Application Server role on the first node. You will have to repeat this process for the other server

We have now gone thru the process of creating the cluster at this point. In the next tip in this series, we will go thru the process of installing the Failover Cluster feature, validating the nodes that will become a part of the cluster and creating the cluster itself. And that is just on the Windows side. Once we manage to create a working Windows Server 2008 cluster, that’s the only time we can proceed to install SQL Server 2008.