SQL Server 2014 Create a New #SQL Server Failover #Cluster (Setup) in 5 minutes Source Files

When I started this blog post it was more a can I create a Fully cluster in 5 minutes and with 10 min extra a two node cluster and loaded with a two instance cluster. Well I could.

If I had better hardware SSD/fusionIO or other SMB 3.0 huge etc it would be much faster ( donations are Welcome Winking smile ) Joking

I posted the vid on youtube and the blog and it seams it is not as common as I thought. no next next Finish Deployment.

As you already know deployments are time eating preparations. But once you have it in place it rocks.

So I’ll place an update on the source files remember change the domain/user account server names

Old Source blog :

https://robertsmit.wordpress.com/2013/07/08/sql-server-2014-create-a-new-sql-server-failover-cluster-setup-in-5-minutes-sql2014-windows2012r2-wpc13/

Get the ini files here http://sdrv.ms/12dqaya ( logon with your Microsoft Passport )

Watch this new video I made http://youtu.be/UyqNY5JyE9k

In the source file there are Create SQL CSV Clustered instance and join other node to the instance.

With the create cluster name IP , bind ISCSI etc and one Extra SQL install with out CSV also in 3 steps.

All the Files are there. just as an sample on how to do this.

http://1drv.ms/1jgOXzk

Have Fun!

@clusterMVP

https://robertsmit.wordpress.com

Windows Server 2012 R2 with SQL Server 2014 Cluster installation in less than 15 minutes #winserv #Rocks #movie

SQL Server 2014 now Generally Available Build your Cluster #sql

Robert Smit MVP Blog

Just for the fun I recorded a movie on the installation of Windows Server 2012 R2 . I thought just a quick recording and done but as it turns out it took longer than I expected, I made a typo in the script or clicked the wrong script or forgot to clean out my netbios name. then you gona build failsafe items in the scripts and they will be shown as errors because some items are already be set and can’t set twice.

The basic Idea was record a quick movie of an unattended installation of a SQL cluster in a few minutes is this possible yes it cab be done in les than 15 minutes. installation of the OS , Cluster with full Storage validation and a two node SQL cluster based on normal disk and a two node cluster based on CSV.

Eh in 15 minutes two full…

View original post 249 more words

Windows Server 2012 R2 with SQL Server 2014 Failover Clustered Instance #Step-By-Step #AlwaysOn AvailabilityGroups What can go Wrong! Part 1

There are a lot of good blog post about how to setup your Availability group, in two blog post I will try to break the basic setup and will show you what you should not do in your production environment. Just because you can does not mean I should do this.

This blog post will also show you most common errors and how to fix them and where to find the errors, but in the end you will have a working two node cluster and one Availability group

First how to setup an Availability group to make things more complex there are multiple instances, see how they look Naming convention is really important when you do complex configurations. an typo is quickly made!

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a WSFC node, and the WSFC cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters.

AlwaysOn Availability Groups relies on the Windows Failover Clustering (WSFC) cluster to monitor and manage the current roles of the availability replicas that belong to a given availability group and to determine how a failover event affects the availability replicas. A WSFC resource group is created for every availability group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration.

Primary on an FCI with a replica on a different FCI

I have a lot of SQL instances and this all runs on a two node Cluster and not all instances are installed on both nodes to trick the installation and to show you the errors you can expect.

Enabling the AlwaysOn and you can see the Difference the new AG Wizard is not grayed out any more .

Starting the Wizard and on system Databases it wil not work AG will only work on your own DB !

You must make a full backup of your DB before you start ( this is always handy )

Now we can add a new replica Check the Server and as it is a Cluster you can not set it to automatic failover.

We do setup a Data share for the replication

Now that we have completed the wizard we do the validation and go for the finish.

An Error ? checking the location ?? eh what should the DB be on the same location ?? not all my SQL server Cluster are the same and are not using all the drive letters. and as I choose to do this on the same cluster ( not supported ) I can not give the other instance the same drive letter. but hat if I had an other cluster and even then I did not have the same Drive letter. Is there a wizard bypass some where. Wizards are nice If you have a default installation. If not Plan B.

TITLE: Microsoft SQL Server Management Studio
——————————

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————
ADDITIONAL INFORMATION:

The following folder locations do not exist on the server instance that hosts secondary replica MVPSQL201402\SQL2:
i:\MSSQL11.SQL001\MSSQL\DATA;
(Microsoft.SqlServer.Management.HadrTasks)

So placing this on a CSV SQL server 2014

Well if drive letters is an issue SQL Server 2014 can store the DB on a CSV so no more drive letters.

And It passed the Validation that is Great.

Great thinking but.. the CSV is connected to all SQL servers So the next error is logical.

Yes the DB is already there.. what now ?

Manually Creating an Availability group for a SQL Server 2014 FCI

This sounds great but where to start ? should I bing It ? Let Me Bing That For You!

Well I Create it with a SQL script ( I’m no SQL master ) So things can be different.

However you can run this in a SQL CMD but here I do this step by Step.

I have My SQL Availibility group name, My DB name,IP, Servers

CREATE AVAILABILITY GROUP SQL001AG04
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE AG04 –, …
REPLICA ON — primary:
N’MVPSQL201401\sql001′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5023′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
— secondary:
N’MVPSQL201402\SQL2′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201402.mvp.local:5022′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

ALTER AVAILABILITY GROUP SQL001AG04
ADD LISTENER N’MVPLST01′
(WITH IP ((N’10.255.255.69′, N’255.255.255.0′)), PORT=1433);

BACKUP DATABASE AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.bak’
WITH INIT, COPY_ONLY, COMPRESSION;

BACKUP LOG AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.trn’
WITH INIT, COMPRESSION;

Now we go to the replica server and run the script below.

—————- Run this on The Replica Server!!!!!!!

ALTER AVAILABILITY GROUP SQL001AG04 JOIN;

RESTORE DATABASE AG04 FROM DISK = ‘\\mvpfsw01\SQLAG04\AG04.bak’
WITH REPLACE, NORECOVERY, NOUNLOAD,
MOVE ‘AG04’ TO ‘E:\MSSQL11.SQL001\AG04.mdf’,
MOVE ‘AG04_log’ TO ‘E:\MSSQL11.SQL001\AG04_log.ldf’;

RESTORE LOG AG04 FROM DISK = ‘\\mvpfsw01\SQLAG04\AG04.trn’
WITH NORECOVERY, NOUNLOAD;

ALTER DATABASE AG04 SET HADR AVAILABILITY GROUP = SQL001AG04

But as You can see in the screen shot it is not working the secondary server is down.

the following error is showing : The connection to the primary replica is not active. The command cannot be processed.

Message
A connection timeout has occurred while attempting to establish a connection to availability replica ‘MVPSQL201401\sql001′ with id [F82BBD94-4F04-4B0A-8B75-28A0899F240C]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

Ok I did turnoff all the Firewall, checked the network set permissions now what.

Think : I have two cluster nodes both are using SQL in the script what do they have in common.

ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5022’

So changed it from 5022 to 5023 and it work like a charm

In the next post I will explain how to check this and how to change this Winking smile

SO basically it is better to use the script that the wizard well it depends For now in the demo environment running on two different disks it is better and It would be better if the wizard ask you about drive letters or storage locations.

But manually you have more control about the setup and if something fails you can fixit before you go further. But also you have to think about a lot of issues Winking smile

Next will be Part 2

More Errors and more fixes on SQL Server 2014 Failover Clustered Instance (FCI) with Step-By-Step #AlwaysOn Availability Groups #winserv #FCI

How to: Create a New #SQL 2012 SP1 Server Failover Cluster (Setup) multiple instances on windows 2012 Quick Guide #TEE13

SQL Server Failover Cluster Installation

The installation of a SQL cluster is easy There is a Menu Item Create new Failover Cluster. #ws2012 #SQLsp1 #sql2012 #cluster #fail #howto

Well I don’t use this option Winking smile Why not to easy eh yes but more important is something fails like wrong IP etc the SQL install is lost , you need to deinstall the whole SQL mesh, I like the advanced version and trust me it will be easy.

OR you can loop at the technet site to see how and when:

SQL Server Failover Cluster Installation

SQL Server 2012

To install a SQL Server failover cluster, you must create and configure a failover cluster instance by running SQL Server Setup.

http://technet.microsoft.com/en-us/library/hh231721.aspx

So how Do we start this installation. Well you can see my blog post :

https://robertsmit.wordpress.com/2012/03/27/how-to-create-a-new-sql-2012-server-failover-cluster-setup-multiple-instances-on-windows-server-2012/

This blog post covers all the screen shots but this post is more a part 2

What we need :

One ini file that setup the advanced cluster preparation.
one ini file that does advanced cluster completion ( if there is a failure it is always here )
2 available cluster disk (Data & Log )
SQL Service Accounts
IP number For the SQL Server
Netbios name for the SQL Server
Instance Name

