I watched most of these a few months ago and wow, this is some serious stuff. I really fancied attending one of the courses as it truly is a level 400 course, take a look at the recommended reading list:

My Book recommendation for developers:

I recommend reading Microsoft SQL Server 2008 series from Itzik Ben-Gan. He has contributed greatly to the SQL Server
community by writing these technical books.

· Microsoft SQL Server 2008 T-SQL Fundamentals from Microsoft Press

· Inside Microsoft SQL Server 2008: T-SQL Querying from Microsoft Press

· Inside Microsoft SQL Server 2008: T-SQL Programming from Microsoft Press

Try to read the SQL Server 2008 books, even if you are not using SQL Server 2008 yet for 2 reasons, SQL Server 2005
features are a subset SQL Server 2008, eventually you will use SQL Server 2008.

· Inside Microsoft SQL Server 2005: T-SQL Querying from Microsoft Press

· Inside Microsoft SQL Server 2005: T-SQL Programming from Microsoft Press

My book recommendation for administrators and developers:

I recommend developers to read above T-SQL books before they venture in to the below books. As a developer you have
some responsibility to understand SQL Server architecture.

· Microsoft SQL Server 2008 Internals from Microsoft Press

· SQL Server 2005 Practical Troubleshooting: The Database Engine by Ken Henderson

· Inside Microsoft SQL Server 2005: Query Tuning and Optimization from Microsoft Press

· Inside Microsoft SQL Server(TM) 2005: The Storage Engine from Microsoft Press

· The Guru’s Guide to SQL Server Architecture and Internals by Ken Henderson

I have not read recent books, so I have no suggestion on them, may be you can share some of your opinions. I remember
reading the ‘The Guru’s Guide to SQL Server Architecture and Internals by Ken Henderson’ long long time ago, read
chapter 10 again, I don’t remember the rest of the book much anymore, but chapter 10 is a must read.

Like I mentioned all books, like software having bugs, have mistakes, larger, smaller, cosmetic, technical, try to practice
what you learn so you don’t learn something wrong. Keep away from undocumented stuff.

Read this blog from top to bottom, read twice if necessary: http://blogs.msdn.com/craigfr, Craig Freedman blog is a
chapter in ‘Inside Microsoft SQL Server 2005: Query Tuning and Optimization’, but I like the blog better, not just because
it is free, it is alive, well was not for nearly 6 months, but is back. You know what I think, you need to keep sending Craig
questions and comments, If not the number of blog entries will decrease over time.

SQL Sever 2005

• You have to reinstall some drivers when you install SQL Server 2005
• FIX: A Deadlock occurs when two transactions try to perform a DML operation on a table that contains a TABLOCK locking hint or an XLOCK locking hint
• Cumulative Update 2 for SQL Server Compact 3.5 Service Pack 2
• FIX: Heap corruption occurs when you use SQL Server Compact 3.5 Service Pack 2

SQL Server 2008

• General overhead performance differences when you view reports in SharePoint integrated mode versus in SharePoint native mode
• How to upgrade an expired installation of SQL Server 2008 Evaluation Edition to a retail version of SQL Server 2008
• FIX: Resource Monitor enters a non-yielding condition on a server that is running SQL Server 2008
• FIX: You experience decreased performance when you run an MDX query in SQL Server 2008 SP1 that has cumulative update 5 or a later version of the update package installed
• SQL Server 2008 full-text query may return false positives using number with more than 9 digits as search predicate condition
• Error 0x80040e97 can occur when indexing large document with integrated full-text search

SQL Server 2008 R2

• FIX: A SQL Server 2008 R2 Integration Services package does not run in SQL Server Business Intelligence Development Studio when you use a shared SQL configuration table

After starting up my machine this morning the instance tried to start up and not surprisingly failed, I have added a subsection of the SQL Log which provides the clues as to why the instance did not start:

2010-08-27 08:45:08.89 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2010-08-27 08:45:08.97 spid10s     Error: 5123, Severity: 16, State: 1.
2010-08-27 08:45:08.97 spid10s     CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘J:\SQLTest\tempdb.mdf’.
2010-08-27 08:45:09.15 spid10s     Error: 17204, Severity: 16, State: 1.
2010-08-27 08:45:09.15 spid10s     FCB::Open failed: Could not open file J:\SQLTest\tempdb.mdf for file number 1.  OS error: 3(failed to retrieve text for this error. Reason: 15105).
2010-08-27 08:45:09.17 spid10s     Error: 5120, Severity: 16, State: 101.
2010-08-27 08:45:09.17 spid10s     Unable to open the physical file “J:\SQLTest\tempdb.mdf”. Operating system error 3: “3(failed to retrieve text for this error. Reason: 15105)”.

