GAPTHEGURU

Geek with special skills

How to: Apply a service pack or hotfix SQL Server 2008 to a failover cluster instance

Installing Service Pack SQL Server 2008 in failover cluster is very different than the SQL Server 2005 cluster failover.

With SQL Server 2005, when you start installing cluster service pack (or hotfix), it must be launched on the active node (node that hosts the instance). When installing the Setup will launch simultaneously  “remote silence” on all passive nodes. All nodes in the cluster containing the SQL Server instance are updated in the same time.

With SQL Server 2008, to reduce the downtime, we have revised the method of deployment. Now if you want to apply a service pack (or hotfix), you must install in first on the passive nodes. The passive nodes are updated before the active node.

Therefore, for your instance SQL Server 2008  in failover cluster, you must follow the scenario below for the application of Service Pack, Cumulative Update or Hotfix :

1.  Apply the hotfix on pasive node N2
2.  Reboot the passive node N2
3.  Failover on SQL resource : the passive node become the active node
4.  Apply the hotfix on the passive node N1
5.  Reboot the passive node N1

Advertisements

08/10/2012 Posted by | Sql Server | , | Leave a comment

Rebuild MSDB Database

If you accidentally delete the transaction log file of msdb database on a newly installed SQL Server 2008 R2 instance do the following steps

I searched Books Online and found this article about Rebuilding System Databases, which helps in rebuild the msdb database.

Steps to Follows

  1. Stop all the SQL Server services & start the command prompt with elevated administrative privilege & execute the following command:
    NET START MSSQLSERVER /T3608
  2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
    SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
  3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
    SQLCMD -E -S DBS03 -i"E:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"E:\instmsdb.out"
  4. Review the instmsdb.out for any errors and re-apply the service packs.
  5. Stop the SQL Server.
  6. Start the SQL Server normally

StepstoFollow

Since I was able to connect to the instance without any error, I stopped the SQL Server instance and copy all the system databases files. Later I restarted the SQL Server Agent and the instance was online.

Hope, this may help someone, Happy Learning Smile

06/19/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , | Leave a comment

SSMS Timeout Expired Error When Making Changes To a Table

Problem

I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn’t think it would have a timeout issue.  In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.

Solution

To simulate the timeout error, let’s create a table with 500,000 rows. We will use this for the examples.

-- Create the table for demo
IF EXISTS (SELECT * FROM sys.objects where name = 'TestingTimeOut')
DROP TABLE TestingTimeOut
GO
create table TestingTimeOut (EmpName varchar(75), Designation varchar(50), Department varchar(50))
GO

-- Populate the table with 500000 rows 
-- Multiple executions by providing number with GO is not ANSI standard 
-- Should not be used in production environment
INSERT INTO TestingTimeOut VALUES ('Atif Shehzad', 'DBA', 'Human Resource')
GO 500000

Transaction timeout for SSMS Designer operates only when SQL Server object modification is performed through the SSMS Designer. It is not a server level parameter and may be viewed or modified in the ‘Designers’ link in the Options menu of SSMS. Its value can be different for each SSMS client and is not stored as a server level configuration.

To check or change this setting, within SSMS go to Tools > Options and click on the Designers link in the left panel as shown below.

Time-out for SSMS designers

The default value is 30 seconds, but for the timeout simulation let’s change it to 1 second and save the changes.

In SSMS, find the table that we just created, right click and select Desgin.  Add an integer type column EmpID as a primary key with an identity property and try to save the modifications. Since we changed the timeout to 1 second, this should cause a timeout error as shown below. If the error is not generated you may need to increase the number of rows in the demo table and try again. (Note if you get this error “Saving changes is not permitted” then check out this tip.)

ssms designer timeout designer expired for operation

This is the T-SQL that SSMS generates and is trying to execute.  We can see here that it is just doing an ALTER TABLE with the ADD option.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestingTimeOut ADD
 EmpID int NOT NULL IDENTITY (1, 1)
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

If we try to add the EmpID as the first column, as shown below we can see that SSMS generates a pre-save warning that this will take some time. .