Now that We have the tools lets start.

Just a brief guidance on how to get the INI files.

You can create the ini files based on all the options on teched or create on during the setup wizard.

In this sample I do the add Node to a SQL Server failover Cluster

Start the Wizard

Choose :

add Node to a SQL Server failover Cluster
advanced cluster preparation
advanced cluster completion

You need all 3 ini files and yes if you did choose the create new SQL failover cluster you need only 2.

But now I have More Control and I can deploy the SQL without adding them to the cluster. Like a pre-stage setup.

But for now I take the add Node to a SQL Server failover Cluster

After accepting the license and checked for updates You will get the installation screen.

As you can see I have Multiple SQL instances but currently only on one node, This is a 6 node Cluster and 3 nodes have only the advanced cluster preparation to hold futures SQL instances but I do not want them in my cluster config. Only step 2 will be needed Winking smile

Only one Instance at the time can be added and this is bad It would save you a lot of time. But Scheduled can be your life saver. ( ini file usage )

No more Use password for all the services,

In the Screen ready to install there is a small line

C:\program files \etc

this is the place your ini file is saved for installation. You can cancel the installation now and use the INI file.

SO grab the ini file and lets see if this is working on you cluster.

advanced cluster preparation

Go to the Command Prompt Or Powershell much easyer. or create a cmd file.

Mount the ISO to the server or VM or place it on the disk it is your choice.

So in this location is the SETUP.EXE

I placed my ini files on a folder sqlsetup and this will removed in my task ( SCCM, VMM etc)

d:\setup.exe /qs /CONFIGURATIONFILE="C:\SQLSetup\sqlconfig-1.ini" /AGTSVCPASSWORD="Password01" /ASSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /ISSVCPASSWORD="Password01" /RSSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

And yes my passwords are in clear text I change the passwords with the accounts when the SQL instance goes to production. Now I can use the same accounts in my scripts and passwords without showing them my real accounts.

I do like the

INDICATEPROGRESS and /QS You can see the progress and or errors.

advanced cluster completion

The second step is the cluster completion.

Use the INI file that is created by the setup with option advanced cluster completion !

Do not use the same ini file. the options are not the same.

So during the setup wizard we created the advanced cluster completion ini file

used it in the same CMD line.
d:\setup.exe /qs /CONFIGURATIONFILE="C:\SQLSetup\sqlconfig-2.ini" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

If all goes right You should see something like this.

And you can logon to the SQL manager.

Adjustments to the INI files

Yes some adjustments are needed to the ini files if you want them to use like I did.

First in every ini file there is the uimode quote them out and I used the /QS put it on Thrue

;UIMODE="Normal"

; Setup will display progress only, without any user interaction.

QUIETSIMPLE="True"

If you do not want to edit the ini files skip the lines in the command line.

And only one setup at the time. But you can run on multiple nodes step 1 if you want more instances ( can not use the same ini files adjustment is needed )

Multiple Instances and the Changes

For multiple instances you need to adjust the ini files

Advanced cluster preparation.ini

For a new instance change the line below in a new value / and or service accounts

;SQL Server 2012 Configuration File advanced cluster preparation
ACTION="PrepareFailoverCluster"

[OPTIONS]
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
INSTANCEID="SQL0002"
AGTSVCACCOUNT="MVP\Sql2012"
ASSVCACCOUNT="MVP\Sql2012"
SQLSVCACCOUNT="MVP\Sql2012"
RSSVCACCOUNT="NT Service\ReportServer$SQL0002"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$SQL0002"

Adjustments for advanced cluster completion.ini

For a new instance change the line below in a new value / and or service accounts use the same as in the advanced cluster preparation.

;SQL Server 2012 Configuration File advanced cluster completion
[OPTIONS]
ACTION="CompleteFailoverCluster"
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
FAILOVERCLUSTERDISKS="Cluster Disk 4" "Cluster Disk 5"
FAILOVERCLUSTERGROUP="SQL Server (SQL0002)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.255.255.28;Cluster Network 1;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="SQL02"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="h:\OLAP\Data"
ASLOGDIR="h:\OLAP\Log"
ASBACKUPDIR="h:\OLAP\Backup"
ASTEMPDIR="h:\OLAP\Temp"
ASCONFIGDIR="h:\OLAP\Config"
ASSYSADMINACCOUNTS="MVP\administrator"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSYSADMINACCOUNTS="MVP\administrator" "MVP\Sql2012" "MVP\sqlserver"
INSTALLSQLDATADIR="h:"
SQLBACKUPDIR="i:\MSSQL11.SQL0002\MSSQL\Backup"
SQLUSERDBLOGDIR="i:\MSSQL11.SQL0002\MSSQL\Data"
SQLTEMPDBLOGDIR="i:\MSSQL11.SQL0002\MSSQL\Data"

