Search results matching tag 'Updates'

Halloween Protection – The Complete Series

Paul White — Wed, 20 Feb 2013 23:29:42 GMT

I have just published a four-part series for SQLPerformance.com on the Halloween Problem. Some of you will never have heard of this issue, and those that have might associate it only with T-SQL UPDATE queries. In fact, the Halloween problem affects execution plans for INSERT, UPDATE, DELETE and MERGE statements.

This is a topic I have been meaning to write about properly for years, ever since I read Craig Freedman’s 2008 blog post on the topic, which ended with the cryptic comment:

“…although I've used update statements for all of the examples in this post, some insert and delete statements also require Halloween protection, but I'll save that topic for a future post.”

That future post never materialized, sadly, so I thought I would have a go. The four parts of the series are summarized and linked below, I hope you find the material interesting.

Part 1 – The Halloween Problem and UPDATE statements

The SQL standard and three-phase separation
Logical update processing
Pipelined execution
The Halloween problem
Avoiding the problem in UPDATE statements

Part 2 – The Halloween Problem in INSERT and DELETE queries

INSERT examples
DELETE examples
Constraint checking and phase separation

Part 3 – Halloween Problem optimizations for MERGE

MERGE contains several optimizations the other DML statements do not
Hole-filling with merge join
Hole-filling with nested loops
Avoiding an extra B-tree navigation
Avoiding the join

Part 4 – The Halloween Problem and the Query Optimizer

Early optimization approaches
The SQL Server optimizer approach
The case of the redundant sort
HP levels and properties
Plan changes for Halloween Protection
Non-spool options
Row versioning
Heaps and forwarded records
T-SQL functions

As always, I appreciate your comments and feedback.

Paul White
@SQL_Kiwi
SQLkiwi@gmail.com

Deletes that Split Pages and Forwarded Ghosts

Paul White — Thu, 30 Aug 2012 16:09:00 GMT

Can DELETE operations cause pages to split? Yes. It sounds counter-intuitive on the face of it; deleting rows frees up space on a page, and page splitting occurs when a page needs additional space. Nevertheless, there are circumstances when deleting rows causes them to expand before they can be deleted. The mechanism at work here is row versioning (extract from Books Online below):

Isolation Levels

The relationship between row-versioning isolation levels (the first bullet point) and page splits is reasonably clear. Any data that existed before either of the isolation levels was enabled will need to have the 14 bytes added by future data modifications, perhaps causing pages to split. In this scenario, tables will likely contain a mix of records to start with, but over time (particularly as index maintenance is performed) the database will end up with row versions on most records, reducing the chances of a page split for that particular reason.

There is nevertheless a window of opportunity where adding the 14 bytes to an existing record could cause a page split. No doubt there’s a recommendation out there somewhere to rebuild all tables and indexes when enabling or disabling a row-versioning isolation level on a database. This is not all that interesting though, so let’s look at the second bullet point instead:

Triggers

The documentation says that versioning information is added if the table has a trigger. What it doesn’t say is:

The extra bytes for row versioning can be added even where both READ_COMMITTED_SNAPSHOT and SNAPSHOT isolation are OFF.
This only applies to AFTER triggers, not INSTEAD OF triggers
The AFTER trigger also needs to be enabled to generate row versions, the mere existence of a trigger is not enough.
There is a very important exception to all the above…

SQL Server can still avoid adding the row-versioning information even where an enabled AFTER TRIGGER exists (the remainder of this post assumes that both row-versioning isolation levels are OFF, by the way).

Avoiding Row Versioning with Triggers