This is the T-SQL that SSMS generates and we can see that this is a much more intensive script that drops and recreates the table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestingTimeOut
 (
 EmpID int NOT NULL IDENTITY (1, 1),
 EmpName varchar(75) NULL,
 Designation varchar(50) NULL,
 Department varchar(50) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TestingTimeOut OFF
GO
IF EXISTS(SELECT * FROM dbo.TestingTimeOut)
  EXEC('INSERT INTO dbo.Tmp_TestingTimeOut (EmpName, Designation, Department)
  SELECT EmpName, Designation, Department FROM dbo.TestingTimeOut WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestingTimeOut
GO
EXECUTE sp_rename N'dbo.Tmp_TestingTimeOut', N'TestingTimeOut', 'OBJECT' 
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

Summary

So if you face these timeout issues when using SSMS to make changes you now know the exact parameter to manipulate. The solution to this problem is to increase the timeout limit for Designer. The specified limit would be implemented for any sever connected via that SSMS connection.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , | Leave a comment

Restore Database From SQL Server 2008 to SQL Server 2005 Part 1 – 3

PART 1:

Problem

When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will see some error messages as the examples below.

Backup and Restore

You have backup a database from SQL Server 2008. If you try to restore the backup database file to SQL Server 2005, you will receive the error message:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
-> The media family on device ‘the backup file‘ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Restore Database Error Message

Detach and Attach

You have detach a database from SQL Server 2008. If you try to attach the detached database file to SQL Server 2005, you will receive the error message:

Attach database failed for Server ‘SQL Server name’. (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement batch. (Microsoft.SqlServer.ConnectionInfo)
–> The database ‘database name’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database ‘database name’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

Attach Database Error Message

Solution

These problems occur because a backup or detach database file is not backward compatible. You cannot restore or attach a database which is created from a higher version of SQL Server to a lower version of SQL Server.

But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.

  1. Part 2: Generate SQL Server Scripts Wizard. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
    • If the source database contains lots of data, you will have a large script file.
    • The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
  2. Part 3: Import and Export Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

————————————————————————————————————————————————————————————————————————————————————————————

PART 2: Generate SQL Server Scripts Wizard

Now let’s see a first solution to solve the problems.

On this post, you see how to backup ‘Northwind’ database by generate a SQL Server script on SQL Server 2008. Then, restore the ‘Northwind’ database by execute the SQL Server script on SQL Server 2005.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to the SQL Server 2008. Right-click on the database that you want to backup and select Tasks -> Generate Scripts.
    Generate Scripts
  2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
    Welcome to the Generate SQL Server Scripts Wizard
  3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
    Select Database
  4. On Choose Script Options, set Script Database Create to False and Script for Server Version to SQL Server 2005.
    Note: You can set Script Database Create to True if your source and destination for store database files are the same location.
    Script for Server Version to SQL Server 2005
  5. Continue on Choose Script Options, scroll down and set Script Data to True. Click Next
    Note: Set this option to true to include data on each table to a script.
    Script Data to True
  6. On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
    Select Script Output Destination
  7. On Script Wizard Summary, you can review your selections. Then, click Finish.
    Script Wizard Summary
  8. On Generate Script Progress, the wizard is creating a SQL Server script.
    Generate Script Progress
  9. When the script has been completed, you see the output file as similar the figure below.
    The Generated Script
  10. Connect to SQL Server 2005, create a new database. Right-click Database -> New Database.
    Note: If you have set Script Database Create to True on step 4, you don’t have to create a database manually.
    Create New Database
  11. Type ‘Northwind’ as database name. Click OK.
    Create Database Northwind
  12. Execute the SQL Server script file that you have created.
    Execute the script
  13. Now the database ‘Northwind’ is restored on SQL Server 2005.
    Northwind Database

———————————————————————————————————————————————————————————-

PART 3: Export Data Wizard

On this post, you see how to export tables on ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 using export data wizard.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to SQL Server 2008. Right-click on the database that you want to export data -> select Tasks -> Export Data.
    Export Data
  2. On Welcome to SQL Server Import and Export Wizard, click Next.
    Welcome to SQL Server Import and Export Wizard
  3. On Choose a Data Source, select the source from which to copy data. Set Data source to SQL Server Native Client 10.0. Verify that Server name is the source of SQL Server 2008 that you want and select Database as ‘Northwind’. Click Next.
    Choose a Data Source
  4. On Choose a Destination, specify where to copy data to. Set Destination to SQL Server Native Client 10.0. Type the Server name to the destination of SQL Server 2005 that you want. You can also click Refresh to verify if you can connect to the specify server name. Currently, I don’t have ‘Northwind’ database on SQL Server 2005 so I will create a new one, click New.
    Choose a Destination
  5. On Create Database, type name as ‘Northwind’ and click OK.
    Create Database
  6. Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
    Choose a Destination
  7. On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  8. On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
    Select Sources Tables and Views
  9. On Save and Run Package, click Next.
    Save and Run Package
  10. On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
    Complete the Wizard
  11. Wait until the wizard finishes execution.
    Export Data Successful
  12. Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.
    The Exported Tables

04/24/2012 Posted by | Sql Server, T-SQL | , , | 2 Comments

Upgrading SQL Server databases and changing compatibility levels

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem
When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server.  From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed.  This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.

Solution
The first thing that you need to do is to check the compatibility level that your database is running under.  As mentioned above any database that is upgraded using the backup and restore or detach and attach method will not change the compatibly level automatically, so you will need to check each database and make the change.

Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.  Here is a list of the compatibly levels (versions) that you will see:

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005

Identifying Compatibly Level

To check the compatibility level of your databases you can use one of these methods:

Using SQL Server Management Studio, right click on the database, select “Properties” and look at the “Options” page for each database as the following image shows:

Another option is to use sp_helpdb so you can get the information for all databases at once:

EXEC sp_helpdb

Or select directly from the sys.databases catalog to get the information for all databases.

SELECT * FROM sys.databases


Compatibly Level for New Databases

When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use.  The compatibility level that is used is the compatibility level of your model database.

Here is a sample CREATE DATABASE command, but there is not an option to change the compatibility level.

CREATE DATABASE [test] ON PRIMARY
( NAME = N’test’, FILENAME = N’Z:\SQLData\test.mdf’ , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’test_log’, FILENAME = N’Y:\SQLData\test3_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%)
GO

When creating a database using SQL Server Management Studio you have the ability to change the compatibility level on the “Options” tab such as follows:

If we use the “Script” option we can see that SQL Server issues the CREATE DATABASE statement and then issues “sp_dbcmptlevel” to set the database compatibility level to 80 as shown below.

CREATE DATABASE [test] ON PRIMARY
( NAME = N’test’, FILENAME = N’Z:\SQLData\test.mdf’ , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’test_log’, FILENAME = N’Y:\SQLData\test3_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80
GO

Changing Compatibility Level

So once you have identified the compatibility level of your database and know what you want to change it to you can use the sp_dbcmptlevel system stored procedure to make the change.  The command has the following syntax:

sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
–to change to level 80
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80

–to change to level 90
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=90

–or
sp_dbcmptlevel ‘test’, ’80’

sp_dbcmptlevel ‘test’, ’90’


Differences

There are several differences on how compatibly levels affect your database operations.  SQL Server Books Online has a list of these differences and the following list shows you a few of these items:

Compatibility level setting of 80 or earlier Compatibility level setting of 90 Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional. With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL). High
The *= and =* operators for outer join are supported with a warning message. These operators are not supported; the OUTER JOIN keyword should be used. High
SET XACT_ABORT OFF is allowed inside a trigger. SET XACT_ABORT OFF is not allowed inside a trigger. Medium

(Source: SQL Server 2005 Books Online)  For a complete list of these items look here:

In addition, each new compatibility level offers a new list of reserved keywords.  Here is a list of the new keywords for SQL Server 2005.

Compatibility level setting Reserved keywords
90 PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80 COLLATE, FUNCTION, OPENXML
70 BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP
65 AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

(Source: SQL Server 2005 Books Online)

If one of these keywords is being used and your database is set to this compatibly level the commands will fail.  To get around this you could put the keyword in either square brackets ([ ]) or use quotation marks (” “) such as [PIVOT] or “PIVOT”.


Summary

The compatibly level setting is used by SQL Server to determine how certain new features should be handled.  This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.  This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.

In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

04/10/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , , | Leave a comment

A guide to recover a database from SUSPECT mode

One of the worst situations I can imagine for a database professional is to get a call reporting a production database is in a “Suspect” state and the business cannot continue. This is a “code red” situation where DBA needs to bring the database online as soon as possible. In this article, I will formulate couple of steps which may be used as a high level process to handle this situation.

The first step is always to inform your customer about the outage before they come back to you. I find most of the times this proactive step becomes a life saver. In this way, you will not be questioned for the time you will spend to bring the database online.

Then refer to the SQL Server error log to find the root cause of the issue. It will give you a clear reason mentioning why the database is in Suspect mode. From SQL Server 2005 onwards, I recommend to use a filter while viewing the log and use “Database name” as the filter criteria. In this way you will only see the logs related to that particular database and then refer to the latest log/s for the root cause. Up until SQL Server 2000, the only option is reading the log from the latest entry backwards to find the root cause. Assuming you found the reason why database is in suspect mode, now you need to take appropriate step to fix the issue.

Here I will discuss some of the possible issues which can put a database in Suspect mode and recommend appropriate resolution.

Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below command to bring your database online with no data loss.

RESTORE DATABASE WITH RECOVERY

Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the file handler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss:

RESTORE DATABASE WITH RECOVERY

Possibility 3: This is a worst case scenario. Database is in suspect because of a corrupted transaction. This is a bad news as you may have to lose data at this point unless you have a good backup! Also this is the most common case I saw for putting an OLTP database in Suspect mode.

The root cause of this issue is actually from a guarantee taken by SQL Server to ensure transaction consistency under fundamental ACID property of RDBMS. The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back, SQL server could not complete (commit/rollback) the transaction.

At this point, I recommend you to take a decision. If you have a good backup and can restore the database in an acceptable time up to an acceptable point, then go ahead for it. But if restore is not an option at this point, then you have to execute below steps:

Caution! Below steps will cause you to lose data and hence are extremely dangerous to execute. I recommend trying all other possible options including calling Microsoft Support before executing below steps.

1. Switch the Emergency mode on for the database using below command:

ALTER DATABASE SET EMERGENCY;

2. Then execute below command:

dbcc checkdb (”,repair_allow_data_loss)

Please note that as stated above this is an extremely dangerous command to execute. It is a one- way command (that is you can not rollback back this execution once you started it) which can cause loss in data or database integrity. Technically, by executing this command you are actually authorizing SQL Server to force transactional recovery to run and skip the errors. If it faces errors, this operation scavenges as much out of the transaction log as it can and then rebuilds the transaction log from scratch. So taking this step is really the last resort you should try after every other attempts fails.

After this operation is complete you will have your database back online. However, you’ll most likely have lost a bunch of data, broken constraints and inherent business logic in the database but at least you haven’t lost everything. You may want to involve your customers to run a sanity check on the data quality at this point.

Possibility 4: If you find out that your data file is corrupted then most likely you have OS / Hardware level failure. For this these type of failure or anything really weird which you can not fixed easily with in acceptable amount of time, your best bet is restore from backup with out really wasting time in a “Code Red” situation.

So in this article, I covered some possible reasons which can put a database in “Suspect” and then the options to be back in business in shortest possible time. To conclude, I would like to add below basic homework tips so that you can be in a good shape while handling this situation:

1. Always ensure that you have a good backup/DR strategy and your customer is in agreement with the risk of possible data/time loss in case of disaster
2. Do not ever attempt to “detach” a database which is in Suspect. This will do nothing but you will probably loose your ability to repair it.
3. Read and understand the error log before taking any action and do not panic. Nothing in computer science is “unexplainable” so if you keep your calm and apply intelligence, you will come out of any situation with honor.

04/10/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , , | 2 Comments

SQL SERVER VERSIONS


I’m continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft’s site. So here’s an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fill out any missing data, please post in the comments and I’ll update the article.

Article Body

SQL Server 2008 R2
10.50.2789.0 SQL Server 2008 R2 SP1 CU3 17 Oct 2011
10.50.2772.0 SQL Server 2008 R2 SP1 CU2 16 Aug 2011
10.50.2769.0 SQL Server 2008 R2 SP1 CU1 16 Sep 2011
10.50.2500.0 SQL Server 2008 R2 SP1 11 Jul 2011
10.50.1807.0 SQL Server 2008 R2 CU10 19 Oct 2011
10.50.1804.0 SQL Server 2008 R2 CU9 23 Aug 2011
10.50.1797.0 SQL Server 2008 R2 CU8 16 Sep 2011
10.50.1777.0 SQL Server 2008 R2 CU7 16 Jun 2011
10.50.1765.0 SQL Server 2008 R2 CU6 21 Feb 2011
10.50.1753.0 SQL Server 2008 R2 CU5 20 Dec 2010
10.50.1746.0 SQL Server 2008 R2 CU4 18 Oct 2010
10.50.1734.0 SQL Server 2008 R2 CU3 17 Aug 2010
10.50.1720.0 SQL Server 2008 R2 CU2 25 Jun 2010
10.50.1702.0 SQL Server 2008 R2 CU1 18 May 2010
10.50.1600.1 SQL Server 2008 R2 RTM 12 Apr 2010
SQL Server 2008
10.00.5768 SQL Server 2008 SP3 CU2 22 Nov 2011
10.00.5766 SQL Server 2008 SP3 CU1 18 Oct 2011
10.00.5500 SQL Server 2008 SP3 6 Oct 2011
10.00.4323 SQL Server 2008 SP2 CU7 21 Nov 2011
10.00.4321 SQL Server 2008 SP2 CU6 20 Sep 2011
10.00.4316 SQL Server 2008 SP2 CU5 18 Jul 2011
10.00.4285 SQL Server 2008 SP2 CU4 16 May 2011
10.00.4279 SQL Server 2008 SP2 CU3 21 Mar 2011
10.00.4272 SQL Server 2008 SP2 CU2 17 Jan 2011
10.00.4266 SQL Server 2008 SP2 CU1 15 Nov 2010
10.00.4000 SQL Server 2008 SP2 29 Sep 2010
10.00.2850 SQL Server 2008 SP1 CU16 19 Sep 2011
10.00.2847 SQL Server 2008 SP1 CU15 18 Jul 2011
10.00.2816 SQL Server 2008 SP1 CU13 22 Mar 2011
10.00.2812 SQL Server 2008 SP1 CU14 16 May 2011
10.00.2808 SQL Server 2008 SP1 CU12 17 Jan 2011
10.00.2804 SQL Server 2008 SP1 CU11 15 Nov 2010
10.00.2799 SQL Server 2008 SP1 CU10 21 Sep 2010
10.00.2789 SQL Server 2008 SP1 CU9 19 Jul 2010
10.00.2775 SQL Server 2008 SP1 CU8 17 May 2010
10.00.2766 SQL Server 2008 SP1 CU7 15 Mar 2010
10.00.2757 SQL Server 2008 SP1 CU6 18 Jan 2010
10.00.2746 SQL Server 2008 SP1 CU5 24 Nov 2009
10.00.2734 SQL Server 2008 SP1 CU4 22 Sep 2009
10.00.2723 SQL Server 2008 SP1 CU3 21 Jul 2009
10.00.2714 SQL Server 2008 SP1 CU2 18 May 2009
10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
10.00.2531 SQL Server 2008 SP1 7 Apr 2009
10.00.1835 SQL Server 2008 RTM CU10 15 Mar 2010
10.00.1828 SQL Server 2008 RTM CU9 18 Jan 2009
10.00.1823 SQL Server 2008 RTM CU8 16 Nov 2009
10.00.1818 SQL Server 2008 RTM CU7 21 Sep 2009
10.00.1812 SQL Server 2008 RTM CU6 21 Jul 2009
10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
10.00.1763 SQL Server 2008 RTM CU1 22 Sep 2008
10.00.1600 SQL Server 2008 RTM 6 Aug 2008
SQL Server 2005
9.00.5266 SQL Server 2005 SP4 CU3 21 Mar 2011
9.00.5259 SQL Server 2005 SP4 CU2 22 Feb 2011
9.00.5254 SQL Server 2005 SP4 CU1 20 Dec 2010
9.00.5000 SQL Server 2005 SP4 17 Dec 2010
9.00.4325 SQL Server 2005 SP3 CU15 21 Mar 2011
9.00.4317 SQL Server 2005 SP3 CU14 21 Feb 2011
9.00.4315 SQL Server 2005 SP3 CU13 20 Dec 2010
9.00.4311 SQL Server 2005 SP3 CU12 18 Oct 2010
9.00.4309 SQL Server 2005 SP3 CU11 17 Aug 2010
9.00.4305 SQL Server 2005 SP3 CU10 23 Jun 2010
9.00.4294 SQL Server 2005 SP3 CU9 19 Apr 2010
9.00.4285 SQL Server 2005 SP3 CU8 16 Feb 2010
9.00.4273 SQL Server 2005 SP3 CU7 21 Dec 2009
9.00.4266 SQL Server 2005 SP3 CU6 19 Oct 2009
9.00.4230 SQL Server 2005 SP3 CU5 17 Aug 2009
9.00.4226 SQL Server 2005 SP3 CU4 16 Jun 2009
9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
9.00.4053 SQL Server 2005 SP3 GDR (Security Update) 13 Oct 2009
9.00.4035 SQL Server 2005 SP3 16 Dec 2008
9.00.3356 SQL Server 2005 SP2 CU17 21 Dec 2009
9.00.3355 SQL Server 2005 SP2 CU16 19 Oct 2009
9.00.3330 SQL Server 2005 SP2 CU15 18 Aug 2009
9.00.3328 SQL Server 2005 SP2 CU14 16 Jun 2009
9.00.3325 SQL Server 2005 SP2 CU13 21 Apr 2009
9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
9.00.3310 SQL Server 2005 SP2 Security Update 10 Feb 2009
9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
9.00.3257 SQL Server 2005 SP2 CU8 16 Jun 2008
9.00.3239 SQL Server 2005 SP2 CU7 14 Apr 2008
9.00.3233 SQL Server 2005 QFE Security Hotfix 8 Jul 2008
9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
9.00.3175 SQL Server 2005 SP2 CU2 28 Jun 2007
9.00.3161 SQL Server 2005 SP2 CU1 15 Apr 2007
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
9.00.3054 SQL Server 2005 KB934458 5 Apr 2007
9.00.3042.01 SQL Server 2005 “SP2a” 5 Mar 2007
9.00.3042 SQL Server 2005 SP2 1 Feb 2007
9.00.2047 SQL Server 2005 SP1
9.00.1399 SQL Server 2005 RTM 1 Nov 2005
SQL Server 2000
8.00.2039 SQL Server 2000 SP4
8.00.0760 SQL Server 2000 SP3
8.00.0534 SQL Server 2000 SP2
8.00.0384 SQL Server 2000 SP1
8.00.0194 SQL Server 2000 RTM
SQL Server 7
7.00.1063 SQL Server 7 SP4
7.00.0961 SQL Server 7 SP3 15 Dec 2000
7.00.0842 SQL Server 7 SP2 20 Mar 2000
7.00.0699 SQL Server 7 SP1 15 Jul 1999
7.00.0623 SQL Server 7 RTM
SQL Server 6.5
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5
6.50.281 SQL Server 6.5 SP4
6.50.258 SQL Server 6.5 SP3
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 RTM

You can determine what version SQL Server is running by running

Select @@version

@@Version is a system level variable that holds the current version. On my computer this returns

Microsoft SQL Server  2000 - 8.00.384 (Intel X86) 
	May 23 2001 00:02:52 
	Copyright (c) 1988-2000 Microsoft Corporation
	Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

The main version number is 8.00.384 which corresponds to SQL Server 2000 SP1. See below for a complete list of versions. It will also tell us the version of the operating system we’re running. In this case I’m running Windows 2000 (aka NT 5.0) Service Pack 2. You can find this same information in Enterprise Manager by right clicking on a server and choosing Properties. The version information is displayed in the General tab.

This information is pulled from the system extended procedure xp_msver. You can call this stored procedure like

exec master..xp_msver

and it returns

Index  Name                             Internal_Value Character_Value                     

------ -------------------------------- -------------- ------------------------------
1      ProductName                      NULL           Microsoft SQL Server
2      ProductVersion                   524288         8.00.384
3      Language                         1033           English (United States)
4      Platform                         NULL           NT INTEL X86
5      Comments                         NULL           NT INTEL X86
6      CompanyName                      NULL           Microsoft Corporation
7      FileDescription                  NULL           SQL Server Windows NT
8      FileVersion                      NULL           2000.080.0384.00
9      InternalName                     NULL           SQLSERVR
10     LegalCopyright                   NULL           © 1988-2000 Microsoft ...
11     LegalTrademarks                  NULL           Microsoft® is a registered ...
12     OriginalFilename                 NULL           SQLSERVR.EXE
13     PrivateBuild                     NULL           NULL
14     SpecialBuild                     25165824       NULL
15     WindowsVersion                   143851525      5.0 (2195)
16     ProcessorCount                   1              1
17     ProcessorActiveMask              1              00000001
18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM
19     PhysicalMemory                   255            255 (267902976)
20     Product ID                       NULL           NULL

03/16/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , | Leave a comment

BIZTALK: How to Cluster the Master Secret Server

The last few weeks I have been setting up a new Biztalk environment with the Biztalk databases on a MS SQL 2008 R2 SP1 failover cluster. In my last post i showed how to cluster the MSMQ and MSDTC and here is my way to cluster the Master Secret Server. When you cluster the master secret server, the Single Sign-On servers communicate with the active clustered instance of the master secret server. Similarly, the active clustered instance of the master secret server communicates with the SSO database.

To install and configure Enterprise SSO on the cluster nodes (Windows Server 2008)

  1. Install BizTalk Server 2010 on each cluster node. In the Component Installation dialog box of the Microsoft BizTalk Server Installation Wizard, select to install the Enterprise Single Sign-On Administration Module and Enterprise Single Sign-On Master Secret Server components. After installation has completed successfully you have the option to run the BizTalk Server 2010 Configuration program but do not do so at this time.
  2. Create domain groups with the names SSO Administrators and SSO Affiliate Administrators. To create a clustered instance of the Enterprise SSO service, you must create the SSO Administrators and SSO Affiliate Administrators groups as domain groups.
  3. Create or designate a domain account that is a member of the SSO Administrators domain group. The Enterprise SSO service on each node will be configured to log on as this domain account. This account must have the Log on as a service right on each node in the cluster.
  4. Add the account that you are using to log on during the configuration process to the domain SSO Administrators group.
  5. Start the BizTalk Server 2010 Configuration program. Click Start, point to Programs, point to Microsoft BizTalk Server 2010, and then click BizTalk Server Configuration to display the Microsoft BizTalk Server 2010 Configuration dialog box.
  6. Choose the Custom Configuration option and enter the appropriate values for the Database server name, User name and Password fields. After entering these values click the Configure button to continue.
  7. Select the Enterprise SSOoption from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and set the following options for the Enterprise SSO feature:
    1. Select the check the box next to Enable Enterprise Single Sign-On on this computer.
    2. Click the option to Create a new SSO system.
    3. Enter the appropriate values under Data stores for Server Name and Database Name.
    4. Verify that the domain account that you created earlier is the account that is associated with the Enterprise SSO service.
    5. Specify the domain SSO Administrators group that you created earlier as the group associated with the SSO Administrator(s) role.
    6. Specify the domain SSO Affiliate Administrators group that you created earlier as the group associated with the SSO Affiliate Administrator(s) role.
  8. Select the Enterprise SSO Secret Backup option from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and provide the appropriate parameters for backing up the Enterprise SSO secret. By default the Enterprise SSO secret is backed up to <drive>:\Program Files\Common Files\Enterprise Single Sign-On\SSOxxxx.bak.
  9. Click the Apply Configuration option to display the Microsoft BizTalk Server 2010 Configuration Wizard Summary dialog box.
  10. Click Next to apply the configuration.
  11. Click Finish to close the Microsoft BizTalk Server 2010 Configuration Wizard.
  12. Close the Microsoft BizTalk Server 2010 Configuration program.
  13. Log on to the passive cluster node and start the BizTalk Server 2010 Configuration program.
  14. Choose the Custom Configuration option and enter the same values for the Database server name, User name, and Password fields that you entered when configuring the first cluster node. After entering these values click the Configure button to continue.
  15. Select the Enterprise SSOoption from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and set the following options for the Enterprise SSO feature:
    1. Check the box next to Enable Enterprise Single Sign-On on this computer.
    2. Click the option to Join an existing SSO system.
    3. Enter the same values for the SSO Database Server Name and Database Name that you entered when configuring the first cluster node.
    4. Enter the same value for the domain account that you entered when configuring the first cluster node.
  16. Click the Apply Configuration option to display the Microsoft BizTalk Server 2010 Configuration Wizard Summary dialog box.
  17. Click Next to apply the configuration.
  18. Click Finish to close the Microsoft BizTalk Server 2010 Configuration Wizard.
  19. Close the Microsoft BizTalk Server 2010 Configuration program.

To update the master secret server name in the SSO database

  1. Type the following commands from a command prompt on the active cluster node to stop and restart the Enterprise SSO service:
  1. net stop entsso

and

net start entsso

  1. Change the master secret server name in the SSO database to the cluster name by following these steps:
Note
The cluster name is the name defined for the network name resource that you have created in the cluster group / clustered service or application that will contain the clustered Enterprise SSO service. For example, the name may be BIZTALKCLUSTER.
    1. Paste the following code in a text editor:
  1. <sso>
  2.   <globalInfo>
  3.     <secretServer>BIZTALKCLUSTER</secretServer>
  4.   </globalInfo>
  5. </sso>
Note
BIZTALKCLUSTER is a placeholder for the actual network name resource that is created in the cluster group / clustered service or application.
    1. Save the file as an .xml file. For example, save the file as SSOCLUSTER.xml.
    2. At a command prompt, change to the Enterprise SSO installation folder. By default, the installation folder is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
    3. Type the following command at the command prompt to update the master secret server name in the database:

10.ssomanage -updatedb XMLFile

Note
XMLFile is a placeholder for the name of the .xml file that you saved earlier.

To create the clustered Enterprise SSO resource (Windows Server 2008)

  1. If the cluster is not configured with a clustered Distributed Transaction Coordinator (MSDTC) resource then follow the steps in my last post.
  2. Click Start, Programs, Administrative Tools, and then Failover Cluster Management to start the Failover Cluster Management program.
  3. In the left hand pane, right-click Failover Cluster Management and click Manage a Cluster.
  4. On the Select a cluster to manage dialog box, enter the cluster to be managed and click OK.
  5. In the left hand pane click to select a clustered service or application that contains an IP Address and Network Name resource.
Note
A clustered Enterprise SSO service does not explicitly require the use of a clustered Physical Disk resource in the same group.
  1. Right-click the clustered service or application, point to Add a resource, and click Generic Service to display the New Resource Wizard dialog.
Important
In the Generic Service Parameters dialog box, if you do not click to select the Use Network Name for computer name check box, SSO client computers will generate an error similar to the following when they try to contact this clustered instance of the Enterprise SSO service:

Failed to retrieve master secrets.

Verify that the master secret server name is correct and that it is available. Secret Server Name: ENTSSO Error Code: 0x800706D9, there are no more endpoints available from the endpoint mapper.

  1. On the Select Service page of the New Resource Wizard, click to select Enterprise Single Sign-On Service and click Next.
  2. On the Confirmation page click Next.
  3. On the Summary page click Finish. A clustered instance of the Enterprise Single Sign-On Service will appear under Other Resources in the center pane of the Failover Cluster Management interface.
  4. Right-click the clustered instance of the Enterprise Single Sign-On Service and select Properties to display the Enterprise Single Sign-On Service Properties dialog box.
  5. Click the Dependencies tab of the properties dialog box and click Insert.
  6. Click the drop down box under Resource, select the Name: resource and click OK.

To restore the master secret on the second cluster node (Windows Server 2008)

  1. In Failover Cluster Management, right click the clustered service or application that contains the clustered Enterprise Single Sign-On service and then click Bring this service or application online to start all of the resources in the clustered service or application.
  2. Right-click the clustered service or application, point to Move this service or application to another node, and click the second node. This step moves the clustered service or application that contains the clustered Enterprise Single Sign-On service from the first node to the second node.
  3. Right-click the clustered Enterprise Single Sign-On service and click Take this service or application offline, then right-click the clustered instance of the Enterprise SSO service and click Bring this service or application online.
Note
If this step is not completed the attempt to restore the master secret may not succeed.
  1. Copy the master secret backup file from the first node to the \Enterprise Single Sign-On installation folder on the second node. By default, the installation folder is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
  2. Log on to the second node and at a command prompt, change to the Enterprise SSO installation folder.
  3. Type the following command from the command prompt to restore the master secret to the second node:
  1. ssoconfig -restoresecret RestoreFile
Note
Replace RestoreFile with the path of and the name of the backup file that contains the master secret.
  1. The master secret is stored in the registry at the following location:
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ENTSSO\SSOSS
  3. Move the clustered service or application that contains the clustered Enterprise Single Sign-On service from this cluster node to other cluster node to ensure failover functionality. Then move the cluster group back to verify fail-back functionality.

 

03/12/2012 Posted by | Biztalk, SQL Scripting, Sql Server, SSO, T-SQL | , , , , | 1 Comment

BIZTALK: Database Structure and Jobs

The last week I have implemented a new Biztalk environment with the databases on a SQL 2008 R2 SP1 failover cluster. I’ve found some interesting information about Biztalk design, functionality and tuning that i would like to share with you.

This topic shows the database structure and database jobs for BizTalk Server 2006.

The following figure shows the processes and entities that write to the BizTalk Server databases.

Database write diagram showing the processes and entities that write to the BizTalk Server databases
Processes that write to BizTalk Server databases

BizTalk Server 2006 includes the following SQL Server Agent jobs to assist you in managing the BizTalk Server databases:

Aa561960.note(en-us,BTS.20).gifNote
The names of the jobs change depending on the database names given during configuration. If you have deployed multiple MessageBox databases in your environment, there will be several jobs for each MessageBox.
Warning
In the BizTalk Management (BizTalkMgmtDb) database, there’s a stored procedure named dbo.adm_cleanupmgmtdb. DO NOT RUN THIS STORED PROCEDURE! If you do run this stored procedure, all the entries in the database will be deleted.
Job Description
Backup BizTalk Server (BizTalkMgmtDb) This job performs full database and log backups of the BizTalk Server databases. For more information about configuring and running this job, see Backing Up and Restoring BizTalk Server Databases.
CleanupBTFExpiredEntriesJob_BizTalkMgmtDb This job cleans up expired BizTalk Framework (BTF) entries in the BizTalk Management (BizTalkMgmtDb) database.
DTA Purge and Archive (BizTalkDTADb) This job automatically archives data in the BizTalk Tracking (BizTalkDTADb) database and purges obsolete data. For more information about configuring and running this job, see Archiving and Purging the BizTalk Tracking Database.
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb This job detects when a BizTalk Server host instance (NT service) has stopped and releases all work that was being done by that host instance so that it can be worked on by another host instance.
MessageBox_Message_Cleanup_BizTalkMsgBoxDb This job removes all messages that are no longer being referenced by any subscribers in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables.

Caution
This is an unscheduled job which is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Do not manually start this job.
MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb This job manages the reference count logs for messages and determines when a message is no longer referenced by any subscriber.

Aa561960.note(en-us,BTS.20).gifNote
Even thought this SQL Server Agent job is scheduled to run once per minute, the stored procedure that is called by this job contains logic to ensure that the stored procedure runs continually. This is by design behavior and should not be modified.
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb This job removes all message parts that are no longer being referenced by any messages in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables. All messages are made up of one or more message parts, which contain the actual message data.
MessageBox_UpdateStats_BizTalkMsgBoxDb This job manually updates the statistics for the BizTalk MessageBox (BizTalkMsgBoxDb) database.
Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb This job is needed for multiple MessageBox deployments. It asynchronously performs operational actions such as bulk terminate on the master MessageBox after those changes have been applied to the subordinate MessageBox.
PurgeSubscriptionsJob_BizTalkMsgBoxDb This job purges unused subscription predicates from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database.
Rules_Database_Cleanup_BizTalkRuleEngineDb This job automatically purges old audit data from the Rule Engine (BizTalkRuleEngineDb) database every 90 days. This job also purges old history data (deploy/undeploy notifications) from the Rule Engine (BizTalkRuleEngineDb) database every 3 days.
TrackedMessages_Copy_BizTalkMsgBoxDb This job copies the messages bodies of tracked messages from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database to the Tracking (BizTalkDTADb) database.

03/08/2012 Posted by | Biztalk, Sql Server | , | 2 Comments

Installation of SSO on SQL Failover Cluster

In this post I will tell a story of my experience with installation of SSO on SQL Cluster. Each BizTalk Server has an Enterprise Single Sign-On service (EntSSO.exe). Enterprise Single Sign-On is also referred as SSO or EntSSO. SSO serves two purposes. One is for data encryption, that is, port URI data. And the other is, as what the name indicates, Single Sign-On. Single Sign-On is about credential mapping. BizTalk Server SSO currently supports only Windows-initiated Single Sign-On. That means you can only map Windows users accounts to external application (affiliate application) user accounts. On the inbound side the sender is authenticated with Windows; on the outbound side, BizTalk Server automatically authenticates with the affiliate applications using the preconfigured credential mapping. Single Sign-On is a useful feature in business-to-business (B2B) scenarios.

Note: However the encryption function is mandatory for a BizTalk system. Single Sign-On for credential mapping can be solved with other tools like Oracle Wallet.

In addition to the SSO services running on each of the BizTalk Servers, there is a master secret server. The master secret server is a server with the SSO service running on it. The master secret server can be one of the SSO services running on one of the BizTalk Servers, or a dedicated master secret server.

It is the same executable called EntSSO.exe, but with an additional sub component responsible for maintain and supply the master secret key to the SSO services on the other BizTalk Servers. The other SSO services running on the BizTalk Servers check every 30 seconds to see whether the master secret has changed. If it has changed, they read it securely; otherwise, they continue to use the master secret they already have cached in memory.

Considering there is only one master secret server in your entire environment and the dependency of BizTalk Server, it is recommended that you use an active-passive cluster configuration for the master secret server. Because the master secret server doesn’t consume a lot of resources, it is very common to use the SQL Server failover cluster for clustering the master secret server.

On the first server cluster node where you run BizTalk Server Configuration, you choose to create a new SSO system. That makes the cluster node as the master secret server. The host name of the master secret server is the host name of the physical cluster node. The master secret key is automatically generated on that node. On the other cluster node, you choose to join the existing SSO system. To cluster the master secret server, you need to change the master secret server from the first cluster node host name to the virtual SQL Server failover cluster network name (NameSQL1), and create a SSO generic service cluster resource. At the end, you restore the master secret key to the other cluster nodes. So, when the cluster fails over to another node, that node has the master secret. These steps can be done using the domain admin account (usually a network senior administrator will perform these steps with this account i.e. as example I will name the account InstallBizTalk).

Clustering the master secret server service is a complicated process. You might find it confusing when and where you need to perform a step, and the order of the steps. Here are some general rules:

· You must install and configure SSO on each of the cluster nodes. When you create the new SSO system on the first cluster node, this node can be either an active cluster node or a passive cluster node.

· After you successfully installed and configured SSO on all of the cluster nodes, you must update the master secret server host name from a physical cluster node host name to the virtual cluster network name, and you must change the rename from an active node.

· After the master secret server host name is changed, you must restart the SSO service on the active node to refresh the cache by taking the SSO cluster resource offline and then online.

· You must create an SSO cluster resource before restoring the master secret key on the other cluster nodes.

· Before you restore the master secret key on a cluster node, you must make it the active node first.

Steps involved to successfully install and configure SSO on cluster will be outlined here.

There are several SSO user groups. Two of them are required when configuring the master secret server. SSO Administrators have the highest level user rights in the SSO system; and SSO Affiliate Administrators defines the affiliate applications that the SSO system contains.

To create a domain group account for the SQL Server service groups

1. If you haven’t already logged on or if you are logged on with a different credential, log on to Cluster Node A using domain admin account.

2. Click Start, and then click Run.

3. In the Run dialog box, enter dsa.msc, and then click OK.

4. From Active Directory Users and Computers, if the YourDomain domain is not already expanded, click the plus sign (+) to expand the YourDomain.com domain.

5. In the left pane, right-click Users, point to New, and then click Group.

6. From New Object – Group, enter the following values, and then click OK.

Name Value
Group name SSO Administrators
Group scope Global
Group type Security

7. Repeat steps 5 to 6 to create one more group:

Name Value
Group name SSO Affiliate Administrators
Group scope Global
Group type Security

To create a domain user account for the SSO Service

1. (continue from the previous procedure)

2. In the left pane, right-click Users, point to New, and then click User.

3. From New Object – User, enter the following values, and then click Next.

Name Value
First name SSO
Last name Service
User logon name SSOService

4. Enter or select the following values, and then click Next.

Name Value
Password TBD
Confirm password TBD
User must change password at next logon (clear)
User cannot change password (select)
Password never expires (select)
Account is disabled (clear)

5. Click Finish.

Both YourDomain\SSOSerivce and domain admin account need to be members of the YourDomain\SSO Administrators group. It is designated for installing and configuring the BizTalk Server system.

To make YourDomain\SSOService and domain admin account members of SSO Administrators

1. (continue from the previous procedure)

2. In the left pane, highlight Users.

3. In the right pane, right-click SSO Service, point to All Tasks, and then click Add to a group.

4. From Select Group, enter or select the following values, and then click OK.

Name Value
Select this object type Group or Built-in security principal
From this location YourDomain
Enter the object name to select SSO administrators

5. To acknowledge that the account was created, click OK.

6. Repeat steps 3 to 5 to add domain admin account into the same group.

Granting YourDomain\SSO Administrators Full Control on Cluster Node A

You need to grant YourDomain\SSOService or YourDomain\SSO Administrators with the full control privilege on the cluster administrator.

To grant YourDomain\SSO Administrators full control on the cluster

1. If you haven’t already logged on or if you are logged on with a different credential, log on to Cluster Node A as YourDomain\IInstallBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. From Cluster Administrator, in the left pane, right-click CLUSTER NODE A, and then click Properties.

4. From CLUSTER NODE A Properties, click the Security tab, and then click Add.

5. From Select Users, Computers, or Groups, enter the following values, and then click OK.

Name Value
Select this object type Group or Built-in security principal
From this location YourDomain.com
Enter the object name to select SSO administrators

6. Verify the Allow box is selected, and then click OK.

Installing the SSO Components on Cluster Node A

With the accounts and permissions configured in the last step, you can now install the master secret server. BizTalk Server is not cluster aware as SQL Server is. You will need to install SSO on each of the cluster nodes. You will also need to create the SSO cluster resource manually.

BizTalk Server installation process has two parts. In this step, you will install the components. And the next step is configuring the master secret server.

To install the SSO components on Cluster Node A and Cluster Node B

1. If you haven’t logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

2. Run setup.exe to install BizTalk Server 2006 R2.

3. On the Start page, click Install Microsoft BizTalk Server 2006 R2 on this computer.

4. On the Customer Information page, enter information in the User name box, the Organization box, and the Product key box, and then click Next.

5. On the License Agreement page, read the license agreement, select yes, I accept the terms of the license agreement, and then click Next.

6. On the Component Installation page, clear all the check boxes, select Enterprise Single Sign-On Administration Module and Enterprise Single Sing-On Master Secret Server from the Additional Software group, and then click Next.

clip_image002

7. On the Summary page, click Install.

8. On the Installation Completed page, clear Launch BizTalk Server Configuration check box, and then click Finish.

Installing the SSO Components on Cluster Node B

Repeat the same steps to install the SSO components on Cluster Node B.

Configuring the Master Secret Server on Cluster Node A

Configuring the master secret server has three parts, creating SSO database, assigning SSO service account, and backing-up the master secret. Notice there are two options, create a new SSO system, and join an existing SSO system. On the first cluster nodes, you must choose to create a new SSO system. When you create a new SSO system, you must specify the database server name, and the database name. But you don’t need to specify the master secret server host name. The current host name, becomes the default master secret server. Later, you must change the master secret server from the physical cluster node host name to the virtual cluster host name, YourVirtualServerName.

It doesn’t matter whether Cluster Node A is the active node or a passive node when you go through this procedure.

To configure the master secret server on Cluster Node A

1. If you haven’t logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

2. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Configuration.

3. On the Microsoft BizTalk Server 2006 Configuration page, choose Custom Configuration, enter the following values, and then click Configure.

Name Value
Database server name Database Name Cluster
User name YourDomain\SSOService
Password TBD

4. in the left pane, click Enterprise SSO.

5. In the right pane, enter or select the following values:

Name Value
Enable Enterprise Single Sign-On on this computer (checked)
Create a new SSO system (selected)
SSO Database: Server Name Database Name Cluster
SSO Database: Database Name SSODB
Enterprise Single Sign-On Service: Account YourDomain\SSOService
SSO Administrator(s): Windows Group YourDomain\SSO Administrators
SSO Affiliate Administrators(s): Windows Group YourDomain\SSO Affiliate Administrators

image

6. In the left pane, click Enterprise SSO Secret Backup. The Enterprise SSO secret is very critical. You must back it up to a file. It is a good practice to burn the key into a CD and store the CD in a safe place.

7. In the right pane, enter the following values:

Name Value
Secret back password TBD
Confirm password TBD
Password reminder TBD
Backup file location C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSecret.bak)

8. Click Apply Configuration.

9. On the Summary page, to apply the configuration, click Next.

10. Verify that the Configuration Result is Success, and then click Finish.

11. Close Microsoft BizTalk Server 2006 Configuration.

1.1.4 Configuring SSO on Cluster Node B

On the second node, you choose to join the existing SSO system. When joining the existing SSO system, it shares the SSO database of the existing SSO system.

To configure the master secret server on Cluster Node B

1. If you haven’t logged on, log on to Cluster Node B as YourDomain\InstallBizTalk.

2. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Configuration.

3. On the Microsoft BizTalk Server 2006 Configuration page, choose Custom Configuration, enter the following values, and then click Configure.

Name Value
Database server name ServerName Database Cluster
User name YourDomainSSOService
Password TBD

4. In the left pane, click Enterprise SSO.

5. In the right pane, enter or select the following values:

Name Value
Enable Enterprise Single Sign-On on this computer (checked)
Join an existing SSO system (selected)
Server Name ServerName Database Cluster
Database Name SSODB
Account YourDomain\SSOService

6. Click Apply Configuration.

7. On the Summary page, to apply the configuration, click Next.

8. Verify that the Configuration Result is Success, and then click Finish.

9. Close Microsoft BizTalk Server 2006 Configuration.

1.1.5 Updating the Master Secret Server Host Name

When SSO was configured on the first cluster node, it created a new SSO system. It used the host name of the physical cluster node as the master secret server host name that is Cluster Node A. You must change it to the server cluster virtual name, which is YourVirtualClusterName. This procedure must be carried out from the active cluster node. All it does is to update the master secret server field in the SSO database.

To configure the master secret server on Cluster Node B

1. If you haven’t already logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

<sso>
  <globalInfo>
    <secretServer> ServerName Database Cluster</secretServer>
  </globalInfo>
</sso>

2. Open a notepad.exe, create a file with the following content, and then save it as “C:\Program Files\Common Files\Enterprise Single Sign-On\SSOCluster.xml” (with the double quotes). The content is case sensitive.

clip_image002[5]

3. Open a command prompt, and then change directory to the C:\Program Files\ Common Files\Enterprise Single Sign-On\ folder.

4. From the command prompt, execute the following command:

ssomanage -updatedb SSOCluster.xml

5. Verify the master secrete server name has been changed to  as shown below:

C:\Program Files\Common Files\Enterprise Single Sign-On>ssomanage -updatedb ssocluster.xml
Using SSO server on this computer
 
Updated SSO global information with the following values -
 
SSO secret server name                  : ServerName Database Cluster
SSO Admin account name                  : NOT CHANGED
SSO Affiliate Admin account name        : NOT CHANGE

image

Creating SSO Cluster Resource

BizTalk Server is not cluster aware. So you must manually create the master secret server cluster resource. You can either create a dedicated virtual server (cluster group) for the SSO cluster resource, or use an existing cluster group. The instructions provided use the SQL Server Cluster Group. If you create a dedicated cluster group, you also need to create a network name cluster resource depended by the SSO cluster resource.

To Create SSO cluster resource

1. If you haven’t already logged on, log on to Cluster Node A as YourDomain\InstBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. In the left pane, expand CLUSTER NODE A, expand Groups, and then expand SQL Server Cluster Group. If you get a prompt before it opens Cluster Administrator, choose Open Existing Cluster, and point it to CLUSTER NODE A.

4. Right-click SQL Server Cluster Group, click New, and then click Resource.

5. From New Resource, enter or select the following values, and then click Next.

clip_image002[9]

6. From Possible Owners, verify that CLUSTER NODE A and CLUSTER NODE B are in the Possible owners list, and then click Next.

7. From Dependencies, select SQL Network Name (Cluster Node A) and click Add. And then click Next.

8. From Generic Service Parameters, type or select the following values, and then click Next:

clip_image002[11]

9. From Registry Replication, click Finish.

clip_image001Note
Do not configure any registry keys for replication in the Registry Replication dialog box. Replication of registry keys is not a requirement when creating a SSO cluster resource and, in fact, may cause problems when failover of this cluster resource is attempted.

10. In the details pane, right-click ENTSSO, and click Bring Online. Verify that the state is changed to Online.

Restoring the Master Secret on Cluster Node B

Before restoring the master secret on Cluster Node B, you must make Cluster Node B as the active cluster node, and restart the cluster resource by taking the cluster resource offline and then online.

To make Cluster Node B the active cluster node

1. Log on to Cluster Node B as RBW-NL\InstBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. From Cluster Administrator, expand CLUSTER NODE A in the left pane, expand Groups, and then expand SQL Server Cluster Group. In the details pane, the Owner column of the cluster resources shows the active cluster node

4. If Cluster Node B is not the active cluster node, in the left pane right-click SQL Server Cluster Group, and then click Move Group. Wait until all the cluster resources are online.

5. In the details pane, right-click ENTSSO, and then click Take Offline. Wait until all the cluster resources are offline.

6. In the details pane, right-click ENTSSO, and then click Bring Online. Wait until all the cluster resources are online.

To restore the master secret on the second cluster node

1. Copy the master secret backup file, C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSecret.bak, on Cluster Node A to the same folder on Cluster Node B. SSOSecret.bak is how you named the file when you configured the master secret server on Cluster Node A.

2. Open a command prompt, and then change the directory to C:\Program Files\Common Files\Enterprise Single Sign-On.

3. Type and execute the following command in the command prompt:

ssoconfig -restoresecret SSOSecret.bak

image

Through following this procedure you will be successful in deploying SSO on SQLCluster and configure SSO. My experience is that in following this procedure with an senior administrator inside an organization works the best. This procedure us done with BizTalk Server 2006 R2, but is also suitable for BizTalk Server 2009.

02/28/2012 Posted by | Active Directory, Biztalk, Cluster Configuration, Sql Server, SSO, Windows Server | , , , | 1 Comment

%d bloggers like this: