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 image  Create SQL CSV Clustered instance and join other node to the instance.

 

image 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!

 

SQL Server 2014 Failover Clustered Instance (FCI)

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

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groups image

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.

image image imageimage

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

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groupsimageimage

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

image 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.

image image

 

 

We do setup a Data share for the replication

image

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

image

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.

image

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.

image

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

 

image

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;

image

 

 

 

 

 

 

 

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 thisWinking smile

 

image

 

 

 

 

 

 

 

 

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 :

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

image

 

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 :

  1. add Node to a SQL Server failover Cluster
  2. advanced cluster preparation
  3. 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

clip_image002

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

clip_image004 clip_image006

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 )

clip_image008 clip_image010

No more Use password for all the services,

clip_image012 clip_image014

clip_image016  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.

clip_image018

 

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.

image

And you can logon to the SQL manager.

image

 

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 )

clip_image018

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.

clip_image002 clip_image006

clip_image008 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.

 clip_image016  clip_image018 

clip_image020 clip_image022

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

clip_image024  clip_image026

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

clip_image028 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

clip_image030 clip_image032

clip_image034 clip_image036

clip_image038

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.

clip_image040 clip_image042

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

clip_image044 First in powershell.

My cluster name is : cluster8

the Get-ClusterAvailableDisk -Cluster cluster8

List all available disk

image

Get-ClusterAvailableDisk -Cluster cluster8

Get-ClusterAvailableDisk -Cluster cluster8 |Add-ClusterDisk

this adds all the disks to the cluster

or in the Gui

clip_image046  Now that the disk are ready we configure the Quorum disk

on top of the cluster right click and configure quorum.

image clip_image050

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

clip_image052 clip_image054

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.

image 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 is available
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

clip_image002 clip_image004

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

clip_image006 clip_image008

clip_image010

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:

clip_image012 clip_image014

 

\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

clip_image016

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\*

clip_image020

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 image and my cluster image 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.

clip_image002 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.

clip_image004 clip_image006

clip_image008 clip_image010

image clip_image014

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

clip_image016 clip_image018 

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

clip_image020 clip_image034

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

clip_image036 clip_image038

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

clip_image040 clip_image042

clip_image044 clip_image046

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.

clip_image048 clip_image050

clip_image051 clip_image053

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.

image

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

image 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.

clip_image002[6] clip_image004[6]

clip_image006[6] clip_image008[6]

clip_image010[6] clip_image012[6]

clip_image014[6] clip_image016[6]

clip_image018[6] clip_image020[6]

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.

clip_image003 clip_image005

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

clip_image007 clip_image009

clip_image011 clip_image013

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.

clip_image015 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

 

clip_image018[8]clip_image022[4]

More info about this you can find here :

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

clip_image024[4] clip_image026[4]

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

clip_image028[4] clip_image030[4]

clip_image032[4] clip_image034[4]

clip_image036[4]

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

clip_image038[4] clip_image040[4]

clip_image042[4]  clip_image044[4]

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

clip_image046[4] clip_image048[4]

clip_image050[4] 

as you can see my cluster is ready to use

clip_image056 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.

clip_image058..import ini file

clip_image060 clip_image062

clip_image064 clip_image066

I skiped the other screens

clip_image076 clip_image078

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.

clip_image080

clip_image082 clip_image084

clip_image088 clip_image090

clip_image092 clip_image093

clip_image095 clip_image097 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.

 

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

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

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

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.

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

Ok and now with the Clustered DTC Winking smile 

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

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

 

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

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.image

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 )

clip_image002 clip_image004 clip_image006 clip_image008 

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

clip_image016clip_image018 

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

clip_image022 clip_image024

clip_image026 clip_image028

Give the file location and name the instance

clip_image030 clip_image032

clip_image034

clip_image036 clip_image038

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

clip_image040 clip_image042 

Put in the service account

clip_image044 clip_image046

Some new screens

clip_image048 clip_image050

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

clip_image054  clip_image058

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

clip_image056 clip_image062

clip_image064 clip_image066

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

clip_image068 clip_image070 clip_image072

clip_image074 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.

image

 

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.

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 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.

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]

clip_image014[8]

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 :

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.

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

PCUSOURCE=”z:SQLSERVER2008_FullSP1PCU”

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

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

Home

 

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:

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

    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 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:

    image

    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.

    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 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:

    image

    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.