Important support changes for SQL Server 2000 and SQL Server 2005

Important support changes for SQL Server 2000 and SQL Server 2005
SQL Server Team
9 Mar 2011 1:28 PM
There are some important support changes coming up for Microsoft SQL Server 2000 and SQL Server 2005. We want to make sure you know what’s happening so you can avoid the risk of running unsupported products and choose the right version of SQL Server for your business.

What is happening:

SQL Server 2000
On 4/9/2013, Extended Support for SQL Server 2000 will come to an end, and SQL Server 2000 will no longer be supported.
After this date:
· Updates to this software will stop and so you will no longer receive patches including security updates.
· Self-Help Online Support will be available for a minimum of 12 months.

SQL Server 2005
On 4/12/2011, SQL Server 2005 will transition from Mainstream Support to Extended Support, which includes:
· Paid support (charged on an hourly basis per incident). Customers will no longer receive no-charge incident support and warranty claims, and won’t be able to request design changes or features.
· Security update support at no additional cost.
· Non-security related hotfix support will require a separate Extended Hotfix Support Agreement to be purchased within 90 days of the end of Mainstream Support – July 11th, 2011.

Microsoft Support Lifecycle Policy
The Microsoft Support Lifecycle policy took effect in October 2002, and applies to most products currently available through retail purchase or volume licensing and most future release products.

Through the policy, Microsoft will offer a minimum of:
· 10 years of support (5 years Mainstream Support and 5 years Extended Support) at the supported service pack level for Business and Developer products
· 5 years Mainstream Support at the supported service pack level for Consumer/Hardware/Multimedia products
· 3 years of Mainstream Support for products that are annually released (for example, Money, Encarta, Picture It!, and Streets & Trips)



Phases of the Support Lifecycle


Your options in moving forward:
SQL Server 2000
· Upgrade to a supported version of SQL Server.
· Find out more about a Custom Support Agreement (CSA).
· Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended)

SQL Server 2005
· Remain on SQL Server 2005 with Extended Support. You can still open support incidents and receive support, however you will not be able to request specific fixes.
· Remain on SQL Server 2005 with Extended Support and purchase Extended Hotfix Support. You can then open support incidents and request specific fixes.
· Upgrade to a supported version of SQL Server.

Licensing changes in SQL Server 2008 R2

I recieved a mail from one of my friend... This may be useful who ever involved in Puchase......

Licensing changes in SQL Server 2008 R2

In the past, Microsoft has followed the “R2” strategy to provide a functional release to existing products. Windows Server 2003 and 2008 went through an R2 version. It was regarded as a bonus for loyal customers who were current on their Software Assurance agreement. This strategy has continued with SQL Server 2008 R2. However, for new licenses the price of a per-processor license for the Enterprise and Standard editions has increased by 15% and 25% respectively while a new edition, Datacenter, is available at a whopping $57,498 per processor for unlimited connections.
Microsoft would say that the introduction of the Datacenter edition means the major SQL Server editions align with those of Windows Server to make things consistent. For instance, you are allowed up to 4 Virtual Machines with the Windows Server 2008 Enterprise license and now the same applies to SQL Server 2008 R2 Enterprise. If you want to go beyond that number you will want to upgrade to the Datacenter edition for both Windows and SQL Server. Also, because the Datacenter edition supports up to 256 logical processors (cores), the Enterprise edition now supports only 8 processors. To keep existing customers happy, to its credit, if you purchased Enterprise licensing with unlimited virtualization, that will be honored as long as you are current with Software Assurance.
There’s also the new SQL Server 2008 R2 Parallel Data Warehouse edition which implements the DATAllegro appliance technology for supporting large databases into the tens of Terabytes. That goes for the same price as the Datacenter edition.
From a technical point of view, the exciting new PowerPivot feature is supported by SQL Server 2008 R2 Enterprise and above with Office Excel 2010 Professional Plus. For existing features, there are a couple changes to licensing. Now Backup Compression is supported by SQL Server 2008 R2 Standard edition and above; however Data Compression still requires the Enterprise. And the free Express edition supports databases up to 10GB up from 4GB. So Microsoft has thrown us a few crumbs.

With regards,
Rahim Kandanuru,
Database Administrator,
DB Admin Team IT Department Dubai Islamic Bank (Head Office) Mob: +971-50-6877056, Off:+971-4-2075563.