To explore this behaviour in a bit of detail, we’ll need a test rig:

     USE tempdb;

    GO

    IF  OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL

        DROP TABLE dbo.Test;

    GO

    -- Test table

    CREATE TABLE dbo.Test

    (

        ID          integer IDENTITY PRIMARY KEY,

        Column01    nvarchar(20) NULL,

        Column02    nvarchar(4000) NULL,

    );

    GO

    -- Add some rows

    INSERT dbo.Test WITH (TABLOCKX)

        (Column01)

    SELECT TOP (100000)

        CONVERT(nvarchar(20), N'X')

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3

    OPTION (MAXDOP 1);

    GO

    -- A trigger that does nothing

    CREATE TRIGGER trg

    ON dbo.Test

    AFTER DELETE

    AS RETURN;

    GO

    -- Write any dirty pages to disk

    CHECKPOINT;

    GO

    -- Physical storage before any changes

    SELECT

        ddips.index_type_desc,

        ddips.alloc_unit_type_desc,

        ddips.page_count,

        ddips.record_count,

        ddips.max_record_size_in_bytes

    FROM sys.dm_db_index_physical_stats(

        DB_ID(), OBJECT_ID(N'dbo.Test', N'U'), NULL, NULL, 'DETAILED') AS ddips

    WHERE

        ddips.index_level = 0;

    GO

    -- Buffer pool pages before any changes

    SELECT 

        dobd.[file_id],

        dobd.page_id,

        dobd.page_type,

        dobd.row_count,

        dobd.free_space_in_bytes,

        dobd.is_modified

    FROM sys.partitions AS p

    JOIN sys.allocation_units AS au 

        ON au.container_id = p.hobt_id

    JOIN sys.dm_os_buffer_descriptors AS dobd ON 

        dobd.allocation_unit_id = au.allocation_unit_id

    WHERE

        p.[object_id] = OBJECT_ID(N'dbo.Test', N'U')

    ORDER BY

        dobd.page_id;

    GO

    SET STATISTICS IO ON;

    -- Delete 1 in 10 rows

    DELETE dbo.Test

    WHERE ID % 10 = 0;

    SET STATISTICS IO OFF;

    GO

    SELECT

        [Page Splits] = COUNT_BIG(*)

    FROM sys.fn_dblog(NULL,NULL) AS fd 

    WHERE 

        fd.[Transaction Name] = N'SplitPage';

    GO

    -- Ensure ghosted records are processed so

    --  we see accurate per-page row counts

    DBCC FORCEGHOSTCLEANUP;

    GO

    -- Physical storage after the delete operation

    SELECT

        ddips.index_type_desc,

        ddips.alloc_unit_type_desc,

        ddips.page_count,

        ddips.record_count,

        ddips.max_record_size_in_bytes

    FROM sys.dm_db_index_physical_stats(

        DB_ID(), OBJECT_ID(N'dbo.Test', N'U'), NULL, NULL, 'DETAILED') AS ddips

    WHERE

        ddips.index_level = 0;

    GO

    -- Buffer pool pages after the delete operation

    SELECT 

        dobd.[file_id],

        dobd.page_id,

        dobd.page_type,

        dobd.row_count,

        dobd.free_space_in_bytes,

        dobd.is_modified

    FROM sys.partitions AS p

    JOIN sys.allocation_units AS au 

        ON au.container_id = p.hobt_id

    JOIN sys.dm_os_buffer_descriptors AS dobd ON 

        dobd.allocation_unit_id = au.allocation_unit_id

    WHERE

        p.[object_id] = OBJECT_ID(N'dbo.Test', N'U')

    ORDER BY

        dobd.page_id;

    GO

    --DBCC TRACEON (3604);

    --DBCC PAGE (tempdb, 1, 4720, 3);

The script performs the following actions:

Creates a clustered table with an ID and two data columns
Adds 100,000 rows each with a single ‘X’ character in the first column, and NULL in the second
Creates an AFTER DELETE trigger that does nothing at all except exist and be enabled
Displays physical storage and buffer pool information using DMVs
Deletes every tenth row in the table
Shows the number of leaf-level page splits that occurred
Displays the physical storage and buffer pool information again

Test One – Clustered Table

Typical output:

There are 235 data pages with a maximum physical record size of 17 bytes before and after the delete. Before the delete, each data page contains 426 rows with 2 bytes of free space. After the DELETE:

A total of 10,000 records have been deleted
The data page count remains at 235
The maximum record size is still 17 bytes
Each data page has lost 42 or 43 rows
The free space on each page has risen to 800 or 819 bytes
All data pages are marked as being modified in memory
A total of 237 logical reads are reported

No surprises there.

Test Two – Clustered Table

Now, run the script again with the only change being that Column01 is defined as nvarchar(22) instead of nvarchar(20). The before picture is the same as before, but the situation after the DELETE is very different:

There have been 234 page splits, increasing the data page count from 235 pages to 469 pages, and halving the number of rows on each data page. The number of reads reported has also blown out from 237 previously to 2342 logical reads in this run (a factor of ten worse).

Explanation

The cause of the page splitting is that the deleted records must be versioned. SQL Server 2005 and later uses the version store to build the inserted and deleted pseudo-tables used by AFTER triggers. Where the data has no pre-existing versioning data, adding the 14 bytes will result in a clustered index page split if the page contains insufficient free space to accommodate this expansion.

Temporarily turning off ghost record clean-up using global trace flag 661 and examining an affected data page using DBCC PAGE shows the following (remember to turn the trace flag off afterward if you try this):

