There's Something about SQL!

The Hidden Database in SQL Server 2005 and 2008

Have you ever had to move your master database? Or have you ever opened the folder which contains your master database? Both cases - if that ever happened to you, you should already know about the Resource Database - a hidden, read-only database introduced with SQL 2005 which contains all the system objects that are included with SQL Server.

The purpose of this database it to speed up the upgrading process. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

What you need to know about it:

• The Resource Database must be placed in the same location as the master database. Therefore - in SQL 2005 and 2008 - if you move the master database you need to move the resource database as well otherwise your SQL Server installation won't start.

More info: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

• You should back it up at each upgrade of your installation (SP, CU or hotfix). Unfortunately, as it's not a standard database, you cannot back it up in the traditional way, but you have to perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file.

Note that - After restoring a backup of the Resource Database you must reapply any subsequent updates. Say that you took your RD backup when you upgraded to CU1, now you are on CU10 and have lost the RD - you can re-apply the old backup but then  you will have to re-apply all the update steps you're performed since CU1.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO

More info:

Resource Database

http://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx

Reserved Resource Database in SQL Server 2005

http://www.extremeexperts.com/sql/Yukon/ResourceDatabase.aspx

SQL Server backup and restore of the Resource database

http://www.mssqltips.com/tip.asp?tip=1544

Geek City: The Resource Database

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

- Beatrice Nicolini -

Performance Monitor, SQL Counters and Tresholds

Whilst surfing on the web last week, I've found this very nice blog from Vipul Shah, which happens to be a Microsoft colleague, which explains which are the key performance counters to monitor for your SQL Server instances and most importantly summarizes which are generally the thresholds you want these counters to fit within.

Understanding Perfmon Counters while troubleshooting SQL Server Performance Issues

http://blogs.technet.com/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

You should consider to use these counters to monitor and benchmark your SQL Server boxes, and then of course you can review results through Performance Monitor itself  or through PAL:

Performance Analysis of Logs (PAL) Tool

http://blogs.technet.com/beatrice/archive/2008/04/02/performance-analysis-of-logs-pal-tool.aspx

- Beatrice Nicolini -

Reading User Group Meeting - 20th Nov

For those which live or work close to Reading - there will be a UserGroup on the 20th Evening held in TVP - Microsoft Offices.

Agenda:

6pm – 6:30 Registration and networking + Tea/Coffee with biscuits.

Meet and greet.

6:30pm – 7 Round Table discussion - ALL

Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have, may be a problem at work.

7pm – 7:40 SQL Server 2008 - Data & Backup Compression

Beatrice Nicolini

Premier Field Engineer | SQL Server |Microsoft U.K.

Read my Blog.: http://blogs.technet.com/beatrice

With the 2008 release, SQL Server makes a major advance in scalability for data warehousing. With data and backup compression, SQL Server 2008 reduces the size of tables and indexes by storing fixed-length data types in variable length storage format and allows saving disk media space for your SQL backups. This session will guide you through the new data and backup features of SQL 2008, and will show which options are available for different data warehousing scenarios.

7:40pm – 7:55 BREAK: Sarnies

More time to network and ask questions...

7:55pm – 8.15pm Open Nuggest Session

The Audience - anyone

Bring your 5 minute hint/tip/demo and present it. This worked well at the London User Group.

Please let me know in advance via email to http://sqlserverfaq.com/events/138/tonyrogerson@torver.net.

It's a great way of getting your name know and developing your career! There will be prizes for each person who presents.

8:15pm – 8.45pm Table Expressions and the Optimiser and some SQL 2008 optimisations for Optional Parameters

Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

