Archive for the ‘SQL 2008’ Category

Clustering SQL 2008 on Windows 2008 R2 what can go wrong

Ok this post is not only on how to setup a sql 2008 cluster but more on what can go wrong and more important how to solve this.

As much as I hate an installation that goes wrong and you spent day’s on how to solve this because you can ‘t bing your problem.

Here is what I have on hardware in this case a two node cluster windows 2008 R2 RC build 7100 and the SQL 2008 DVD

I have more than enough disk space in may case I have two disk of 100 GB but I will use only one disk per instance this is not Best Practice but handy in a test lab.

So I downloaded the SQL DVD and hit the setup.

clip_image002 clip_image004 clip_image006

I love the warning but as every one I do not read it and click run program 2x , finaly there is the setup splash.

So First I do the Prereq. This is nice this way you can see if the configuration you use is ok for the job.

clip_image008 clip_image010 clip_image012

Well I have a pending reboot and a .NET security warning , mmm strange I can’t connect to the Web .  first a reboot.

Since this is not tux I rebooted the kernel.

clip_image014  clip_image016 clip_image018

ok after the Reboot I hit setup again and I click the setup failover cluster , now there is no pending reboot here and I can go further

clip_image020 clip_image022 clip_image024

Remember that You must use the same product key on your Cluster nodes !!!!

Yes I agree who reads this any way I just wanna do a fast setup my boss want the sql cluster ready today. Yes I can.

clip_image026 clip_image028 clip_image030

Yes next next eh No MSDTC ? do I need it ? mmmm network binding error firewall error can’t the setup handle this for me ?

clip_image032 clip_image034

Troubleshoot the install errors:

Ok it seems is will not be a next next finish I agree setup Right. and this is whay you must read the guides!! except in a demo where things must go wrong.

So first we do a MSDTC setup this is needes if You wanna do Transactions

We also need to cluster the Microsoft Distributed Transaction Coordinator resource (MSDTC). Although MS DTC is not required for Database Engine-only installation, without it being clustered, you would receive a warning message during the setup configuration checks because of the incompliance of the Cluster_IsDTCClustered rule. If you need to install SSIS, Workstation Components or use distributed transactions, then MS DTC must be clustered. In our example, the MSDTC resource is clustered in a group called “Your name MSDTC Group”. This group contains the MSDTC resource, a shared Physical Disk T: to store MSDTC log files, a network name, and an IP Address. Before clustering MSDTC, network MSDTC access must be enabled on both nodes. For more information, please refer to The MSCS Cluster Service account will need to have public rights to SQL Server so that it can run SELECT @@servername for the IsAlive cluster checks.

Setup MSDTC :

This is the easy part Go in you FCM ( Failover Cluster Manger ) configure a service or application and select the MSDTC.

clip_image002[4] clip_image004[4] clip_image006[4]

So I give the MSDTC the IP and point it to the new disk for the log files. I use a separate disk for the MSDTC so that I can move the resource where and whan I want.

clip_image008[4] clip_image010[4] clip_image012[4]


Done I just created my MSDTC failover cluster resource !

Network binding Order :

clip_image032 This error is easy to fix and it is new I have never seen this error ( or I did always ther right steps ) so go to your network tab eh yes but where is it. well it take me almost a minute to find it.

Go to Control PanelNetwork and InternetNetwork Connections

click organize and layout and check the menu bar now you can go to advanced.

clip_image016[4] clip_image018[4] Change the order and done.

Back to the SQL backup :

So Again I swing the SQL setup

clip_image036 clip_image038 clip_image040

I named the first instance and changed the default

clip_image042 clip_image044 clip_image046

clip_image048 clip_image050 clip_image052

So whay would you use DHCP ?? just give it a fixed IP

clip_image054 clip_image056 clip_image058

I use the same service account for all the services added the administrator to the sql admins.  You should create a group and put users in i. but you can do this later.