Slots 8 and 10 on this page hold records that were unaffected by the DELETE; the physical row length is 17 bytes as displayed previously. The record that was in slot 9 has been deleted. It is a ghost record with versioning information added. The record size is 17 + 14 = 31 bytes, and this expansion with only 2 bytes of free space on the page caused it to split.

This explains why the nvarchar(22) DELETE test caused page splitting, but why didn’t the original nvarchar(20) script behave the same?

There is a performance optimization that can avoid adding row versioning information, but only if the table cannot generate ROW_OVERFLOW or LOB allocation units. This means that the definition of the table must not allow for LOBs or for the possibility of variable length columns moving off row. The actual size of the data stored is immaterial – it is the potential size that matters.

In our test, the nvarchar(22) column definition caused the maximum possible INROW size to just exceed the 8060 byte limit. (The exact INROW limit also depends on the table definition; marking one of the data columns SPARSE would reduce the limit to 8015-8019 bytes – whatever the right number is).

Heaps of Forwarded Ghosts

“Page splitting only occurs in index structures, are heap structured tables affected by this issue too?”

It is true that heap pages do not split, but when a heap row needs to expand, the engine will move the row to another page if insufficient free space exists on the current page. When the storage engine does this, it leaves a forward pointer behind to avoid updating all non-clustered indexes to reflect the new physical row locator.

For a heap table with an active AFTER trigger, and a LOB column (or the possibility of row-overflow) the row has to be versioned and ghosted. If the page contains insufficient free space to accommodate the versioning, the row moves to another page leaving a forwarding stub behind. This results in a forwarded ghost record. Ghost clean-up will normally remove this record pretty quickly, so we will need to disable that process temporarily. The following script creates the very special circumstances necessary to produce a forwarded ghost record this way (note this script is for test systems only should not be run in tempdb):

    USE Sandpit;

    GO

    IF  OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL

        DROP TABLE dbo.Test;

    GO

    -- Heap test

    CREATE TABLE dbo.Test

    (

        ID          integer IDENTITY PRIMARY KEY NONCLUSTERED,

        Column01    nvarchar(16) NULL,

        Column02    nvarchar(4000) NULL

    );

    GO

    -- Add some all-NULL rows

    INSERT dbo.Test WITH (TABLOCKX)

        (Column01)

    SELECT TOP (100000)

        NULL

    FROM sys.columns AS c

    CROSS JOIN sys.columns AS c2

    CROSS JOIN sys.columns AS c3

    OPTION (MAXDOP 1);

    GO

    -- Ensure rows are tightly packed

    ALTER TABLE dbo.Test REBUILD;

    GO

    -- A trigger that does nothing

    CREATE TRIGGER trg

    ON dbo.Test

    AFTER DELETE

    AS RETURN;

    GO

    -- Write any dirty pages to disk

    CHECKPOINT;

    GO

    -- Physical storage before any changes

    SELECT

        ddips.index_type_desc,

        ddips.page_count,

        ddips.record_count,

        ddips.max_record_size_in_bytes,

        ddips.ghost_record_count,

        ddips.forwarded_record_count

    FROM sys.dm_db_index_physical_stats(

        DB_ID(), OBJECT_ID(N'dbo.Test', N'U'), NULL, NULL, 'DETAILED') AS ddips

    WHERE

        ddips.alloc_unit_type_desc = N'IN_ROW_DATA'

        AND ddips.index_level = 0;

    GO

    -- Buffer pool pages before any changes

    SELECT 

        dobd.[file_id],

        dobd.page_id,

        dobd.page_type,

        dobd.row_count,

        dobd.free_space_in_bytes,

        dobd.is_modified

    FROM sys.partitions AS p

    JOIN sys.allocation_units AS au 

        ON au.container_id = p.hobt_id

    JOIN sys.dm_os_buffer_descriptors AS dobd ON 

        dobd.allocation_unit_id = au.allocation_unit_id

    WHERE

        p.[object_id] = OBJECT_ID(N'dbo.Test', N'U')

        AND dobd.page_type = N'DATA_PAGE'

    ORDER BY

        dobd.page_id;

    GO

    -- Disable ghost clean-up

    DBCC TRACEON (661, -1);

    GO

    SET STATISTICS IO ON;

    -- Delete three records on the same page

    DELETE dbo.Test

    WHERE ID BETWEEN 1 AND 3;

    SET STATISTICS IO OFF;

    GO

    -- Physical storage after the delete operation

    SELECT

        ddips.index_type_desc,

        ddips.page_count,

        ddips.record_count,

        ddips.max_record_size_in_bytes,

        ddips.ghost_record_count,

        ddips.forwarded_record_count

    FROM sys.dm_db_index_physical_stats(

        DB_ID(), OBJECT_ID(N'dbo.Test', N'U'), NULL, NULL, 'DETAILED') AS ddips

    WHERE

        ddips.alloc_unit_type_desc = N'IN_ROW_DATA'

        AND ddips.index_level = 0;

    GO

    -- Buffer pool pages after the delete operation

    SELECT 

        dobd.[file_id],

        dobd.page_id,

        dobd.page_type,

        dobd.row_count,

        dobd.free_space_in_bytes,

        dobd.is_modified

    FROM sys.partitions AS p

    JOIN sys.allocation_units AS au 

        ON au.container_id = p.hobt_id

    JOIN sys.dm_os_buffer_descriptors AS dobd ON 

        dobd.allocation_unit_id = au.allocation_unit_id

    WHERE

        p.[object_id] = OBJECT_ID(N'dbo.Test', N'U')

        AND dobd.page_type = N'DATA_PAGE'

        AND dobd.is_modified = 1

    ORDER BY

        dobd.page_id;

    GO

    -- View the appropriate page

    --DBCC TRACEON (3604);

    --DBCC PAGE (0, 1, 339408, 3);

    -- Enable ghost clean-up

    DBCC TRACEOFF (661, -1);