We will look at the theory behind Table Expressions (Derived Tables, Views, Table Valued Functions and Common Table BLOCKED EXPRESSION, we will delve into some traps and suprise you at what the optimiser is doing under the covers. To finish off we will look at some SQL Server 2000 introduced optimiser improvements that certainly if you are using dynamic SQL because of optional parameters will mean you can move away from dynamic SQL back to simple straighforward SQL.

To register:

http://sqlserverfaq.com/events/138/UK-SQL-Server-User-Group-Evening-Meeting-Agenda-TBC.aspx 

- Beatrice Nicolini -

PPT Plex add on for Power Point - Cool!

PPT Plex is a new tool - or more precisely - a new add on for Microsoft Power Point - which will change the way you will deliver your presentations.

In short, what it allows is a different navigation experience trough your slide deck. Instead of having the classical navigation path - in which you follow your slides in a pre-definite order - you can group your slides based on topic, subtopic etc, and quickly drill out and drill down on the subject you want to cover.

I would generally not recommend it for whichever presentation.

For the standard presentations in which you really want to follow a specific path, then you don't really require this tool (or you can use it, if you just want to look cool :-)).

Though you really get the most out of it during interactive presentations - sessions in which you don't have a specific path to follow but you want to jump between different subjects - perhaps because you want to follow the public's questions or specific areas of interest.

Anyway, said that, it is definitely something to try out - and then you'll figure out why and how to use it - based on your tastes.

More info?:

Well, PPT Plex official site: http://www.officelabs.com/projects/pptPlex/

Eileen Brown's blog contains some useful information too: http://blogs.technet.com/eileen_brown/archive/2008/09/12/pptplex-changing-the-way-you-present.aspx

- Beatrice Nicolini -

Resource Governor - How to get started?

Here we are, as promised, with some rumblings on SQL Server 2008 Resource Governor.

I've played a lot with the various options which this features provides, especially in preparation of my TechEd presentation last week! Did I ever mention how much I loved it :-)? Yeah, I guess too much already. Ok, so this is the last time. Maybe. Umh…

Moving on, so what if you want to start using Resource Governor, let's say… today? :-)

As usual, I don't like to repeat what others have already been posting, possibly in a better way that I would do really, so here are first some resources to check out to start learning about it:

First place, of course, Technet: http://technet.microsoft.com/en-us/library/bb933866.aspx

A bit more advanced, from PSS Engineers: http://blogs.msdn.com/psssql/archive/2008/01/10/sql-server-2008-resource-governor-questions.aspx

So what to add on that?

I guess the tricky bit would be to understand how to effectively implement Resource Governor on your specific scenarios.

Of course there are some typical scenarios in which you'll most likely use it. Amongst the ones I use to mention:

• SQL Server 2008 Backup Compression - as I posted some time ago (http://blogs.technet.com/beatrice/archive/2008/06/11/data-backup-compression-in-sql-2008.aspx), backup compression is a brilliant new feature which comes with SQL Server 2008 (yep - always enterprise - though you can restore on whichever version). It saves you money (for third party products), storage space, and it allows you to reduce the run time required from your backups and restores. Yeah… and…??? And you pay all these benefits in terms of CPU. So. If CPU is a issue on your implementation, or better, if CPU is a resource which you want to govern on your implementation and you're not bothered on having your backup\restores taking a little bit longer, well Resource Governor is your friend.  Set up a pool and specify a max memory, call it however you want (Backup Compression Pool I guess is a good idea :-)), and run your compressed backups with a specific user which you can isolate through your Classifier Function. Deal done.

• Reporting Scenarios. Does this ring a bell? Correct - Resource Governor governs only the Database Engine, not SQL Server Integration Services, Analysis Services, Reporting Services. OK. BUT. Your Reporting Services or Reporting Application (oh no - are you NOT using Reporting Services yet? - we'll have a word about this maybe in another post) well you'll have to retrieve data from the Database Engine at some time. Correct. And what if that reporting application is very critical to your business or especially - what if you want a quiet day without your customers phoning you to complain that the application is slow? Nearly the same as before. This time though your pool will have a minimum CPU and Memory, and again the deal is done by isolating the user name or the application name in the classifier function.

• DBAs. Ohhhhhh yeah. DBAs have LOADs of work to do during the day (of course, I am nearly a DBA so I better save my job :-)). So, what do they have to do? Troubleshoot and monitor, so they really need a minimum amount of CPU and Memory to play around but also we still want our business users to be happy with our implementation, so we do not want DBAs to take up all the resources on our server. So this time we'll be using MAX and MIN values for our CPU and Memory.

• LOL - I love this - and what if you have a "crap application"? I've been asked this question at TechEd. I'm afraid a crap application will still remain a crap application. FIX IT! But yeah, whilst fixing it, if your application is so crap to fill up all you resources, TEMPORARLY you can assign max values for it in the Pool, or if for example you have to deal with locking, you can grant MINIMUM values to have  your applications queries to run as fast as they can, to leave other workload free to run asap. Again, this is not the way Resource Governor is meant to be used and especially, I am talking generally, so TEST before doing anything in production.

• So what if you have anything else? I mean most of your SQL Server implementations will not just be about Backups, Reporting and DBAs (I'm not even considering crap apps :-)). How can you understand how to benefit from Resource Governor in your specific scenario?

It's always very difficult to talk generally, but I would personally approach the problem in the following way:

• As I will generally know my implementation and the users and apps which are connecting to it, I will set up pools (remember max 18 user-defined) and workload groups (1:1 relationship) with default parameters, plus classifier function to assign each workload to a specific pool.
• In this way - with default parameters - I will obtain SQL 2005 behavior BUT I will be able to monitor pools usage (and therefore CPU and Memory Usage per Pool) with the new events available in PerfMon and the new DMVs dedicated to Resource Governor
•  I can then start changing my default parameters based on monitoring, benchmark and monitor once again till I will achieve the desired results.

I will soon post about some other questions I've been asked during TechEd! Stay tuned!

- Beatrice Nicolini -

SQL Server and Virtualization - can we help?

Just discovered whilst at TechEd that Microsoft policies for SQL Server products that are running in a hardware virtualization environment are not much known as yet to the majority of the public.

Therefore the PFE in me :-) wants to be sure that when you opt for virtualization you are in a safe position to receive support from myself, my PFE colleagues or from GTSCs when things go wrong.

So, here's the deal:

Microsoft provides technical support for SQL Server 2005 and for SQL Server 2008 that are running in the following hardware virtualization environments:

KB article http://support.microsoft.com/?id=956893 has been updated as the single resource to obtain information on support policies for SQL Server running in a hardware virtualization environment.

If you are looking to take advantage of virtualization, here are some must read articles you should look at:

WHITEPAPER: Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations

http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx

By the way, this is the blog from the SQL Cat team. One of my favorite RSS feeds on SQL ever!

Licencing - Virtualization and Multi-Instancing

http://www.microsoft.com/sqlserver/2005/en/us/special-considerations.aspx#Virtualization

Migrating a virtual machine from Virtual PC to Hyper-V

http://blogs.technet.com/andrew/archive/2008/06/30/migrating-a-virtual-machine-from-virtual-pc-to-hyper-v.aspx

- Beatrice Nicolini -

TechEd ItPro - SQL Server 2008 Resource Governor!

Wow! Presenting at TechEd yesterday had been one of the best experiences of my life!

Being in front of different customers, which work with SQL Server every day, and being able to share my experience on the field with the new great features included with SQL 2008 had been fantastic, and a great learning experience for me as well!

I'm still pretty busy here in Barcelona, so for today I just want to quickly post my slide deck on SQL Server 2008 Resource Governor (http://blogs.technet.com/beatrice/attachment/3147812.ashx) and one nice picture with myself and my colleague Andrew Fryer http://blogs.technet.com/andrew/ - which helped me out with the Q&As bit of my session.

In the next days I will post a bit more in detail about Resource Governor - and how you can effectively benefit of its features in your specific organizations.

Also - I've received pretty interesting questions during the Ask the Experts session - of which I want to post about as soon as I can!

Thanks again to all the participants (feel free to drop me an email), and stay tuned with my next posts!

- Beatrice Nicolini -

I'm at TechEd ItPro - Barcelona!

This week I'm presenting on SQL Server 2008 Resource Governor @TechEd Barcelona http://www.microsoft.com/emea/teched2008/itpro!

My session is on Tuesday, 10:45 - 12:00, make sure to come along if you want to learn about this cool new feature in SQL Server 2008 or if you simply have some questions to ask!

- Beatrice Nicolini - 

Announcing SQL 2005 SP3 Beta

SQL Server 2005 Service Pack 3 (SP3) is now available for public testing; a final release version will be available in the future (Near future. Before the end of this calendar year ;-)).

The SP3 Beta version addresses issues reported through the SQL Server community and quick fixes to enhance SQL Server 2005. No new product features are included in SP3.

You can download and test the beta from http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

- Beatrice Nicolini -

SQL Server 2008, Cumulative Updates and... Slipstream

I've posted some time ago about Slipstream of Service Packs on SQL Server 2005  - which was and is still not available -  so basically with 2005 it is not possible to have the Update/SP and the RTM included together in a single package.

This was a feature which most administrators were looking forward to as it allows to save time and complexity during installs.

Now question is - what about SQL server 2008?

Answer is unfortunately the same - no slipstream I'm afraid - BUT (underlined J) it is possible to fix your SQL Server 2008 setup before you actually... run the setup itself.

As you all know, it is possible to fall into errors from the very beginning, at setup time - you know, when it is one of these lucky days...

SQL server 2008 allows you to take immediately advantage of fixed setup fixes, and you should always consider to use this new ability as it can save you a lot of time, effort and... headaches!

For detailed information - have a look to this article from Bob Ward:

How to fix your SQL Server 2008 Setup before you run setup

http://blogs.msdn.com/psssql/archive/2008/09/30/how-to-fix-your-sql-server-2008-setup-before-you-run-setup.aspx

Also - when patching your SQL Server - consider first having a look to this very interesting article:

Welcome to SQL Server 2008 Patching

http://blogs.msdn.com/psssql/archive/2008/09/26/welcome-to-sql-server-2008-patching.aspx

- Beatrice Nicolini -

SQL Server 2000 and 2005 - Recommended Builds - (another) update

Latest Microsoft Security Bulletin (MS08-052 - Vulnerabilities in GDI+ Could Allow Remote Code Execution) changes once again Microsoft recommended builds for SQL Server  as follows:

• SQL 2000 is not touched from the Security Update, so minimum build remains 2273
• SQL 2000  Reporting Services minimum build is now SP2 +  KB954609 which will take your instance to 1062
• SQL 2005 minimum build  is now SP2 + KB954606 for GDR implementations, which will take your instance to 3073 and SP2+ KB954607 for QFE implementations, which will take your instance to 3282.

As usual, to not get lost in the build numbers world, I recommend to refer to this  useful website: http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

- Beatrice Nicolini -

\3GB \PAE and AWE - Taking away (some) confusion...

It's a long time since I wanted to post some info concerning memory on this blog,  but I always been confused on where to start from.

To take away any doubt, let's start from scratch. Even amongst the most seasoned DBAs there is some confusion about memory , so we should try first to clarify these.

Starting with what is already available over the internet, so far the best blog I found you should consider starting with if you are a newbie in the "memory world", is the following:

PAE and /3GB and AWE oh my...

http://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx

For any further detailed information on SQL server memory internals, Slava Oks's WebLog (http://blogs.msdn.com/slavao/default.aspx) is what you need.

To summarize (from the SQL Server point of view)

1) With default settings:

Each process will be assigned memory split between:

Physical Memory - user-mode - therefore max 2GB (max depending on phy mem or other apps using the same space) + Disk (Paged)

2) Same (nearly) happens with \3GB.

Each process will be assigned memory split between:

Physical Memory - user-mode - therefore max 3GB (max depending on phy mem or other apps using the same space) + Disk (Paged)

3) With \PAE and AWE

PAE\AWE-aware processes will be assigned up to 64GB of memory (depends on Windows Edition) split between:

Physical Memory - user-mode - therefore max 2GB or 3GB depending on \3GB option (see rule of thumb below) + Physical Memory (Additional) + Disk (Paged)

Rule of Thumb

If your system has < 4 GB - use only /3GB

If your system has > 4 GB and < 16 GB - use /3GB + /PAE + AWE

If your system has > 16 GB - use /PAE + AWE

Now then, so why considering x64 Architecture if we have AWE?

The use of SQL Server (32-bit) with AWE has several important limitations. The additional memory addressability is available only to the relational database engine’s management of database page buffers. It is not available to other memory consuming database operations such as caching query plans, sorting, indexing, joins, or for storing user connection information. It is also not available on other engines such as Analysis Services.

For more detailed information, refer to:

Advantages of a 64-bit Environment

http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx

What about max_server_memory and min_server_memory?