After looking at the data in the log file you can clearly see the error is that it can not find the TempDB data file. In my case this was because I did not have the USB stick plugged in, but let’s use our imagination and pretend that this drive is actually a single physical disk that has failed to come back online after some kind of maintenance.

The first step is pretty obvious we need to start the instance up, however in reality this could be quite tricky as one of the things a SQL instance does when it starts up is perform a recovery of each database – including TempDB. Luckily the trace flag 3608 allows you to bypass this for every database baring the master database. It is not recommended to use this as a standard startup parameter, every time you start your instance you will want to ensure that each database is transactionally consistent. You will also need to add the /f parameter this will allow you to start up the instance in minimal configuration mode.

To start the instance again you should open up the command prompt (running as an administrator) and run the NET START command. In my environment the service I wanted to start was MSSQL$MAPS. The full command needed to run from the command prompt is shown below:

NET START MSSQL$MAPS /f /T3608

You should then see that the instance has been started successfully. Similar to the image below:

At this point we break out the old trusty SQLCMD utility which you can call from the same command prompt session. The command you will need is:

SQLCMD –S Machine\Instance

At this point you are connected to the instance and can run the ALTER DATABASE statement as we did in “Moving TempDB”, I’ve included below the code I used to bring my TempDB database back to it’s default settings.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev',  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAPS\MSSQL\DATA\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog',  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAPS\MSSQL\DATA\tempdb.ldf');

To execute a batch of commands in sqlcmd you must use the GO command. At this point you will receive confirmation that the command has completed successfully and you will be informed that the new path will be used the next time the database is started. Exit out of the SQLCMD session and restart the SQL instance.

Once you have restarted the service you will be able to log in to the instance using Management Studio, users will fall at your feet, managers will give you promotions, pay rises and anything that your heart desires.  Either that or someone will walk up to you and say “why did that take so long?”

You may think that you need a server in order to be able to test this, but you can do this on a budget with a bog standard desktop or laptop (as long as it meets SQL Server Express’s minimum requirements) with one physical drive and a USB stick.

First of all insert the USB stick, ensure it’s mapped as a drive and connect to the instance on which you wish to move the  TempDB database then run the following code:

USE master;
GO
SELECT Name, Type_Desc, File_ID, Physical_Name, Size, Growth, Is_Percent_Growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

This code provides the current location of the files (make sure you note this down if you are running this as a test as you will want to change it back to the original configuration) as well as the size and growth settings. The reason I am adding the extra size information in the resultset is that we are going to need to restart the instance and when the instance is restarted TempDB will be pre sized to the values shown above. You really need to check to see if these values reflect the current size of the database. You can do this one of two ways.

1. Run the stored procedure sp_SpaceUsed whilst connected to TempDB.
2. Run the Disk Usage report for TempDB in SSMS. To do this you need to right click on TempDB, choose “Reports” then “Standard Reports” and then “Disk Usage”.

I recommend using option 2 if you do not regularly check the size of your databases as the report has a feature allowing you to see recent file growth.

If the pre determined size is smaller than the current size then you really do need to pre size it accordingly. Again this can be done two ways, via the GUI or by the “ALTER TABLE” T-SQL command. Of course you may have the other issue where TempDB is pre sized and it will not fit onto the USB Stick, in this instance you will need to shrink the database files (never thought I would be saying that on a blog post!).

Now we have checked the size of TempDB it is time to move the TempDB database files onto the USB stick. In my case the USB stick has been mapped to drive “J” and I have created a directory SQLTest to hold the files. To move the files you need to use the “ALTER DATABASE” command with the “MODIFY FILE” clause:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'J:\SQLTest\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'J:\SQLTest\templog.ldf');
GO

Depending on the number of files you have in TempDB you will receive a message similar to the following:

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

I found that when I ran the first query again on my local MAPS instance (SQL Server 2008 R2 Express), it showed the TempDB database files in the new location with the correct sized files. However Books Online says that this will not happen until the service is restarted. I would still recommend restarting the service just to make sure that everything is OK.

After the service restart you should run the first query to make sure that everything is OK, you will then be safe to delete the old database files to free up space.

• Before any implementation project is considered you should have change control and source control in place. Remember the old adage failing to prepare is preparing to fail.

 

• Document, document, document. All areas of the implementation should be documented. This should start with the request for change document, from this a technical change document should be created. Once the change has been made and documented in the development environment you need to follow it up with a release plan. Possibly the most important but often overlooked document is the testing plan.

 