Example output, showing page 453648 receiving a versioned forwarded ghost record (click to enlarge in a new tab):

Partial DBCC PAGE output for the highlighted page:

Summary

If you ever wonder why your deletes are so slow, it is worth checking to see if you are suffering from page splitting due to an enabled trigger and a table definition that allows for LOB allocations or ROW_OVERFLOW. Any table with a LOB column (including the max data types) qualifies, as does one with even a surprisingly small number of variable-length columns, as shown in the examples in this post. This is a great reason to avoid using ‘max’ or old-style LOB data types unnecessarily, and to be careful about the maximum length of ‘normal’ variable-length data types too. Remember, it is the potential maximum row size that is important, not the actual row size.

On a related note, remember that deletes on a heap can only deallocate empty pages if a table lock is acquired? A table definition that allows for LOB or ROW_OVERFLOW prevents that optimization too. So, if your heaps are growing despite DELETE WITH (TABLOCKX), check the maximum possible row length! You could also convert them to clustered tables as well, of course, but that’s a quite different debate.

I would like to acknowledge and thank SQL Server MVP Dmitri V. Korotkevitch who first brought the basic issue to my attention with UPDATE queries. I would strongly encourage you to also read his blog entry showing how this behaviour also affects UPDATE queries, resulting in slow performance and excessive fragmentation.

Thanks for reading.

How to Install Master Data Services (MDS) Service Pack 1 (for SQL Server 2008 R2)

mattande — Tue, 16 Aug 2011 21:47:00 GMT

[This post was created by Jason Howell, Senior Escalation Engineer in the SQL Server Support team specializing in MDS]

SQL Server 2008 R2 Service pack 1 was released in July 2011, as well as CU#1 for Service Pack 1 a few days afterwards, and CU #2 just yesterday August 15^th, 2011.

The purpose of this blog is to call out the difference in finding and running the download specifically for Service Pack 1, since it is packaged and extracted differently that Cumulative Updates. Other than the download & extraction steps, it is pretty much the same process as other Cumulative Updates for MDS. Steps 1 and 3 are the main differences here as compared to this this prior blog post.

Installing the Service Pack 1 for MDS requires manual intervention in this release, and running the typical Service Pack 1 setup for the other parts of SQL Server itself is not enough to patch MDS databases and websites.

If your server does not have an existing MDS installation, you can install a fresh copy of MDS following these steps in MSDN: Installing and Configuring Master Data Services You can use the SP1 MSI to install a fresh copy of MDS if needed.

If you already have a copy of MDS up and running, you can patch the existing instance with Service Pack 1 updates.

Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

1. Download SQL Server 2008 R2 Service Pack 1

The first step is to download the service pack.

You can use this link to download the English version, or change the language as needed. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727

For example get the file SQLServer2008R2SP1-KB2528583-x64-ENU.exe

However, wait until you have read Step 3 to run the download.

A. It’s worth noting that the MDS Service Pack 1 download package looks different than the CU downloads.

Service Pack 1 does not have a specific MDS download like the CU’s downloads have. Pick the SP1 for x64.

Note: The KB for 2008 R2 Service Pack 1 (KB2528583) is located here: http://support.microsoft.com/kb/2528583