Add Node to a SQL Server failover Cluster

For a new instance change the line below in a new value / and or service accounts use the same as in the advanced cluster preparation.

;SQL Server 2012 Configuration File
[OPTIONS]
ACTION="AddNode"
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
FAILOVERCLUSTERGROUP="SQL Server (SQL0002)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.255.255.28;Cluster Network 1;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="SQL02"
AGTSVCACCOUNT="MVP\Sql2012"
ASSVCACCOUNT="MVP\Sql2012"
SQLSVCACCOUNT="MVP\Sql2012"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$SQL0002"

If you have multiple SQL cluster an easy and quick way is change the FAILOVERCLUSTERIPADDRESSES and FAILOVERCLUSTERNETWORKNAME and you have a new cluster that has the same installed base. Or reuse it on your DTAP.

Happy Clustering

How to: Create a Failover Cluster (Setup) Windows Server 2012 Failover Cluster (Setup)

To create a Windows server 2012 failover cluster you need just a few steps.

First we use the group creation to manage the servers easily. I name the Group as it would be my new SQL Cluster. I show you all the screens just so that you can find the options. Not every body find it easy to find the things that he has to do.

So we created the groups and we are adding the Failover cluster Roles to the nodes just right click and add roles and the wizard pops up choose the failover cluster tools do this for both nodes.

On top of the screen you can see on what node you install the Roles. Remember you don’t have to go to the node to install the roles you can do this in the server groups.

Or you can use Posh. there are a lot new powershell commands to find them it is easy you can use the * The above Gui things can be done with one line Posh.

Now there are a few steps you can do to deploy roles

Get-WindowsFeature *

Get-WindowsFeature Failover-Clustering

As you can see this is on the remote cluster node ! I’m logged on on node 1 and it runs also on node 2

install-WindowsFeature Failover-Clustering

Or do it in

When We want to create a new cluster we can do this also in Posh just one line

New-Cluster -Name "myfirstcluster" -Node mvpwin8-n1 -NoStorage -StaticAddress "10.255.255.73"

Or gui again I show you all the screens just to see how fast posh is

Remember the validation reports are important to have if you setup fails or you cluster is in trouble run validation and see where to problem is.

7 clicks further your cluster is ready. now we are adding disk to the cluster and configure the witness disk

First in powershell.

My cluster name is : cluster8

the Get-ClusterAvailableDisk -Cluster cluster8

List all available disk

Get-ClusterAvailableDisk -Cluster cluster8

Get-ClusterAvailableDisk -Cluster cluster8 |Add-ClusterDisk

this adds all the disks to the cluster

or in the Gui

Now that the disk are ready we configure the Quorum disk

on top of the cluster right click and configure quorum.

We have a two node cluster , use the recommended settings.

So all this in 3 lines powershell

install-WindowsFeature Failover-Clustering

Get-ClusterAvailableDisk -Cluster cluster8

New-Cluster -Name "myfirstcluster" -Node mvpwin8-n1 -NoStorage -StaticAddress "10.255.255.73"

Did you also see the little export line ? in the Roles installation field save it for later.

it looks like this.

<?xml version="1.0"?>