• You must have an extensive testing plan. Your test plan should involve unit testing each piece of code or data that is being changed. For example if your code is inserting data into a lookup table what happens if that value already exists? Will this break your application? The chances are that a duplicate value will cause significant problems. If you are able to change the schema think about adding a unique index so the insert will fail, if you are not able to make schema changes then the code should check for the existence of the value prior to inserting the data.
  Make sure that you have end users testing your system and that they sign off the changes have worked successfully, some companies call this User Acceptance Testing (UAT) whilst other call it Quality Assurance (QA) whatever the name it is a vital piece of work which can save hours of headaches post go live. If the end users are not happy, help them! Thomas LaRock recently wrote an entry on his blog about making this right. In many companies I.T. is a supporting service, others call this cost centre’s so it’s important to ensure that your customers are happy with your service, it’s not enough to say “that’s what you asked for” or “I just ran the scripts I was provided with”.

 

• Never ever test your implementation on your live environment. You should always have an environment where you can run a test implementation. Ideally you’re implementation environment should be as up to date as possible so you have an indication of how the data will be affected in the live environment. Chances are that this environment will not to the same spec so it will not be as fast as in the live environment, it will however give you a time that the task should not exceed. You may even want to run a benchmark test with some queries that way you can extrapolate out the times to give a clearer indication of the time it will take to do each task.

 

• Consider what activity will be happening on the server during the implementation window.
  Are there any scheduled jobs that will occur during this window? Does your business require these to run at this time, can they be stopped or rescheduled? In an ideal world you would have no user access during your implementation however in this day and age a lot of companies have their own websites and one day you will find yourself implementing a change on the website’s database. Build a business case for having a maintenance window where a holding page can be put up on your website. Buy in from senior management is especially important here. If you simply cannot stop your website than make sure you have a test website connected to your test implementation environment (or at least the simulated commands) so you can look at the kind of blocking that you may face when implementing on your production environment.

 

• Consider the amount of data that is going to be produced during the implementation.
  Will you need to resize your database to prevent auto growth? Think about both the data file AND the log file.
  If you are using the FULL recovery mode how big are your transaction logs going to be? Will you have enough space to cope with the extra transactions on your backup drive? Would you gain from moving to BULK LOGGED mode?
  If you are log shipping will your destination drive have enough space for the increased size of the log files? Don’t forget that with SQL Server 2008 Enterprise Edition you can compress your log files.
  If you are using replication do you need to resize the distribution database accordingly?

 

• Rollback plans. You should never implement anything without having a way to rollback to the previous correct state. In some scenarios this may be as simple as taking a backup prior to the changes and restoring the database should anything have gone awry. For most of us however there will be activity on the server whilst we are implementing code. So you should think about auditing data changes and creating rollback scripts for each procedure/function that has been modified. Don’t forget that testing your rollback is as important as testing the implementation. Where would you be if your last backup wasn’t valid? I actually work across the road from a job centre which acts as a constant motivator for me!
  Knowing when to implement your rollback scripts is of paramount importance, before running any scripts at all you should discuss with the process owner the potential outcomes and ask them for their views. In other words you should set your rollback threshold levels prior to implementation. On the night there should be no need to make any major decisions as you should be fully prepared, you should however make sure that you have contact details for all the major members of the project team.

 

• Relax, implementations take time so make sure that you have everything that you need. You’re likely to be doing this at some unsociable hour so make sure you’re in the right state of mind. Don’t relax too much to the point of complacency as you will make mistakes. One thing a colleague did was to play a practical joke on another colleague whose favourite football team had taken a battering:

  
  It certainly lifted the mood in the office!

I keep banging on about how great the SQL community is and I consider myself very lucky to work with a product where people are so keen to share their knowledge and experience with others. In fact this is the main reason that I blog – to help give something back.

Most if not all SQL professionals will have heard of SQLServerCentral.com, if you haven’t it’s a great community site with it’s own SQL Server forum where you can ask questions and receive answers from your peers. The site has a contribution center feature where registered members can submit articles, scripts and even a “Question of the day”. In true community spirit I shared one of the scripts that I had been working on. It was a great feeling when I received an email from SSC saying that my script would be published on their site. It was even listed in the daily digest email:

The decision to publish a script on SSC rather than my own site was based on the premise that it would gain more visibility and be able to help more people than if I were to list it here on my own blog. At the time of writing this it has received over 1,200 hits in under 3 weeks which has proved that I made the right decision.