B. What’s in SQL Server 2008 R2 Service Pack 1?

Service Pack 1 includes fixes for SQL Server 2008 R2 CU1 through CU6 plus a few other fixes that are not in the RTM CU’s. Read the Kb for an exact list of the additional fixes not in CU1-CU6.

Moving forward, fixes made in the RTM cumulative updates are also ported forward to the next subsequent cumulative update for SP1. However, fixes made to the SP1 cumulative updates will NOT be ported back to the RTM unless there is express need to do so. This makes SP1 updates favorable moving forward to get the most fixes.

The green cumulative updates for MDS are included in Service Pack 1. The orange items below are carried forward in CU#1 for SP1. To get the latest updates, then use SP1 CU2 instead of SP1 itself. The list grows every 2 months, so refer to this list if in doubt which one is latest.

RTM & Updates
- 10.50.1600.1 Release to Manufacturer – May 10, 2010
- 10.50.1617.0 RTM Security Patch MS11-049 (2494088) June 14, 2011
- 10.50.1702.0 RTM CU #1 (981355) May 18, 2010
- 10.50.1720.0 RTM CU #2 (2072493) June 21, 2010
- 10.50.1734.0 RTM CU #3 (2261464) Aug 16, 2010
- 10.50.1746.0 RTM CU #4 (2345451) Oct 18, 2010
- 10.50.1753.0 RTM CU #5 (2438347) Dec 20, 2010
- 10.50.1765.0 RTM CU #6 (2489376) Feb 21, 2011
- 10.50.1777.0 RTM CU #7 (2507770) Apr 18, 2011
- 10.50.1790.0 RTM CU Security Patch MS11-049 (2494086) June 14, 2011
- 10.50.1797.0 RTM CU #8 (2534352) June 20, 2011
- 10.50.1804.0 RTM CU #9 (2567713) Aug 15, 2011
- etc…
SERVICE PACK 1 & Updates
- 10.50.2500.0 SP1 Release to Web (2528583) July 13, 2011 (contains RTM CU6 + others)
- 10.50.2769.0 SP1 CU #1 (2544793) July 18, 2011 (contains RTM CU7 - RTM CU8)
- 10.50.2772.0 SP1 CU #2 (2567714) Aug 15, 2011
- etc…

C. Instead of SP1 itself, you can alternately download CU2 (or later) for Service Pack 1

Instead of SP1 itself, you could run CU1 or CU2 (or later CU) for SP1 if you have business requirements to do so. There is a specific MDS file available for download for each CU, which contains only the piece to patch Master Data Services.

Since the MDS download contains a *.msi, it is unlike the rest of SQL feature area CU’s which use *.msp patches that require SP1 as a prerequisite to installing. Therefore you could use the SP1 CU2 file for a fresh install of MDS.

Cumulative Update 1 for SP1: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2544793&kbln=en-us
Cumulative Update 2 for SP1: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2567714&kbln=en-us
Future SP1 updates will be linked from this article: http://support.microsoft.com/kb/2567616

When you fill out the request page, as shown above the hotfix website will send you an email with the link to download the CU update.

2. MDS Preparations – stop current activity, backup your MDS Database, check the starting version

The upgrade process is two-fold: First, Upgrade the binaries. Secondly, upgrade the database schema

Therefore, before we begin, let’s stop all current activity so that we have a clean and safe copy of the MDS database in a consistent state, and make a backup of the MDS database and transaction log to be sure we have a safe copy just in case.

A. Stop the IIS web site or application pool.

This will keep users out of the system while we do the upgrade maintenance. You may want to alert the MDS users about the scheduled maintenance according to your businesses' change control processes.

Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.

B. Stop any query or maintenance operations

Such as scheduled jobs, reports, or custom applications that interact with the MDS database directly.

You could stop your SQL Agent service temporarily if unsure about scheduled jobs that might affect MDS data.

C. Backup the MDS database and the transaction log

You get to pick the name of your MDS database when you originally set it up with the Configuration Manager, so your database name may vary.

If you use FULL recovery model on the MDS database, you can back up the transaction LOG also.

Use a TSQL query or SSMS backup as you please.

BACKUP DATABASE MDS TO DISK='C:\mybackups\mds_before_sp1.bak'

BACKUP LOG MDS TO DISK='C:\mybackups\mds_log_before_sp1.trn'

Object Explorer in SQL Server Management Studio:

D. Query mdm.tblSystem in the current MDS database.