By default, SQL Server can change its memory requirements dynamically based on available system resources. Which means, it can use the amount of memory specified between min_server_memory and max_server_memory.

Use min server memory to guarantee a minimum amount of memory available to the buffer pool of an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.

Use max server memory to prevent the SQL Server buffer pool from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly.

Usually (underlined, yes it is underlined) you should leave these settings at the default value,  BUT the following considerations\exceptions apply:

• As there is a short delay between the start of a new application and the time SQL Server releases memory, using max server memory prevents this delay and may give better performance to the other application.
•  With SQL Server 2000 AWE or SQL 2005\2008 AWE on Windows 2000, memory is not managed dynamically.  This means, min server memory is ignored and max server memory is never released so must be set (underlined again).

Min server memory and max server memory are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately (without a server stop and restart).

For more info:

Enabling AWE Memory for SQL Server

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

Full Backups & Transaction Logs Backup

A general misconception when it comes to transaction log management, is that the transaction log gets automatically TRUNCATED (underlined, as I am not saying SHRINKED!!!) when a full database backup is performed.

This is completely untrue, and if you just don't believe it, just open Management Studio, right click your DB, select task  > backup, select backup full and then switch to options... well, as you can see, the "truncate the transaction log" checkbox is greyed out - not an option - indeed.