Must Read White Papers

For Developers and DBAs .....

A must read collection of White papers from http://mssqltips.com

click here .

Lock Escalation in SQL Server 2005

Locking is a mechanism which is internally handled by SQL Server. This locking technology helps synchronize access of same piece of data by multiple users.
A piece of data is being modified by a transaction triggered by USER A will be accessible only to the USERA and not for others. For achieve this, SQL Server’s Database Engine’s LOCK Manager Component is performing locking methods with different level of locking. The Data can be locked in lowest level at Row level and be at the highest level at Database Level.
The hierarchy starts database level at the top and further drill down to schema, tables, table partitions, extents, pages and finally at the bottom level to the individual ROW. By default SQL Server starts lock from the bottom level starts with row level locking. Each locking uses some memory resources in SQL Server Box. If a heap or B-Tree’s locks threshold crosses 5000 Row level locks counts by a single transaction then SQL Server automatically locks the whole table that is instead of having 5000 different row level locks for a single table, It locks whole table with a single lock. This process of converting many fine-grain locks into fewer coarse-grain locks is calling Lock Escalation.
Lock Escalation reduces system overhead and also increase the probability of concurrency contention.
In practical we often use the table hints in our Select Update Or Delete Command with in transactions. The Table_hints like NOLOCK, HOLDLOCK, PAGLOCK, UPDLOCK and TABLOCK are specifying locking modes of a particular heap or B-Tree for the transaction.
As the life time of a lock structure is equivalent to the life time of a transaction, in-completed transactions may hold large number of locks and these locks may cause severe blockings. For information please visit :
Incomplete transaction may hold large number of locks and cause blocking
We can use DBCC OPENTRAN command to find out the open transactions in the instance.
For More detailed study about the lock escalation please go through the following links
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx
http://blogs.technet.com/mat_stephen/archive/2005/01/31/363803.aspx
http://support.microsoft.com/kb/323630

Know The Transaction Log – Part 4 - Restoring Data

After looking about the backups in SQL Server it is time to know about the Restore and Recovery in SQL Server.

SQL Server supports three levels of Restoring data. They are

1. Complete Database Restore : This is the basic restore strategy. A complete database restore for a simple recovery model database simply involve a full backup followed by the latest differential backup if any available. For a full or bulk-logged recovery model database this complete Data Restore involves restoring a Full Backup followed by a latest differential backup and then a sequence of Transaction log backup in which they are backed up and finally tail-log backup if any available.

2. File Restore : This restore operation is very useful when any one of the files in the file group is damage. The main advantage of this restore is restore time will be less, obviously compared to complete database restore. For simple recovery model database file restore will work with read-only secondary files.

3. Page Restore : This restore is only applicable for Full or Bulk-Logged Recovery model database and not available for Simple recovery model. Using this level of restore , a particular page or pages can be restored.

How Restore Works ?

Restoring is the process of copying data from a backup and applying Transaction logs to the data to point of time when the backup is taken. This process is done in three phases, Data Copy phase, Redo Phase and Undo Phase.

Data Copy Phase: The process of copying the data, index and log pages from the backup media to the database files. No log backups nor log information in the data backups are applied in this phase.

Redo Phase : This phase applies logged changes to the data by processing log information from the log backups.

Undo Phase : This phase applies undoing any uncommitted transactions from the data that are restored from Backup and brings the database online.

In this stage we have to understand the relationship between the WITH RECOVERY and WITH NORECOVERY options in RESTORE Command.

The default option is WITH RECOVERY. This will continue the Undo phase after completing the REDO phase.

A normal restore operation stops at the redo stage if WITH NORECOVERY is included in RESTORE statement. This allows Roll Forward to continue with the next statement of the Restore Sequence, in which the other backups  typically a differential or a transaction backup will do the undo phase.

For a Full Recovery model database or for a Bulk-logged recovery model database, a restore operation is done by a sequence of RESTORE statements. This sequence is called Restore Sequence.

For a simple scenario a restore sequence might be

· starting with restoring a recent full backup,

· applying the most recent differential backup,

· restoring the sequence of log backups in the order they are backed up after the most recent differential backup,

· finally the restoring tail log backup if any taken after the failure occurred.

For more complex scenarios, complex sequence planning will be required. For these planning a recovery path is very important. A Recovery Path is a complete sequence of data and log backups that can  bring database to a point of time. For more details about Recovery Path search in Books On Line.