Review value for SchemaVersion and note the value, so you can compare the value after you apply the MDS update to verify that the value has been increased. The SchemaVersion may vary based on the build you are starting with. Some MDS Patches do no schema upgrades, but some do. Depending on which version you start from, Service Pack 1 may or may not upgrade the schema.

USE MDS
GO
SELECT * FROM mdm.tblSystem

3. Run the Service Pack 1 executable and msi’s

The SP1 download SQLServer2008R2SP1-KB2528583-x64-ENU.exe is a self-extracting zip. You can run SP1 for all of SQL if you want (see step 3A) or manually extract just the Master Data Services *.msi if needed (see step 3B).

Double-clicking the .exe extracts SP1 to a temp folder on the root of the drive

After extracting, it will prompt you with security UAC to run the service pack 1 setup.

A. Option 1: Run SP1 for all SQL Server feature areas first, then find the MDS msi.

If you need to patch all of the features of your SQL Server with Service Pack 1, follow the normal steps in the GUI.

Running SP1 will extract the MasterDataServices.msi file but it will not run it automatically.

After you have finished patching SQL Server 2008 R2 with Service Pack 1, you can then find the MSI for MDS in the setup bootstrap folder. It’s kept here for caching purposes:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache\KB2528583\ServicePack\1033_enu_lp\x64\setup\ masterdataservices.msi

On my computer it is in this folder, but I imagine the language (1033=English) may vary based on which SQL locale you have used in the installation.

Within that folder, click the MSI to run the MDS SP1 setup masterdataservices.msi

B. Option 2: Manually Extract SP1 if you do not need to patch any other parts of SQL.

If you don’t need to patch the other features of SQL Server, or maybe you need to just patch MDS as a separate step, you can manually extract the Service Pack 1 and get to the MSI. Several 3rd party compression tools let you right click on the .exe and extract it to a folder of your choosing as well.

From a command line, you can run:

SQLServer2008R2SP1-KB2528583-x64-ENU.exe /extract

Find the MDS msi in the specified location, in the subfolder:
<Extracted folder>\1033_enu_lp\x64\setup\masterdataservices.msi

4. Run the masterdataservices.msi manually (like any other MDS update)

Run through the installation until it completes

Note: If you start the MDS website at this point and visit it, you may get a version incompatible error. This is expected behavior when the schema version increases because the MDS binaries are upgraded, but the database is not yet upgraded. Users cannot use MDS yet if you see this message.

Client Version: 10.51.2500.0
Database Version: 1.0.0.0
The Client version is incompatible with the Database version. Please ask your administrator to upgrade the Client components or the Database components or both.

Note the binaries and scripts are upgraded already to 10.50.2500.0 (the build number of R2 Service Pack 1)

Binaries are the *.exe or *.dll in the Master Data Services installation folders such as

C:\Program Files\Microsoft SQL Server\Master Data Services\Configuration\

C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin

When you right click on them in Windows Explorer, and view the properties, you can check the file version:

5. The Master Data Services Configuration Manager may need to run to complete the MDS database schema upgrade.

A. Run Configuration Manager

It will try to run automatically after the setup completes, or you can run it later from the start menu. Complete the additional steps therein to upgrade your MDS Database and your MDS Websites.

B. Connect and select your database

In the MDS Configuration Manager, click the [Select Database…] button.

Get connected to your MDS database on whichever SQL Server it may reside on. Note if you utilize a named instance, you have to manually type in the servername\instancename because the configuration manager forgets which instance to connect to every time.

C. Upgrade your MDS database if needed

If you see the below red error text "This database requires an upgrade. You cannot change system settings until the database is upgraded" then you need to upgrade your MDS database.

Sometimes you do not see this text, if you already had the schema upgrades are already in place from a prior cumulative update patch, then no further action is required, skip to step 6.

If you get the error after you picked the database server and the existing database name, in the MDS Configuration Manager, use the [Upgrade Database…] button to upgrade the database.

D. Click through the Upgrade Database Wizard

When you run the Upgrade, you will see several screens in the upgrade wizard. It will show which schema upgrades need to happen. It’s easy, so I’ll just show the progress bar:

E. Now check the database SchemaVersion in your MDS database by running the query to confirm the schema is now upgraded.

I expect 1.0.7.0 for Service Pack 1.

USE MDS
GO
SELECT * FROM mdm.tblSystem

6. Start the MDS website and Application Pool and make sure it works.

A. Start the web site and application pools

After the database upgrade is complete, your users can start using the MDS website again. Start the Website and or Application Pools, and remember to enable any SQL Agent jobs or other maintenance you may have disabled for the upgrade timeframe.