*

Obviously, TRUNCATING the transaction log is something you must do as a DBA, as if log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files.

So question is WHEN does it get TRUNCATED?

Well, except when delayed for some reason, log truncation occurs automatically as follows:

• Under the simple recovery model, after a checkpoint.
-
• Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

For more info:

Transaction Log Truncation

http://msdn.microsoft.com/en-us/library/ms189085.aspx

Therefore, if you are under full of bulk logged recovery mode - you have two options:

• Backup the transaction log - so that it will get automatically truncated (STRONGLY suggested)
• Force the truncation (...)

Now when you actually go for the STRONGLY suggested option :-) i.e. you backup your transaction log and it is actually automatically truncated, this has actually a good consequence, which is let's say that your last backup is corrupted, you can still go back to the one before and recover all the way through to your favourite point in time :) yay!

OK now, what's the difference between TRUNCATE and SHRINK (http://msdn.microsoft.com/en-us/library/ms178037.aspx)? Think it like a box full of books. Truncate takes out the books but the size of the box is still the same, ready for new books (same amount) to be stored in. Shrink makes the box smaller and then when you actually have to store new books well, you will have less storage space - and then you will have to autogrow - therefore you'll be hitting performances (that's actually very good idea, I would like so much an autogrow luggage for my trips).

- Beatrice Nicolini with the contribution of Norm Eberly- aka "he"- Senior PFE -

Backup\Restore & Corruption – What is worth to know

Just being discussing recently the Backup\Restore best practices (related to corruption) and wanted to share some information.

SQL Server Backup\Restore in SQL (when actually done directly from SQL, not from a 3rd party application - I come to that in a second) offers many options which are:

- Checking the backup consistency while performing the backup

- Checking the backup consistency after the restore (therefore by running DBCC CHECHDB on the restored Database)

- Checking the backup consistency with the restore command without actually performing a restore

Everything is very well explained in this article from (surprise surprise...) Paul Randal:

Oh no – my backup is corrupt too! Help!

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/05/oh-no-my-backup-is-corrupt-too-help.aspx

Now, with a 3rd party application things are a bit different as you need to stick with the options that it gives to you.

I've just checked Litespeed options (as an example) and all that I can find is a "Verify Backups" option in the restore wizard.

Now this means that

- you won't be able to check backups when actually creating a backup

- you don't have anything similiar to WITH VERIFYONLY, so you need actually to restore your database to check consistency of your data

This leaves you with the two options:

- Restore with the "Verify Backups" option provided from the Wizard

- Restore and then run DBCC CHECKBD

I am not sure which one would be more efficient. I would guess all that the "Verify Backups" option does is actually to automatically run DBCC CHECKDB but happy to be contraddicted.

Any case, rule of thumb is to ALWAYS DBCCC CHECK your databases (daily) and ALWAYS CHECK YOUR BACKUPS (daily would be best but I already know the opinions on this... say weekly). How- is up to you!

- Beatrice Nicolini -

SQL Server 2005 SP2 - Recommended Build - Update

I've posted a blog entry some time ago about Microsoft recommendation for SQL Server SP2 builds: SQL Server 2005 SP2 - Recommended Build?

Last week Microsoft has released a new SecurityBullettin (MS08-040 - Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege) which while we're waiting for the release of SP3, changes Microsoft position about recommended builds as follows:

• SQL Server 2000 SP4 minimum recommended build is now 2273 (which includes 2187 whch was the old recommended one)
• SQL Server SP2 minimum recommended build in now 3068 (which includes 3054 which was the old recommended one)

Just a couple of notes concerning SQL  Server 2005 as things can become a little bit confusing .

Minimum recommended build is 3068, but for example you cannot apply it if your build is between 9.00.3150 and 9.00.3230.  In that case you'll have to use KB948108 which will take you to 3233.

Also, if you have decided to keep your instances up to date with the SQL Server 2005 Cumulative Updates, you don't have to apply anything in case you are already on CU7 - build 3239 or CU8 - build 3257 (or above, in the future :-)).

More info:

Microsoft Security Bulletin MS08-040 – Important

http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

MS08-040: Vulnerabilities in Microsoft SQL Server could allow elevation of privilege

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

- Beatrice Nicolini -