<Objs xmlns="http://schemas.microsoft.com/powershell/2004/04" Version="1.1.0.1"><Obj RefId="0"><TN RefId="0"><T>System.Collections.ObjectModel.Collection`1[[System.Management.Automation.PSObject, System.Management.Automation, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]</T><T>System.Object</T></TN><LST><Obj RefId="1"><TN RefId="1"><T>Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/ServerManager/ServerComponent_NET_Framework_Features</T><T>Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/ServerManager/MSFT_ServerManagerServerComponentDescriptor</T><T>Microsoft.Management.Infrastructure.CimInstance#ServerComponent_NET_Framework_Features</T><T>Microsoft.Management.Infrastructure.CimInstance#MSFT_ServerManagerServerComponentDescriptor</T><T>Microsoft.Management.Infrastructure.CimInstance</T><T>System.Object</T></TN><ToString>ServerComponent_NET_Framework_Features</ToString><Props><Nil N="PSComputerName"/></Props>

So if you want to reuse / import this file on a fresh new server it can.

Import Configuration settings in windows 2012 based on exported Configuration settings in windows 2012

Go to powershell

install-WindowsFeature –configurationfilepath \\server\configfiles\savedconfig.xml

And your done

hotfix that improves the performance of the "AlwaysOn Availability Group"

A hotfix that improves the performance of the "AlwaysOn Availability Group" feature in SQL Server 2012 is available for Windows Server 2008 R2

Hotfix Download Available
View and request hotfix downloads

Assume that you enable the AlwaysOn Availability Group feature in Microsoft SQL Server 2012 on a Windows Server 2008 R2-based failover cluster. However, when you use local and remote Availability Group database replicas, the failover time to the local replica takes longer than expected.
Note This issue occurs because of inter-node communication within the cluster.

How to: Change the Network Binding order for SQL 2012 Windows Server 8 Failover Cluster (Setup)

During the SQL 2012 Setup there is a warning that my binding order is not correct. It looks ok but it is not. there are a couple of KB items on technet. this is on Windows 8 server

Why this is appearing did I make a mistake ? lets see mm my lan nic is on top. this looks ok so why this error. lets look at the SQL log

Ok here it is the SQL setup log file is it wrong ? local area connection* 11 is on top ? what is this ? lets lookit up .

NetworkBindingFacet: Looking up network binding order.

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘Local Area Connection* 11’ Device: ‘\Device\{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}’ Domain: ” Adapter Id: ‘{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘LAN’ Device: ‘\Device\{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}’ Domain: ‘mvp.local’ Adapter Id: ‘{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘ISCSI’ Device: ‘\Device\{1F016AE2-DD00-4B0F-B80B-57173AF2F36E}’ Domain: ” Adapter Id: ‘{1F016AE2-DD00-4B0F-B80B-57173AF2F36E}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘Cluster’ Device: ‘\Device\{BA1F99C0-0D58-44AA-85E0-ADFB1EF2C422}’ Domain: ” Adapter Id: ‘{BA1F99C0-0D58-44AA-85E0-ADFB1EF2C422}’

I know what it is it is the hidden cluster adapter which needed to be moved in the binding order, and that had to be done in the registry.

I opened up regedit and drilled down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage and opened up the Bind value:

\Device\{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}

\Device\{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}

Flip the registry keys and you are ready to go.

This is hard to find eh yes but you can use powershell

wmic nicconfig get Description,SettingID

More info about this is on MSDN

http://msdn.microsoft.com/en-us/library/ms189910(v=sql.110).aspx

changing-the-network-binding-order-in-windows-server-2008

and it you want to change this by powershell the get-itemproperty is the way.

Get-ItemProperty Registry::HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\bind\*

After the NIC binding order change the SQL setup marked all as passed.

Samples :

Get-Item -Path Registry::HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\bind\* | Select-Object -ExpandProperty Property

Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | select Description, IPAddress, index, SettingID

gwmi Win32_NetworkAdapterConfiguration | where-object {$_.IPEnabled -eq "True"} | ft Description, SettingID -auto

How to: Create a New SQL 2012 Server Failover Cluster (Setup) multiple instances on windows server 2012 #ws2012

Now that windows 2012 is in beta we can talk almost about anything and it is time to create my demo SQL 2012 machine. In this case I uses the Windows server 8 cluster from the other blog item to create a SQL 2012 cluster with 3 instances. All of the instances are created on different ways , Instance 3 is a ZTI Winking smile #WS2012 #TEE #TEE13 #MVP13

For starters build a 2 node cluster or more nodes if you want and use the SQL 2012 Media. I use the Enterprise version.

Remember that I create the Server Group and my cluster I already added the disk to my cluster mounted the SQL 2012 ISO file to the node. Ready for the Setup.

again I use a lot of pictures in this blog and I do almost everything twice just to show you how you can configure your cluster.

the disk listing in the cluster. eh no we do not use the basic setup. Installation should be fun and not follow the wizard Winking smile ok we have to use the wizard in this part. We use the advanced cluster preparation setup this will setup your SQL cluster in two parts.

Fill in your SQL product key or use a evaluation , accept the agreement.

Mm a warning it looks OK yes it is I create a new blog post on this ignore it for now.

Yes I corrected the binding order and we continue the setup.

I choose to install all the options This cluster will be used for the System center products.

I use a basic account but for more security you should use different accounts. And I will also configure my report server by hand later.

Now that the first step is ready we can do the second step but first I want to create a second instance preparation. as a basic I use the setup .ini file that you can save during the setup.

The second instance I use this ini file to create the instance preparation. Open the ini file and I changed the SQL01 in SQL02. easy

in the setup screen I use the install based on a Ini file setup. this is not an unattended setup. But all the screens are already filled in. So this is like a practice unattended setup like in SQL03 Winking smile

… import ini file As you can see the installation of a second instance is the same as installing one instance on a node or installing a third instance but you can install less or more options in the instances. they don’t have to be the same.

Easy just press next and finish almost unattended. You can also use the ini file of this installation if you want to rebuild this instance during a disaster.

Advanced Cluster Completion

Now that the Instance SQL01 and SQL02 are installed but not jet completed in my cluster we need to do the next step Cluster completion. This step we need to do this also twice , and yes one time I do this in the GUI and save the INI file and One I use the Ini file for the SQL02 setup just to see how it can be done.

Here we create the Cluster name and the SQL instance As you can see. And You see also the SQL02 instance. but remember you can only do one instance installation at the time

We choose the Disks and the IP address for this SQL cluster instance.

I use here only two disks but you can use more disk, DB ,Logs, and Temp DB and Analysis all on different disks. if you want more performance. this is if it runs on multiple SAN disks.

for System center the Collation is most important If you choose the wrong one you are in trouble it is hard to reconfigure this.

If you continue Setup using the default collation (SQL_Latin1_General_CP1_CI_AS), you will not be able to support multiple languages in Service Manager. In the future, if you decide you want to support multiple languages, you will have to re-install SQL Server.

Service Manager doesn’t like the default collation method (SQL_Latin1_General_CP1_CI_AS). If your SCSM SQL server is running with that collation mode you will get a warning when you install SCSM.

Further down in the error the Exhange 2010 MP was referenced with regards to “Microsoft.Exchange.2010.Reports.Dataset.Ten

After doing some investigation discovered that nager is only supported with a collation of “SQL_Latin1_General_CP1_CI_AS” hange 2010 management pack relies on the collation being “SQL_Latin1_General_CP1_CI_AS” to place certain data into the Data Warehouse.

Here is the bad news!!! The only way to resolve is it re-install SQL. So make sure if you are installing Operatons Manager your SQL collation is correct.

This error message is because we installed SQL Server in the default SQL_Latin1_General_CP1_CI_AS collation. This collation is the only one that is supported by SCOM2012 and so this is necessary when we want to install SCOM2012 and want to connect SCSM with SCOM2012.

When installing SCSM 2012 it’s important to use a supported language.

If you use the default collation: SQL_Latin1_General_CP1_CI_AS

multilanguage support will not be available in Service Manager.

Instead you should use a supported collation like eg.: Latin1_General_100_CI_AS

More info about this you can find here :

http://technet.microsoft.com/en-us/library/hh495583.aspx

If you use system accounts you don’t want to add the domain administrator but in my demo It is ok

Ready. the one thing that I did not install is a DTC because it is so easy to install on a cluster we do this in just a few steps. go to the failover cluster manager and add a cluster role

pick the msdtc and give the role a IP and I always use a own disk for the DTC but you can share this if you want. my advice use a own disk for a more flexible cluster

as you can see my cluster is ready to use

the second cluster completion I do this with a INI file all the steps are the same as without the ini file only the items are filled in.

..import ini file

I skiped the other screens

Done Now I have two SQL instances and 2 sets of ini files. these file I’m gonna use it for an unattended setup. SQL installation in just 10 minutes Winking smile

the first step SQL cluster preperation

setup.exe /Q /CONFIGURATIONFILE=”C:\SQLSetup\ConfigurationFile.ini”

/SQLSVCPASSWORD=”<StrongPassword>” /IACCEPTSQLSERVERLICENSETERMS/INDICATEPROGRESS

because I use a account for my SQL server I need to fill in my password.

this will kick in the cluster completion.

For this unattended setup I created the following file I use the following account for all the services : sql2012 and the password is also sql2012 Remember this is a lap use strong passwords in real life !!!

Step one is the Cluster preperation

d:\setup.exe /q /CONFIGURATIONFILE=”C:\SQLSetup\SQL03-1.ini”

/AGTSVCPASSWORD=”sql2012″

/ASSVCPASSWORD=”sql2012″

/SQLSVCPASSWORD=”sql2012″

/ISSVCPASSWORD=”sql2012″

/RSSVCPASSWORD=”sql2012″ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

Step Two is the Cluster Completion

d:\setup.exe /q /CONFIGURATIONFILE=”C:\SQLSetup\SQL03-2.ini”

/AGTSVCPASSWORD=”sql2012″

/ASSVCPASSWORD=”sql2012″

/SQLSVCPASSWORD=”sql2012″

/ISSVCPASSWORD=”sql2012″

/RSSVCPASSWORD=”sql2012″ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

You can do this also in one ini file but if something fails you don’t have to do it all again.

Happy clustering !!

Windows 2008R2 MSTDC Clustering how hard can it be. Linked SQL Servers

The DTC option is a simple feature just turn it on and it works eh yeh sometimes. but in advanced environments you need often some tweaks and how to’s

The following errors are common on misconfiguration on the SQL Cluster / MSDTC source

MSDTC encountered an error (HR=0x80000171) Or if you are using linked SQL servers If the MSDTC is not working most of them solve the error with code, actually it is a misconfiguration of the MSDTC How to solve this error ?

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Or error 7391

Well here is a little tip on how to solve this.

Typical MSDTC cluster resource , But in the resource kit there is a little tool that is named SUBINACL

SubInACL is a command-line tool that enables administrators to obtain security information about files, registry keys, and services, and transfer this information from user to user, from local or global group to group, and from domain to domain. For example, if a user has moved from one domain (DomainA) to another (DomainB), the administrator can replace DomainA\User with DomainB\User in the security information for the user’s files. This gives the user access to the same files from the new domain.
SubInACL enables administrators to do the following:

Display security information associated with files, registry keys, or services. This information includes owner, group, permission access control list (ACL), discretionary ACL (DACL), and system ACL (SACL).
Change the owner of an object.
Replace the security information for one identifier (account, group, well-known security identifier (SID)) with that of another identifier.
Migrate security information about objects. This is useful if you have reorganized a network’s domains and need to migrate the security information for files from one domain to another.

This update addresses the following issues:

Fixed bug where subinacl.exe failed to process command line arguments
Fixed bug where subinacl.exe failed to function correctly with cluster file shares

So now that we have the tool let us use this filling in the following command

subinacl /service msdtc /grand=”network services”=qsetil

Access denied ! , I am an Admin the CMD is a Admin why the syntax is correct .

Yes all above is ok except the MSDTC part it is a clusterd so you need to use the cluster part.

Ok and now with the Clustered DTC Winking smile

Ok done now the error below is gone.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Msg 7395, Level 16, State 2, Line 3

But what about the XACT_ABORT option was set to OFF and the error 7391 ?

Yes easy to solve turn it on.

Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction.
Issue this statement before you run your query:
```
SET XACT_ABORT ON
```
The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.
Check whether any of the servers are on a cluster. The DTC on the cluster must have its own IP address. You must verify proper name resolution of the DTC service on each server. The IP address of the DTC must be defined in your name resolution system (such as WINS, DNS or LMHosts). Verify that each server can communicate with the other’s MSDTC by name, not just by IP address. Check in both directions. For example, check from server A to server B’s MSDTC, and then check from server B to server A’s MSDTC. You must resolve all name resolution problems on the network before you run your distributed query.

Below is a little example of how to test your transactions just change your servername [SQL instance name].

BEGIN TRAN

UPDATE [SQL instance name].test_DTC.dbo.Table1

SET Field1 = ‘MSDTC is a pain if it is not working but now it is working like a charm’

Where Field1 = ‘Second’;

COMMIT TRAN

SET XACT_ABORT OFF

SET XACT_ABORT ON

Select * from [SQL instance name].test_DTC.dbo.Table1

What can go Wrong during the SQL 11 upgrade ( Beta SQL 11 Denali )

Now that there is a beta of SQL 11 ( denali ) I want to see if I can break my Cluster with SQL 2008 on it during the upgrade. Yes I can.

<>>Extra

These are testing machines ( snapshot of production ) and the screenshot are showing you what can go wrong. It was hard to make a blog post about this there was so much to fix and to do and to explane how to solve things. At the end I deside to create a fresh cluster ;-(

<>>

I use a 2 node cluster windows 2008r2 with SQL 2008 on it 2 named sql instances.

Lesson 1 be prepared and no rush during de upgrade.

Lesson 2 Make sure your current SQL cluster is fine if not do not upgrade !

In my post https://robertsmit.wordpress.com/2009/06/18/clustering-sql-2008-on-windows-2008-r2-what-can-go-wrong/ and I guessed it would be fun to do this with SQL Denali.

Well It is fun but the things I want to fix and to make sure the I can fix it and write a blog about this was not easy.

Well this blog is holding a lot of screen shots to see what and how the installation can be done.

So my 2 node cluster that I used for the SQL 2008 what can go wrong is up and running and ready for the beta sql 11.

I downloaded the SQL denali beta mounted the ISO in my hyper-V and started the splash.

But first let me test this on a brand new cluster and see how things are going.

Except I use the Same IP for the SQL instance ( typo on perpes )

Ok I needed a reboot package after the reboot I’m ready to go

Fill in your key or leave it this way ( give your key later )

Give the file location and name the instance

I give the disk and the IP number ( make sure it is a free IP )

Put in the service account

Some new screens

Well All set and ready to go But during the install there was this error, What is it and why ?

Well It seams that I used a Ip adress that was not Free but why this error and why not installing and leave the resource off line. So what is the status of the install.

Well there is no cluster good that this was not the production SQL so lets remove it an reinstall

Can’t find the SQL , yes the setup did not install it but why ?

But on more error messages to go there is no clean fix and this is just for not putting in a right Ip adress. next step clean out the register and delete the files, and rerun the setup again.

After playing with denali and upgrading , deinstall , install, fresh install , Win2008R2 Sp1

If you are beta testing denali on a cluster do not use the setup for cluster node install but choose the prepare for cluster install and after that use the finish cluster install. this works fine and fast.

Both my test clusters are broken during the upgrade It is all my fault I did this and not all the errors are SQL denali problems. but the main problem is make sure you have a plan and if something goes wrong make sure you can fixit or do a rollback.

In my case the only thing I did was a taken IP address and running an instance that is clustered and running a non clustered instance on both nodes. seems not logical at all. But have you seen crappy sql clusters ? Or are all your Clusters in excellent shape ?

dumb questions do not exist, but have you ever worked on a helpdesk ?

http://www.rinkworks.com/stupid/cs_calls.shtml

Windows Live Tags: Wrong,Beta,Denali,Cluster,node,Lesson,Make,installation,Except,Same,instance,typo,Fill,Give,location,disk,account,Some,error,Free,resource,status,production,setup,messages,files,Both,problems,instances,seams,errors,blog,reboot
WordPress Tags: Wrong,Beta,Denali,Cluster,node,Lesson,Make,installation,Except,Same,instance,typo,Fill,Give,location,disk,account,Some,error,Free,resource,status,production,setup,messages,files,Both,problems,instances,seams,errors,blog,reboot

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.

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.

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.

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

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.

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

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 http://support.microsoft.com/kb/817064/. 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.

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.

Done I just created my MSDTC failover cluster resource !

Network binding Order :

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.

Change the order and done.

Back to the SQL backup :

So Again I swing the SQL setup

I named the first instance and changed the default

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

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.

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.

No errors

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

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

Same services account

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

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

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

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

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 :

RequireKerberos

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.

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.

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.

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

PCUSOURCE=”z:SQLSERVER2008_FullSP1PCU”

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

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.

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

Fill in the password and next

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

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 192.168.1.1

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.

https://robertsmit.wordpress.com/2010/10/20/msdtc-clustering-logfile-bug/

Microsoft SQL Server 2008 SP2 Release Notes

What’s New in Service Pack 2

The following features are new in Service Pack 2 (SP2).

SQL Server Utility. After you apply SP2, an instance of the SQL Server 2008 Database Engine can be enrolled with a utility control point as a managed instance of SQL Server. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.
Data-tier Application (DAC). Instances of the SQL Server 2008 Database Engine support all DAC operations after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services SharePoint integration. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in for SharePoint 2007 products. The new add-in supports the integration of SharePoint 2007 products with SQL Server 2008 R2 report servers. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What’s New (Reporting Services).

To download the SQL Server 2008 SP2 release please select from the links below:

SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

WordPress Tags: Microsoft,Server,Release,Notes,Beta,Service,Pack,features,instance,Database,Engine,Overview,Online,Data,tier,Application,DACs,client,tools,Management,Studio,objects,unit,Services,SharePoint,Mode,integration,products,supports,links,LinkId,Feature,Instances,operations,servers,fwlink

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.

Download the IPD Guide for Microsoft SQL Server 2008 and SQL Server 2008 R2 at http://go.microsoft.com/fwlink/?LinkId=160982

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 http://msdn.microsoft.com/en-us/library/cc645993.aspx for more detailed information on features in specific versions of SQL Server 2008 and http://msdn.microsoft.com/en-us/library/cc645993(SQL.105).aspx 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.