B. Browse through your MDS website to make sure the models are present, and that things seem normal.

You can get IIS to help you if you forgot the URL:

You can confirm the version number in the Help menu (blue question mark on the upper right of on the web page).

The MDS website shows the version as 10.51.2500.0 for Service Pack 1.

SQL Server 2005 SP4 is here!

AaronBertrand — Sat, 18 Dec 2010 03:59:00 GMT

Yes, the day has finally arrived, and a couple of weeks ahead of schedule. Typically when Microsoft promises a release in Qx or Hx, the software comes on the last or second last day of that quarter or half. This year, we get an early Christmas present: SQL Server 2005 SP4.

Downloads

To download SP4, go to this link:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b953e84f-9307-405e-bceb-47bd345baece

If you are looking for the Express versions of SP4, you can get Express, Express with Tools, and Express with Advanced Services at the following URL (though they don't really do a great job of describing which file(s) may be most appropriate for you):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=26435597-b28e-4568-9d16-017bdf47abdc

If you want Express that can install on 32-bit or 64-bit systems, download SQLEXPR.EXE. If you know you will only install to 32-bit systems, download the smaller SQLEXPR32.EXE. (The only difference is the size of the package - I can hear the peanut gallery now. In all seriousness, there is no such thing as a 64-bit version of SQL Server Express for SQL Server 2005 - that option first arrived with SQL Server 2008.) If you want Express with Tools, use SQLEXPR_TOOLKIT.EXE, and if you want Express with Advanced Services, use SQLEXPR_ADV.EXE.

Fixes

To review the fixes in SP4, see the following Knowledge Base article:

http://support.microsoft.com/kb/2463332

I am not sure why the list is so short - SP4 does contain all of the fixes in Cumulative Updates 1 through 11 for Service Pack 3, and there are definitely more than that handful. I think this makes the service pack look a lot less useful than it actually is, especially for people who are still on SP3 or its earlier cumulative updates. Here is the full list of 11 cumulative updates, to make it a little easier to generate the full list of fixes or to find a particular fix you want to make sure is included in the service pack (see below for information about fixes in CU #12):

CU #11 (9.00.4309 - 2010-08-16) : KB #2258854
CU #10 (9.00.4305 - 2010-06-21) : KB #983329
CU #9 (9.00.4294 - 2010-04-19) : KB #980176
CU #8 (9.00.4285 - 2010-02-15) : KB #978915
CU #7 (9.00.4273 - 2009-12-21) : KB #976951
CU #6 (9.00.4266 - 2009-10-19) : KB #974648
CU #5 (9.00.4230 - 2009-08-17) : KB #972511
CU #4 (9.00.4226 - 2009-06-15) : KB #970279
CU #3 (9.00.4220 - 2009-04-20) : KB #967909
CU #2 (9.00.4211 - 2009-02-16) : KB #961930
CU #1 (9.00.4207 - 2008-12-19) : KB #959195

Most importantly (at least IMHO), this service pack finally addresses the MSXML6 issues that have plagued Windows XP SP3 users since at least February of 2009. From the fix list:

When SQL Server 2005 SP3 is applied on a machine that has a later version of MSXML6 installed, the service pack setup fails. This issue occurs when you upgrade Windows XP SP2 to Windows XP SP3 and then apply SQL Server 2005 SP3. To fix this issue, apply SQL Server 2005 SP4.

Build Number

The build number for SP4 is 9.00.5000. (This "round number" build is becoming a favorite practice not just within the SQL Server team but across a lot of other Microsoft divisions as well.) Here is the result of "SELECT @@VERSION;" on my local Express instances, just for fun (notice that even though I am running x64 there is nothing in there that tells you whether the operating system is 32-bit or 64-bit):

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    Dec 10 2010 10:56:29 
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 6.1 (Build 7600: )

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    Dec 10 2010 10:56:29 
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition with Advanced Services on Windows NT 6.1 (Build 7600: )

Should you update?

You will still need to wait one Cumulative Update (CU) cycle to restore all of the fixes from SP3 CU #12. As with previous SP releases in the 2008 branches, if you've already applied CU #12 to your SQL Server 2005 SP3 instance (SELECT @@VERSION will show 9.00.4311 or greater, but less than 9.00.4912 - the build for the Service Pack 4 CTP), and you are relying on fixes in that build, you should hold off on SP4 until the first CU for SP4 is released. Usually this occurs within about a month of the service pack, but given the holidays, at this point I would expect it mid-February.

This will almost certainly be the last service pack for SQL Server 2005 - from this point forward all you're likely to see are cumulative updates to the SP3 and SP4 branches and, roughly a year from today, mainstream support will only need to maintain the SP4 branch. You can read more about this in the following blog post from the CSS blog:

Mainstream vs Extended Support and SQL Server 2005 SP4: Can someone explain all of this?

Downloading and Installing SQL Server 2008 R2 Master Data Services (MDS) Cumulative Updates

mattande — Thu, 26 Aug 2010 00:13:00 GMT

(This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

Note: This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature. SQL Server 2012 now ships Master Data Services on the DVD with the other SQL Server 2012 features and is patched by the same Cumulative Updates and Service Packs for SQL Server 2012 and the downloads are no longer specific to MDS for separate download .

Recently, we released the first cumulative update (CU) for Master Data Services (MDS). This blog entry explains how to download and install that CU, as well as subsequent CUs for MDS.

Each Master Data Services (MDS) cumulative update is released as a stand-alone installer for SQL Server 2008 R2 Master Data Services. An update can be installed on a server without a previous installation of MDS, or it can be applied over an existing RTM installation or previously updated installation of MDS.

Note MDS is a feature of the versions and editions of SQL Server listed in Setup Requirements (Master Data Services) in SQL Server Books Online (BOL). Any computer where you install MDS must be licensed accordingly. For more information, refer to the End User License Agreement (EULA).

To download an MDS Update:

At the top of the Knowledge Base page for the download, click View and request hotfix downloads.
Read the terms of the agreement and click I Agree if you agree and want to continue.
On the Hotfix Request page, select the necessary file to obtain this update.
Specify an e-mail address to e-mail this update to, and type the characters in the picture. Then click Request hotfix.
On the Hotfix Confirmation page, review the information and wait for the hotfix e-mail to arrive.
From the hotfix e-mail, open the link and download the .Zip file.
Extract the .Zip file and provide the password included in the hotfix e-mail. MasterDataServices.msi will be extracted to the location you specify.

Installing MDS cumulative updates on a server without an existing MDS installation

If your server does not have an existing MDS installation, you can follow the process documented in Installing and Configuring Master Data Services in SQL Server BOL. However, instead of running MasterDataServices.msi from the product media as directed in the documentation, run MasterDataServices.msi from the location where you extracted it in the download process above. Then create and configure the MDS database, Master Data Manager Web application, and MDS Web services as documented in SQL Server BOL.

Applying MDS cumulative updates to an existing MDS installation

If you apply an MDS cumulative update to an existing installation of MDS, you must run the installation package and then use Master Data Services Configuration Manager to upgrade the database.

Important Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

Before you start the upgrade process:

Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.
Stop any operations, such as scheduled jobs, that affect the MDS database.
Query mdm.tblSystem in the current MDS database. Review value for SchemaVersion and compare the value after you apply the MDS update to verify that the value has been increased.
Take a current backup of the MDS database. If you are using the full recovery model, take a log backup immediately before you upgrade the database. If you are using the simple recovery model, take a full or differential database backup. For more information about backups, see “Backup Overview (SQL Server)” at http://msdn.microsoft.com/library/ms175477(SQL.105).aspx.

To apply an MDS cumulative update:

On the server where you want to install the update, navigate to the location where you extracted MasterDataServices.msi and double-click it.
The following message appears: A lower version of this product has been detected on your system. Would you like to upgrade your existing installation? Click Yes to proceed with the upgrade process.
The Microsoft SQL Server 2008 R2 Master Data Services installation wizard opens. Follow the instructions in the wizard to configure installation options and then click Install.
After installation completes, Master Data Services Configuration Manager opens so you can upgrade the MDS database.
Click Databases, and then click Select Database. Connect to the database you want to upgrade. Once the database is selected, the following message appears: This database requires an upgrade. You cannot change system settings until the database is upgraded.
Click Upgrade Database. The Upgrade Database Wizard opens. Follow the instructions in the wizard to upgrade the database and then click Finish.
To verify that the database upgrade was successful, connect to the MDS database and query mdm.tblSystem. The value for SchemaVersion should be increased and match the value as specified for the individual cumulative update.

If the update was unsuccessful, open Control Panel and use Programs and Features to uninstall Microsoft SQL Server 2008 R2 Master Data Services and then re-run the steps in this procedure.

After you apply the cumulative update and upgrade the database, start the Master Data Manager Web application, MDS Web service (if enabled in your environment), and any operations that you suspended prior to the upgrade process. Verify that your environment works as expected before you allow users to access the environment.