The script itself can be found here. What the script does is to expand the functionality of sp_spaceused when you are trying to find the size of a database. If you have used sp_spaceused before you will know that it returns two result sets. My script combines these two resultsets into a single resultset but also returns the information for each database in the instance rather than just the database specified. This can really help you to monitor the size of your database over time.

I hope that you find the script useful, and just maybe I have even convinced you to share a script of your own with the community.

Of the few posts I have been able to check out in the evenings there are a couple of posts that I just have to share:

Paul White has continued his rich vein of form with another great blog entry:
Viewing Another Session’s Temporary Table

Christian Bolton announced on his blog that Coeo will be giving away a Fusion IO SSD drive at SQLBits:
Coeo are giving away a Fusion-IO card at SQLBits

Talking about SQLBits and more importantly discount codes for attending, founding member Simon Sabin has said on his blog that if you contact him he will provide you with a discount code:
Taking control of your career with SQL Server

Last but not least Buck Woody has shared a great quote which ties in nicely with the anniversary celebrations going on at the moment: Quote of the Day: The Role of Persistence

Brad McGehee has had a couple of good blog entries this week:
Instant File Initialization Speeds SQL Server - This post is all about the benefits of having Instant File initialization set on your servers. If you’re not aware what it is then it allocates the space to the file without zeroing out the space on the disk (which may cause a security risk on shared servers). Quite a few people know about this, but it’s worth reading the comment by Jose A. Hernandez who says you can allocate the policy permission to a windows group to avoid having to add the permission to a user should the service account change – great tip!

Windows OS Power Saving Feature Directly Affects CPU Performance – As you may have gathered by the title this post is about Windows power setting and what direct affect it has on the CPU. After reading this I went straight to my inherited servers to check their settings. You may want to consider adding this as a step in your server setup if you do not already have it.

Paul White – A SQL Server technical blog from the K?piti Coast, New Zealand:
The “Segment Top” Query Optimisation – Paul’s maiden post at his new blog home on SQLBlog.com

Simon Sabin – SimonS SQL Blog
Want to support SQL Server ? - SQL MVP Simon Sabin provides a link to career opportunities at Microsoft to work with SQL Server.

SQLBits is a not for profit organisation created by members of the SQL community for the SQL community. The first ever SQLBits was held back in October 2007, since then a further 5 have been held in various venues around the country. The idea behind this is to try and involve as many people as geographically possible by simple moving it to people who would not otherwise attend events in other UK towns and cities.

To give an idea of the frequency and locations of the events I’ve listed below all the previous incarnations of SQLBits:

1. October 2007 – Reading.
2. The SQL – March 2008 – Birmingham. 
3. Cubed – September 2008 – Hatfield.
4. Goes Fourth – March 2009 – Manchester – This was my first SQLBits experience.
5. Goes West – November 2009 – Newport – You can read my review of Goes West here.
6. The 6th Sets – April 2010 – London – I had breakfast with Christian Bolton and Connor Cunningham at this one!

The current incumbent  promises to be the biggest event ever and is considered by many to be the premier SQL Server event in Europe with many Europeans and even Americans flying in to attend.

Why would so many people want to attend from so far away? The reason is this, the sessions that are available have actually been voted for by the people who want to attend from an open session submission process. This means that you could submit a session to SQLBits and actually end up presenting and almost certainly guarantees a diverse and high quality programme.

But I’m not a DBA, would I really benefit from going to this?
Yes, there are normally three or four different tracks available. At the last SQLBits the tracks were:

• Business Intelligence
• DBA
• Dev
• SQL2008

So there’s enough diversity to cater for everyone!

OK, I’m sold. How do I go about attending? Is it going to cost much?
The event itself is going to be held over 3 days from September 30th to October 2nd. If you wish to attend the first two days then there will be a cost, but the Saturday event is totally free – they’ll even provide refreshments:

Discounts available from local usergroups. Contact your usergroup organiser for
details.

Extra Discount – 20% off when 6 or more people book for the whole conference.

For more information on travel and accommodation for the conference click here. One quick tip if you’re going by train and have a change to make look at buying a ticket for each change rather than one to the final destination, sometimes it works out cheaper and you’ll end up on the same train anyway! My wife has saved up to £30 by using this method in the past.

All this is only possible sue to the dedication of the founders of SQLBits and the continued support of the sponsors, I urge you to take a look at the sponsor stands if you go as not only do they sell some cool stuff that will make your jobs easier, but there are also competitions to win things like Xbox’s!

Finally, to register for SQLBits 7 click here

P.S. If anyone can tell me what the “7 wonders of SQL” are I’d love to know!