We have a visit from MySpace database team in our lab this week. I have used this opportunity and asked Kevin Stephenson, MySpace’s Senior Database Developer, about “pain points” he encounters while doing his everyday job supporting SQL Server. He brought up a problem of efficient ordered delete. He needs to delete large portions of older custom log entries periodically and he needs to delete them in certain order. He also knows that it is efficient to break huge update operations into more small ones to allow truncating log. Kevin is aware of several methods. He immediately illustrated his problem on a small example:
drop table t1
create table t1 (a int primary key, b char (100))
declare @i int
set @i=1
SET NOCOUNT ON
while (@i<100000)
begin
insert into t1 values (@i,'x');
set @i=@i+1
end
set statistics io on
set statistics time on
delete from t1 where a in (select top (10000) a from t1 order by a);
set statistics time off
set statistics io off
And I got the following result messages:
Table 't1'. Scan count 2, logical reads 30564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 20152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 80 ms.
The delete query in the above script has the following plan (before performing the delete):
We can see the table t1 is accessed twice; the lower branch is performing the “select top (10000) a from t1” subquery and joining the result to the second occurrence of the same table t1. So we need to read the “deleted” portion of the table twice to identify the rows to delete and then once more to perform the delete. The question was, is it possible to perform the same operation more efficiently?
The answer is using a tip – a view with ORDER BY. You cannot use ORDER BY in a view definition unless you use also TOP in the SELECT clause in the same view. And we know we want to delete 10000 rows. So the view definition is as follows:
create view v1 as (select top (10000) * from t1 order by a)
and we can delete the “top” rows using simply
delete from v1
The query plan for this delete is much simpler.
and the I/O and cpu statistics demonstrate the improvement:
Table 't1'. Scan count 1, logical reads 654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 75 ms.
Kevin Stephenson and Lubor Kollar
In data warehouses, it is quite common that fact records arrive with a source system key that has not yet been loaded in the dimension tables. This phenomena is known as “late arriving dimensions” or “early arriving facts” in Kimball terminology.
When you see a fact record that cannot be resolved to a dimension surrogate key, the typical solution is this:
- Create a dummy member in the dimension table using the source system key
- Assign a surrogate key to this dummy member
- Use the newly create surrogate key and assign it to the fact record
If you use T-SQL to load the data warehouse, it means you have to pass over the input fact rows twice. First, you have to discover which keys are not present in the dimension (and create surrogates for them). Second, you will have to look at the input data again and use the newly generated surrogate keys to load the the fact table.
Using Integration Services, early arriving facts can be populated with just one pass over the source rows, which means less read I/O operations. Nice!
In project Project REAL, a script component is used to achieve this effect. If many of your dimension have early arriving facts, this creates a lot of copy/paste code. There is a cleaner solution that does not use script components.
There is a way handle early arriving facts without relying on script components. It is best illustrated with an example. Let us create these three tables:
/* The input table */
CREATE TABLE Stage_Fact
(
NK_A CHAR(10) NOT NULL /* The late arriving source system key */
)
/* The late arriving dimension table */
CREATE TABLE Dim_A
(
SK_A INT PRIMARY KEY IDENTITY(1,1) /* The surrogate key*/
, NK_A CHAR(10) NOT NULL /* The natural, source system key */
)
/* The final destination table */
CREATE TABLE Fact
(
SK_A INT NOT NULL /* Surrogate key from dimension */
)
Now, use this script to generate 16M rows in the input table and create a 9000 row dimension table:
/* Create some staging data */
INSERT Stage_Fact WITH (TABLOCK)
SELECT RIGHT(REPLICATE('0', 10) + CAST(K AS VARCHAR(10)), 10) AS NK_A
FROM (SELECT ABS(binary_checksum(*) % 10000) AS K
FROM sys.trace_event_bindings eb1
CROSS JOIN sys.trace_event_bindings eb2) AS stuff
/* Populate Dim_A with 90% of the keys from the fact table */
INSERT Dim_A WITH (TABLOCK) (NK_A)
SELECT DISTINCT NK_A FROM Stage_Fact
WHERE NK_A < '0000009000'
With this data, there will be 1000 late arriving dimension keys in Stage_Fact in around 1.8M rows. When we see a non-matched key in Stage_Fact, we want to generate a new surrogate key in Dim_A. But here is the catch: We only want to generate the surrogate once, and we do NOT want to do a roundtrip to the database the second time we see the same key.
Project Real uses a .NET hash table to track the generated keys and perform quick lookups the next time we see the key. But, we already have a fine hash table available without using script components: the lookup transformation. Let us see how we solve the early arriving fact problem with Integration Services, au natural:
The non-matched rows from Lookup SK_A go into the second lookup (New SK_A Cache). New SK_A Cache is where we want to handle the early arriving facts.
First, configure New SK_A Cache as a partial cache:
Now, we play a clever trick: Whenever a partial lookup cache first receives a non-matched row, it will call a SQL statement and fetch data to populate the lookup cache. The default is a SELECT statement, but it does not have to be a SELECT statement. We could replace it with a stored procedure that returns the same result as the SELECT. Actually, let us do exactly that:
Now, the FIRST time the partial lookup cache sees a early arriving fact, it will call Generate_SK_A. I have mapped the NK_A (the source system, natural key) column to the input parameter. To finish the trick, we just have to create a simple stored procedure that uses NK_A to lookup SK_A (the Surrogate Key), and if not found, create a new key:
CREATE PROCEDURE Generate_SK_A
@NK_A CHAR(10) /* The key to find a surrogate for */
AS
SET NOCOUNT ON
/* Prevent race conditions */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* Check if we already have the key (procedure is idempotent) */
DECLARE @SK_A INT
SELECT @SK_A = SK_A
FROM Dim_A
WHERE NK_A = @NK_A
/* The natural key was not found, generate a new one */
IF @SK_A IS NULL BEGIN
INSERT Dim_A (NK_A) VALUES (@NK_A)
SET @SK_A = SCOPE_IDENTITY()
END
/* Return the result.
IMPORTANT: must return same format is the SELECT statement we replaced */
SELECT @SK_A AS SK_A, @NK_A AS NK_A
Simple isn’t it?... No need to use any .NET script components here. Have a look at the attached files to study the technique further and you will be handling early arriving facts elegantly in no time.
Proper partitioning can improve dramatically the writeback process when dealing with large data sets
Author: Nicholas Dritsas
Reviewers: Richard Tkachuk, Akshai Mirchandani
CASE:
Customer is using SQL 2008 and SSAS's writeback abilties to do 52-weeks rolling sales forecasting. Their fact table has reached 250 million rows and they want to add 40 million records into the writeback table each week using weight allocation when updating 2 measures and executing 20,000 update cube statements per week. This process takes 11 hours currently using MOLAP writeback storage. They have 5 dimensions, with the biggest been the item dimension with 130,000 members and 7 levels. They do not have properly defined aggregations or attribute relationships yet. Overall, as number of records in writeback table is increased, performance is progressively slower. The number of records in a writeback partition will have an impact on both query performance and writeback performance.
SUGGESTIONS:
Suggestion was around proper partitioning. Transferring rows from the writeback partition into multiple regular partitions is likely to help only from the standpoint of standard partitioning optimization techniques. All relevant partitions (and not just the writeback partition) will be scanned for the deltas. The Writeback partition usually doesn’t have good partition slices, and by distributing records to other partitions that have good partition slices you may get better performance. For example, if you move all updates for Year 2008 into the Year 2008 partitions – now any subsequent updates to Year 2009 cells will avoid unnecessarily scanning all of those records.
The number of records in the writeback partition will have an impact on both query performance and writeback performance – particularly if the partition is ROLAP. However, if this partition is MOLAP then it should behave very much like a standard MOLAP partition for most situations – although 250 million rows will still probably result in perf slowdowns to calculate the deltas at the leaf level.
SOLUTION:
By doing the following operations, we improved writeback performance from 11 hours to 2 hours 46 minutes.
1. We introduced partitions to the fact and writeback tables. We aligned them so they correspond to each other. We added partitions for years 2005, 2006, 2007 and 2008 plus 9 partitions for 9 weeks todate for 2009. So, a total of 13 partitions for fact and 13 for writeback tables. The 2005, 2006, 2007 and 2008 partitions were done using a WHERE clause against the main single table. For 2009, we have a separate physical table per week. ETL process will be creating a new weekly table and SSAS partition each week.
2. We applied time slicer to all partitions, even if they were MOLAP.
Profiler result for one update cube statement.
A. Before : When writeback was executed to 200908, update cube accessed three partitions:
1. 200908 Partition
2. All Partition
3. Writeback Partition
B. After : When writeback is executed to 200909, update cube accessed two partitions:
1. 200909 Partition
2. 200909 Writeback Partition
Author: Denny Lee
Reviewers: Prem Mehra, Kevin Cox
With the terabytes of data that are being stored today - truly representing the data explosion that we've always talked about - it's becoming more and more difficult to provide an audit trail or utilize tools that can ensure that your SQL environment is compliant. To help resolve this problem, we've introduced some guidance in the form of our SQL Server Reaching Compliance Guide. We have built up more resources and case studies so you can reference them to help you build a compliant SQL Server environment. These resources include:
And recently added is SQL Server helping with HIPAA compliance in action:
As a teaching hospital of Harvard Medical School, and the largest member of the CareGroup Healthcare System, Beth Israel Deaconess Medical Center needs an IT infrastructure with auditing capabilities to help it remain in compliance with regulations including those of the Health Insurance Portability and Accountability Act (HIPAA). The organization has more than 2 terabytes of information, including data warehouses, clinical applications, and other solutions hosted using Microsoft® SQL Server® database software. BIDMC is upgrading to Microsoft SQL Server 2008 Enterprise to take advantage of advanced auditing and transparent data encryption features in the latest version of SQL Server to help it meet HIPAA and other regulatory requirements. As early adopters, CareGroup IT professionals helped Microsoft create the new SQL Server 2008 Compliance Software Development Kit.
As well, for all of your interested in evidence that SQL Server can be used for PCI compliance as per the Payment Credit Industry Data Security Standards (PCI DSS), check out:
Author: Nicholas Dritsas
Reviewers: Thomas Kejser, Stuart Ozer
In SSAS 2005 and later, there is a dimension property called ProcessingGroup. It has two values; ByAttribute (default) and ByTable.
When you use ByAttribute, SSAS will send a SELECT DISTINCT query to the relational engine for each attribute PLUS, at the end, an additional SELECT DISTINCT that combines all the attributes plus key. As you can imagine, if you have several attributes and a very large dimension table, this process can take a while. Our customer in this case has a 100 million members dimension table in Oracle.
Using the option ByTable, SSAS sends one table scan query to the relational engine and temporarily caches in memory the results. This can work well only if you have enough memory for the cache (see related blog for potential issues here).
Now, even if you have enough memory, you may encounter a different problem. If some of the attribute keys are strings, you may hit the 4gb limit of SSAS for the string store. This limitation is faced typically on an attribute level, if you process ByAttribute. But, since we process ByTable here, the limit applies to the whole table. So, if you have a very large dimension table, you will hit this limit even if no single attribute size is more than 4gb.
There are a few workarounds, but, the problem of having a very large dimension with many attributes that have string keys is still hard to manage currently:
1. ByAttribute and configure relational engine to perform better by adding indexes. For more details, please look at the SQL Server 2008 Analysis Services Performance Guide here.
2. ByTable and reduce the dimension string sizes at the key.
There is plenty of material available to help you upgrade to SQL Server 2008. This blog is intended as a short list for the most useful guidance that I have found. And you may have different experiences upgrading from SQL Server 2000 and SQL Server 2005 so please take the time to do proper preparation work and advanced studying. And test your upgrades before you do it for real in production.
There is one bug that you need to know about that potentially affects your upgrade experience. If you are planning on using backup/restore to move your database over to your new SQL Server 2008 environment, then you will need to start with the latest Cumulative Update. Get that installed before you do the restore as it contains a fix you will need. The problem is that the log replay after the restore may take a very long time, up to 10x the time you expected. It only happened when log files were very large and had many VLFs (virtual log files). Detach/Attach method is not affected. This is the KB article for the error: http://support.microsoft.com/kb/967178/EN-US/. The fix is in CU4, but you should get the latest one available (as of the time of this writing it is CU4).
Here are the minimum steps I recommend:
Start with the SQL Server 2008 upgrade advisor: http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en. Read all output in detail because it can be easy to miss a small warning that could be important.
Then get the 490 page document available on the web that contains all the upgrade best practices. http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en. It has a good section on Windows failover clustering.
Another great site is http://msdn.microsoft.com/en-us/library/cc936623.aspx. Although there is some overlap with the 490 page document, it contains more detail for some sections, especially clustering. This site is an absolute requirement to use if you have Windows failover clustering.
Specialty Links:
See my other blog on this site that I co-wrote with Glenn Berry on how to use database Mirroring to minimize downtime if you are upgrading from SQL Server 2005. http://blogs.msdn.com/sqlcat/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx
Moving DTS 2000 packages: http://blogs.msdn.com/sqlcat/archive/2009/03/20/remember-to-move-dts2000-packages-when-upgrading-msdb-from-2005-to-2008.aspx
Motivation to upgrade if you are using failover clustering: http://sqlcat.com/top10lists/archive/2008/11/20/six-failover-clustering-benefits-realized-from-migrating-to-sql-server-2008.aspx
Kevin
Some customers prefer to upgrade to SQL Server 2008 by detaching all databases from their SQL Server 2005 instance and then reattaching them to a completely new SQL Server 2008 instance.
If you choose this upgrade strategy, you should be aware that you cannot detach an msdb database from a SQL Server 2005 instance and reattach it to a SQL Server 2008 instance. If you try, you will get an error message. Backup/restore will not work for msdb in this upgrade scenario either.
The msdb database may contain objects that are relevant to your installation – for example agent jobs and DTS 2000 packages. When upgrading, you should make sure you move all relevant objects in msdb. You can use tasks in Integration Services to move most of these objects. However, DTS 2000 packages cannot be moved using the Integration Services tasks. Instead, you must manually move the contents sysdtspackage.
The CSS team has a great blog entry describing how to move DTS 2000 packages to a SQL Server 2008 instance: How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008)
Thomas and I updated the SQL Server 2008 Analysis Services Performance Guide principally to address features available in the latest release. But there was one important change related to partition size driven by changes in hardware. The earlier version of the performance guide for SQL Server 2005 Analysis Services Performance Guide stated this:
“In general, the number of records per partition should not exceed 20 million. In addition, the size of a partition should not exceed 250 MB.”
This guidance is reasonable but implies a strict size limit that should not be exceeded when in fact no such hard and fast limit exists. Query performance scales well as partition sizes exceed 20 million rows or 250MB, so that isn’t the primary concern here. Rather, the main criterion is manageability and processing performance. Partitions can be processed in parallel, so the more there are the more can be processed at once (assuming that I/O can keep up). Furthermore, the smaller a partition is the less memory is required for the ProcessIndex step to build the indexes and aggregations. Therefore, the likelihood of spilling temporary files to disk during processing (and the associated performance hit) decreases with smaller partitions.
So lots of partitions is good, right? Well, not always. The more partitions you have the more things you have to manage. That carries a cost. Also, partitions have some metadata and when the number exceeds several thousand, the cost of managing this metadata becomes apparent.
There is much more detail in the updated performance guide on best practices around partition sizing, but there are some simple rules of thumb to keep in mind. These are (in order or priority):
a) Keep the number of partitions fewer than a few thousand
b) Get the most out of your hardware and process as much data as possible by processing multiple partitions in parallel
c) Avoid spilling to disk when processing indexes and aggregations (the ProcessIndex step of the processing stage)
d) Target 20 million rows or 250 MB per partition
Some simple examples:
- If you’re building a 1TB cube, the first rule takes precedence and you should exceed the 250 MB suggestion to keep the partition count down. In this case, sizing partitions between 500MB to 1 GB is reasonable.
- If records come in at the end of each day and history does not change you can get the quickest processing with daily partitions for recent data and monthly or annual partitions for historical data.
- If you have to reprocess a good portion of your cube on some regular basis, the size of partitions can be driven by how many can be processed in parallel keeping CPU and/or I/O usage as high as possible.
Author: Nicholas Dritsas
Reviewers: Lubor Kollar, Michael Thomassy, Sanjay Mishra
SQL Server 2008’s latest feature pack, that can be found here, contains a new Oracle connector by Attunity that supports Oracle versions 9.2.0.4 and higher. When you use this connector in SSIS 2008 to send data into Oracle, you may not be able to use fast load if some Oracle system views are missing. This is the case in Oracle 9.2.0.7, but, not 10.2.x.
When we tried to use fast load option using Attunity’s Oracle destination task to 9.2.0.7, we received the following SSIS error message:
[Oracle Destination [268]] Error: Fast Load error encountered during PreLoad or Setup phase. Class: OCI_ERROR Status: -1 Code: 0 Note: At: ORAOPRdrpthEngine.c:735 Text: ORA-00942: table or view does not exist.
We also switched on tracing at the Oracle instance level, and then scanned the trace files looking for exceptions. This is what we found:
PARSE ERROR #4:len=207 dep=0 uid=26 oct=3 lid=26 tim=63762441650753 err=942
SELECT DECODE(COUNT(*), 0, 0, 1) FROM SYS.LOADER_NESTED_VARRAYS
WHERE TABLE_NAME = :tname AND TABLE_OWNER = :owner
PARSE ERROR #4:len=51 dep=0 uid=26 oct=3 lid=26 tim=63762441654278 err=942
SELECT VALUE FROM SYS.LOADER_SKIP_UNUSABLE_INDEXES
XCTEND rlbk=0, rd_only=1
The loader_ (sql loader) views are usually created when the catalogue script has been executed. The specific script that creates the loader_ objects is (.\rdbms\admin\catldr.sql).
In this case, these two views causing the errors don't exist in the catldr.sql file for 9.2.0.7 - We have confirmed this to the case for both Unix (aix 51) and Windows Server 2003.
However - both these views do exist in the 10.2.0.2 catldr.sql file for Windows, i.e.:
rem
rem $Header: catldr.sql 31-aug-2004.15:07:06 msakayed Exp $ ulview.sql
rem
Rem Copyright (c) 1990, 2004, Oracle. All rights reserved.
Rem NAME
Rem catldr.sql
Rem FUNCTION
Rem Views for the direct path of the loader
Rem NOTES
Rem This script must be run while connected as SYS or INTERNAL.
Rem MODIFIED
Rem msakayed 08/30/04 - column encryption support (project id 5578)
Rem rphillip 05/08/03 - Add view to get full attribute name
Rem msakayed 02/10/03 - Add security clause to loader views
Rem rphillip 12/02/02 - Add view to check for nested varray tables
Rem preilly 11/22/02 - Add view to get version of type used for a column
Rem msakayed 11/11/02 - remove hard tabs
Rem msakayed 11/01/02 - Bug #2643907: add LOADER_SKIP_UNUSABLE_INDEXES
We did create these missing views (see script below) on our DEV instance (running 9.2.0.7 on Aix) and the connector now seems to be functioning correctly with the both the fast load (direct path) and the table name access modes.
However, we have noticed some other unusual behavior with the Fast load - direct path method, especially on special characters and space trimming. It seems trailing spaces are removed and special characters may convert to ?. But, if you do not use fast load option, the trailing spaces and special characters are loaded into Oracle fine. We have filed a bug and we follow up with a blog when there is a fix.
Missing system views script (provided as is per Oracle’s 9.2.07 scripts)
CREATE OR REPLACE VIEW SYS.LOADER_NESTED_VARRAYS
(
TABLE_OWNER,
TABLE_NAME
)
AS
select u.name as table_owner, o.name as table_name
from col$ c, obj$ o, user$ u, ntab$ nt
where o.obj# = nt.ntab# and o.owner# = u.user# and
c.obj# = nt.obj# and c.type# = 123 and c.intcol# = nt.intcol#
and (o.owner# = userenv('schemaid')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
/
GRANT SELECT ON SYS.LOADER_NESTED_VARRAYS TO "PUBLIC"
/
CREATE OR REPLACE VIEW SYS.LOADER_SKIP_UNUSABLE_INDEXES
(
VALUE
)
AS
select count(*) as value from v$parameter
where upper(name) = 'SKIP_UNUSABLE_INDEXES'
and value = 'TRUE'
/
GRANT SELECT ON SYS.LOADER_SKIP_UNUSABLE_INDEXES TO "PUBLIC"
/
If you have experimented with cube design in SQL Server 2008, you will probably have run into the new AMO design warnings in Business Intelligence Development Studio 2008 (BIDS). We find that these warnings help customers a lot: they allow them to quickly analyze your cube for classic design mistakes and provide advice on how to correct them. In Analysis Services 2005 you need the best practice analyzer to get the same warnings.
Did you know that you can use BIDS 2008 to connect to Analysis Services 2005 cubes and immediately get the new 2008 AMO warnings feedback on your 2005 design? You can even save the cube back to Analysis Services 2005 with BIDS 2008. If you decide to save back the cube to a 2005 server you should first perform a backup of the 2005 cube.
Please note that you CANNOT use BIDS 2008 to save Integration Services or Reporting Services 2005 projects. If you open an Integration Services 2005 or Reporting Services 2005 project in BIDS 2008 it will be upgraded to the 2008 format instead.
I’m happy to announce availability of an updated version of the SQL Server Partition Management tool – now offering full support for SQL Server 2008 as well as improvements in handling SQL Server 2005. The tool is available along with source code at http://www.codeplex.com/SQLPartitionMgmt. If you’re not already familiar with the tool, it provides a set of commands (at the Command Line or via Powershell) to create a staging table on-demand (including all appropriate indexes and constraints) based on a specific partitioned table and particular partition of interest.
By calling this executable, with parameters, from maintenance scripts or SSIS packages, DBAs can avoid having to ‘hard code’ table and index definition scripts for staging tables. The tool eliminates the challenge of keeping such scripts in synch with changes to partition tables’ columns or indexes. It also provides a fast, single-command shortcut for the operation of quickly deleting all data from a partition.
The latest version supports new features in SQL Server 2008 such as filtered indexes, new data types, and partition-aligned indexed views. Enjoy!
-- Stuart Ozer / SQLCAT
By popular request, the SQL Customer Advisory Team has collected our lessons learned about tuning data loading into a new whitepaper.
We call it the “Data Loading Performance Guide” and you can find it on MDSN: http://msdn.microsoft.com/en-us/library/dd425070.aspx
In this paper, you can find all the tuning tricks we applied to achieve the ETL World Record and a full tuning methodology. We also provide a full overview of the bulk load methods and solutions to common data loading scenarios.
Happy data loading!
At one high volume OLTP project we are involved in, the customer likes to use DB Mirroring to minimize downtime for planned failovers. This usually involves patching either Windows or SQL Server.
The interesting part of this strategy is that they switch to synchronous mirroring just before doing a failover. They would normally like to run with synchronous mirroring but their volume is too high during the day to keep up. So they run asynchronous mirroring until just before they want to switch. They plan these failovers for a lower volume time, usually around midnight.
Here are the steps to take to apply patches:
1. Patch the mirror machine first. If this means that SQL Server needs to be stopped for a while to apply patches, this company is ok with the risk of running without a mirror for a short period of time.
2. Start the mirror machine again and let asynchronous mirroring resume and catch up.
3. Switch to synchronous mirroring, and let it get to steady state.
4. Fail over to the mirror machine.
5. Switch to asynchronous mirroring
6. Patch the principal, which may need a restart.
7. When the original machine is back on line, asynchronous mirroring will start again and eventually catch up. (sometimes 15-30 minutes with this application, which is I/O limited on the mirror side)
8. Switch to synchronous mirroring, and let it get to steady state.
9. Fail back to the original principal machine.
10. Switch back to asynchronous mirroring.
So far we’ve been fortunate that the outages are between 10 and 15 seconds. We know that sometimes mirroring failovers can take longer.
If you have a witness server, you can upgrade that to SQL Server 2008 first, before you upgrade either the principle or the mirror.
In addition to doing Windows patches and SQL Server cumulative updates, this technique was also used to upgrade from SQL 2005 to SQL 2008. We upgraded the mirror first, then upgraded the principal in the same steps as above. One requirement for this scenario is that your SQL Server 2005 instance must be running Build 3215 (SP2 Cumulative Update 5) or better, or else you will not be able to establish a mirroring partnership with SQL Server 2008. The only worry was that there was no way to go back once we failed over to SQL 2008 since mirroring will not work with a SQL 2008 source and a SQL 2005 destination. We thought about creating an additional replicated database from the mirror, but decided to take the risk since we had the luxury of practicing the upgrade on a good set of test servers that were under a simulated production load. If you want to see the detail of how to replicate from a mirrored database, see this link: http://sqlcat.com/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx.
Another recommendation that makes database mirroring much easier to setup with a high volume application is to make sure that both sides of the mirror are running on Windows Server 2008. When you are “preparing the mirror” to set up a mirroring partnership, you have to take a full backup of the principal database, copy the backup file(s) to the server where the mirror database will run, and then restore the database with no recovery on that instance. Depending on your database size, your workload, and your infrastructure, you will probably have to do several transaction log backups of the principal database, which will also have to be copied to the mirror and restored with no recovery. You are essentially racing to catch up with the principal database.
The SMB 2.0 and other network stack improvements in Windows Server 2008 make network file copies between servers that are both running Windows Server 2008 nearly an order magnitude faster than on Windows Server 2003 R2. If you are also running the Enterprise Edition of SQL Server 2008, you should use native backup compression for your full database backup and your transaction log backups. This will make the backups complete more quickly, make the backup files much smaller (which makes them easier to copy to the other server), and will make the restores finish more quickly. You should also make sure that the SQL Server service account on the mirror has the “Perform Volume Maintenance Tasks” right in Windows for that server, so you can use Windows Instant File Initialization. This will make your full restore complete much more quickly. Using Windows Server 2008 and SQL Server 2008 together can cut many hours off the time required to establish a mirror on a high volume OLTP system.
To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):
· Database Mirroring and Log Shipping Working Together: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Implementing Application Failover with Database Mirroring: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring Best Practices and Performance Considerations: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring FAQ: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
· Performance Boost for Database Mirroring: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx
· Troubleshooting Database Mirroring Deployment: http://msdn.microsoft.com/en-us/library/ms189127.aspx
· How to Avoid Orphaned Users With Database Mirroring:
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry
Hope this helps,
Kevin Cox - SQL CAT; Glenn Berry – SQL MVP and DBA.
For the ultimate guide to upgrading to SQL Server 2008, please refer to the SQL Server 2008 Upgrade Technical Reference Guide.
A successful upgrade to SQL Server 2008 should be smooth and trouble-free. To achieve that smooth transition, you must devote plan sufficiently for the upgrade, and match the complexity of your database application. Otherwise, you risk costly and stressful errors and upgrade problems. Like all IT projects, planning for every contingency and then testing your plan gives you confidence that you will succeed. But if you ignore the planning process, you increase the chances of running into difficulties that can derail and delay your upgrade. This document covers the essential phases and steps involved in upgrading existing SQL Server 2000 and 2005 instances to SQL Server 2008 by using best practices. These include preparation tasks, upgrade tasks, and post-upgrade tasks.
- Chapter 1 gives an overview of the technical issues and decisions that are involved in an upgrade to SQL Server 2008, as well as recommendations for planning and deploying an upgrade.
- Chapter 2 addresses issues related to upgrading to SQL Server 2008 Management Tools.
- Chapters 3 through 8 focus on upgrade issues for SQL Server relational databases.
- Chapter 9 addresses upgrading to SQL Server 2008 Express.
- Chapters 10 through 14 focus on upgrading to SQL Server 2008 Business Intelligence components: Analysis Services, Data Mining, Integration Services, and Reporting Services.
- Chapter 15 addresses the implications of upgrading to SQL Server 2008 for other Microsoft applications and platforms.
- Appendix 1 contains a table of allowed SQL Server 2008 version and edition upgrade paths.
- Appendix 2 contains an upgrade planning checklist.
Thomas Kejser and I made some significant updates to the SQL Server Analysis Services Performance Guide for SQL Server 2008 - it's available here.
Our hope is to keep it alive and make updates as necessary. So if there is something you'd like to see added or have any comments at all, shoot me a note at richtk@microsoft.com.
- Richard Tkachuk