Complete Database Restore :

A simple restore strategy. Let us see how you have to do a Complete Database Restore using an example. Suppose for a full or bulk-logged recovery model database, a series of backups are taken in the following schedules. A Full backup on Monday 10 PM, Differential backups are scheduled on 10 PM of Wednesday,Friday and Sunday. Transaction Log Backups are scheduled twice a day 6 AM and 6 PM every day. In this sequence, the database is got failed on Saturday 4 PM. How to Restore this database with available backups ?

On a failure situation of database, first thing we have to do is take the tail log backup with NOTRUNCATE option if possible. So take the Tail-log backup first.

Every Restore is to be started with Full Backup. So start with restoring the full backup taken on Monday 10 PM with NORECOVERY option. We have the latest differential backup taken on Friday 10 PM. So apply that backup, and we can omit applying log backups taken after Monday 10 PM and before Friday 10 PM. After restoring this latest differential backup, we have to restore the log backup taken on Saturday 6 AM. That is latest log backup taken on schedule before failure. Now the database is ready up to the Saturday 6 AM. Now, Restore the tail backup that is taken after the failure , that Saturday 4 PM with RECOVERY option. Now the database is fully restored.

File or File Group Restore :

To Restore a Single File in a File group or Complete File group of a Database you have to use Restore Command with FILE option or FILEGROUP option. All you need is unbroken chain of log backups from the time of file or file group backup was made. Before applying the file  or file group backup you have to take the transaction log backup. After restoring the file or file group , you have to restore all the transaction log backups to synchronise that file or file group with the rest of the database.

Let us see an example. Suppose a SecondaryFG is a file group of a database backed up on Friday 12 noon and the database is still in use. Backing up of Transaction log of this database is scheduled on 10 AM, 11:30 AM, 1:00 PM, 2:30 PM , 4:00PM, 5:30PM and so on. Note that the database is still in use, and the changes are made in SecondaryFG and other file groups too. At 5:15 PM, a media failure occurs that corrupts the SecondaryFG. Now we have to restore this. First take the tail log backup that contains all the log records after 4:00 PM Log Backup, with NOTRUNCATE NORECOVERY, to make the database in Restoring state so that no other modification will be done after the failure. Now apply the backup that was taken at 12 noon. So the SecondaryFG now have  all the changes that are made up to 12 noon. Now start applying the Transaction log backups in the sequence of they backed up that is apply 1:00PM Backup first, 2:30 PM backup second, 4:00 PM backup third. Now the SecondaryFG is synchronised with all the database files up to 4:00 PM. Finally apply the tail log backup that was taken after the failure to make SecondaryFG fully compatible with all the files of the database.

Page Restore:

Page restore is only possible for databases using Full Recovery model or Bulk-logged Recovery model. All Editions of SQL Sever other than Enterprise Edition  support offline Page Restore whereas SQL Server 2005 Enterprise Edition supports Online PAGE Restore when database is online.

A page may be marked as suspect page, when a query or DBCC CHECK TABLE or DBCC CHECKDB  failed to access it. Every page in a database that is marked as suspect will have an entry in msdb..suspect_pages table. Event_type column of this table may have either one of the following numbers, 1 for the pages marked with error number 824 other than Bad Page ID and Checksum Error; 2 for Bad PageID ,3 for Checksum Error. 4,5 and 7 for the repaired pages. This table is limited to size and if it is full, the errors could not be logged in this table. So it should be a DBA’s routine to delete the all the records in the msdb..suspect_pages table having event_type is greater than or equal to 4 in regular intervals.

Get the pageId and fileId from msdb..suspect_pages for the pages to be restored. Start RESTORE with full or File or Group Backup that contains the pages to be restored and use PAGE option. Then apply the most recent differential backup if any available and apply all subsequent log backups. Now backup the log and restore it again to match the last_target_LSN in sys.masterfile.

Limitations of Page Restore

· Only Database pages can be restored not the log pages.

· File boot page i.e Page 0 can not be restored and page 1:9 can not be restored that is database boot page.

· GAM , SGAM and PFS Pages can not be restored.

For more details see Books Online.

With this I conclude my Know The Transaction LOG Series. Some things are  purposely omitted in this series of post that are POINT-IN-TIME Restore using RESTORE with STOPAT option to avoid over doses.