clip_image060  What is this ?volume{

Ok this is strange I give the SQL server disk 4 , lets see what the problem is. Oh ok this is a CSV disk CSV = cluster shared volume.

You can’t use this disk!.

So I added a fresh new iscsi target to the nodes and run the setup again.

clip_image061 clip_image063 clip_image065

No errors

clip_image067  clip_image069 clip_image071

clip_image073 clip_image075 clip_image077

As in the settings above I filling the items and go.

clip_image079 clip_image081 clip_image083

Ok Now I have more disks to use and I do not choose a CSV volume , and no I do not wanna use DHCP

clip_image085 clip_image087 clip_image089

Same services account

clip_image091 clip_image093 clip_image095

Now as you can see I can see the disk letter this is great and almost done.

clip_image097 clip_image099 clip_image101

Yes I do believe in the error roporting this way we can make the product better.

clip_image103 clip_image105 clip_image107

No the filestream does not need an update I’m using Windows 2008 R2 RC state of the art server. I saved the config file maybe I do a command line setup later if I want to reinstall this cluster.

C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20090604_212627ConfigurationFile.ini

clip_image109 clip_image111

As you can see the ini file is easy to read and to adjust where needed.

clip_image113 clip_image115 clip_image117

What another error ;-( kerberos , Well this is a nice error bink it and you will see not much errors or resolution about this.

Did I miss something I solved all the other errors.

So what is it :


Controls whether or not the Network Name resource can be brought online without Kerberos authentication. The choices are:

RequireKerberos is set to 1. The next time this resource is brought online, clients using this resource will be authenticated using Kerberos. That is, a computer object for the Network Name resource will be created or updated before that resource comes online.

RequireKerberos is set to 0. Disables this option, that is, no computer object is created for the Network Name resource. This is the default. The Cluster service will try to detect if a computer object already exists for the Network Name resource; if a computer object is present, the resource will fail to come online. If this property were previously set to 1, setting it back to zero will disable the computer object created for this Network Name resource, if any.

Do not rename a Network Name resource and change the Kerberos authentication setting at the same time. Instead, allow time for changes to replicate after performing each action.

clip_image119 clip_image121 clip_image123

When I looked at my install I can’t see anything about a sql cluster only the network name. ah no ip settings.let me fix this.

clip_image125 clip_image127 clip_image129

Done fixed the IP and bring it online start the sql manager and go. NO  it won’t work. You know whay. YOU missed the error in the beginning.

SQL 2008 cluster this is saying USE SP1  my download came without sp1 and I can download sp1 but how to use it.

Well it is not easy, yes it is.

First copy the files from the DVD to a disk location.

use robocopy .

robocopy “sqlDVD” to “ disklocation” ( change the names !)

download the sp1 sql 2008 software details.aspx-FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

Extract the sp1 files to the hardisk loaction

SQLServer2008SP1-KB968369-x86-ENU.exe /x:v:SQLSERVER2008_FullSP1PCU
SQLServer2008SP1-KB968369-x64-ENU.exe /x:v:SQLSERVER2008_FullSP1PCU
SQLServer2008SP1-KB968369-ia64-ENU.exe /x:v:SQLSERVER2008_FullSP1PCU

Change the Setup files

robocopy SQLSERVER2008_FullSP1PCU v:SQLSERVER2008_FullSP1 setup.rll
robocopy SQLSERVER2008_FullSP1PCU v:SQLSERVER2008_FullSP1 setup.exe

robocopy v:SQLSERVER2008_FullSP1pcuia64 v:SQLSERVER2008_FullSP1ia64 /xf  Microsoft.SQL.Chainer.PackageData.dll
robocopy v:SQLSERVER2008_FullSP1pcux64 v:SQLSERVER2008_FullSP1x64 /xf  Microsoft.SQL.Chainer.PackageData.dll
robocopy v:SQLSERVER2008_FullSP1pcux86 v:SQLSERVER2008_FullSP1x86 /xf  Microsoft.SQL.Chainer.PackageData.dll

and merge the sp1 files.

And change the installation path in the DefaultSetup.ini located in the ia64,x64,x86


So I did the complete setup again but now with SP1 and I can login in the SQL manager

SQL 2008 cluster SQL 2008 cluster SQL 2008 cluster

Watch the servername instance you can see this in de FC manager netbios name and sql name.

Now that we are installed SQL on node 2 we must install SQL2008 SP1 on node 2.

clip_image002[6] clip_image004[6] clip_image006[6]

Just add the node to the cluster and follow the guide fillin the serial

clip_image008[6] clip_image010[6] clip_image012[6]

clip_image014[10] image SQL 2008 cluster

Fill in the password and next

image SQL 2008 cluster

I hope I showed you that alot can go wrong and a setup can always be tricky if you want to to this to fast.

I will mesh this configuration so that I hopefully have nice errors to solve.

In the next session I will add a second instance.

positive comments are welcome

Posted January 18, 2011 by Robert Smit in SQL 2008

Tagged with

Powershell MSDTC cluster add in

Teched 2010 Berlin #TEE10 #Teched

For Demo and learning Sites you need often thing that takes only a minute but when you play with it it takes more time than you want.

So with this in my mind I created 3 powershell scripts that you can use for builing a cluster , NLB Cluster and a MSTDC cluster resource.

So If you want to create a MSDTC resource in your Cluster you can do this in the GUI or with a Powershell Script.

You can find the Scripts Here : Powershell Scripts

Powershell MSDTC cluster add in

First We load the powershell modules If it is a cluster you should do this on all the Cluster Nodes

Servermanagercmd -install Application-Server
Servermanagercmd -install AS-Dist-Transaction AS-Ent-Services –allSubFeatures

I Comment every line for what it does.

# Create a new HA Server Role – Distributed Transaction Coordinator
Add-ClusterServerRole -Name ServerDTC -Storage "Cluster Disk 6" -StaticAddress

Here we create the resource and chuck it to a disk and IP

# Add the MSDTC Service to the new Server Role
Get-ClusterGroup ServerDTC | Add-ClusterResource -Name MSDTC-ServerDTC -ResourceType "Distributed Transaction Coordinator"

Put in the MSDTC services.

# Create Dependencies for the DTC group
Add-ClusterResourceDependency MSDTC-ServerDTC ServerDTC
Add-ClusterResourceDependency MSDTC-ServerDTC "Cluster Disk 6"
# Start DTC group
Start-ClusterGroup ServerDTC


So Now that this is ready you can configure the MSDTC but remember the items that I mentioned in my previous blog.

Microsoft SQL Server 2008 and SQL Server 2008 R2

guide for Microsoft SQL Server 2008 and SQL Server 2008 R2 The Infrastructure Planning and Design team has released an updated guide for Microsoft SQL Server 2008 and SQL Server 2008 R2.

The Infrastructure Planning and Design (IPD) guide for Microsoft SQL Server 2008 and SQL Server 2008 R2 helps organizations address key design decisions and confidently plan the efficient implementation of a SQL Server infrastructure. The tasks addressed in this guide help technical decision makers identify the appropriate SQL Server roles needed as well as determine the infrastructure components, server placement, and fault-tolerance configuration necessary to meet planning requirements. By using this guidance, organizations can make efficient planning and optimal design decisions for their SQL Server infrastructures.

SQL Server architecture

Download the IPD Guide for Microsoft SQL Server 2008 and SQL Server 2008 R2 at







What’s New in Microsoft SQL Server 2008 R2

Built on SQL Server 2008, SQL Server 2008 R2 has expanded reporting and analytics through self-service business intelligence.

• Master Data Services. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time.

•Improved application and multi-server management capabilities.

•The SQL Server Utility allows DBAs to centrally manage and view instances of SQL Server, data-tier applications, database files, and volumes.

•The Utility Control Point (UCP) collects configuration and performance information from managed instances of SQL Server every 15 minutes, and provides dashboard views of health summary of SQL Server resources.

•Data-tier applications (DAC), which contain all of the database’s schema, dependent objects, and deployment requirements used by an application to improve the deployment of data applications and the collaboration between data-tier developers and DBAs.

•Utility Explorer dashboards to monitor resource utilization and health states.

•Two new premium editions. SQL Server 2008 R2 introduces two new premium editions to meet the needs of large-scale datacenters and data warehouses: Datacenter and Parallel Data Warehouse.

•Integration with Microsoft SQL Azure. The client tools included with Microsoft SQL Server 2008 R2 allows DBAs to connect to SQL Azure, a cloud-based service that offers a flexible and fully relational database solution in the cloud.

•Integration of SQL Server with Sysprep. Allows DBAs to automate the deployment of SQL Server.

•Analysis Services integration with SharePoint. SQL Server PowerPivot for SharePoint is a new role-based installation option in which PowerPivot for SharePoint will be installed on a new or an existing SharePoint 2010 server to support PowerPivot data access in the farm.

•See for more detailed information on features in specific versions of SQL Server 2008 and for SQL Server 2008 R2.

Determine capacity and performance requirements


•Disk storage required. For databases that don’t yet exist, an estimate will need to be made of the disk storage required. Storage needs should be calculated for the database, transaction log, indexes, and tempdb database.

••IOPS and throughput required. Since the main function of SQL Server is to manipulate data, and that data resides either in memory or on the I/O subsystem, any I/O performance problems will result in performance degradation of SQL Server. Although it may not be possible to calculate the required IOPS in advance, benchmarks for some workloads may be available from SAN and disk vendors that may provide a baseline for estimating the required performance and the disk storage configuration required to deliver that performance level.

More information about the IPD series:


The Infrastructure Planning and Design guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario. All guides share a common structure including:

  • Definition of the technical decision flow through the planning process.
  • Listing of decisions to be made and the commonly available options and considerations.
  • Relating the decisions and options to the business in terms of cost, complexity and other characteristics.
  • Framing decisions in terms of additional questions to the business to ensure a comprehensive alignment with the appropriate business landscape.
  • These guides complement product documentation by exposing and focusing on infrastructure design options.

Posted May 27, 2010 by Robert Smit in SQL 2008, SQL 2008 R2

Tagged with

  • Tag