SimonS' SQL Server Stuff

Blobs block online indexing

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Blobs block online indexing
The new RSS feed is here

If you are lucky enough to be running enterprise edition of SQL you may have looked or want to look into online index rebuilds. Unfortunately having blobs in your index blocks you from being able to do online rebuild of that index. Cruically what that means is that if your table is clustered then you cannot rebuild the clustered index on that table

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Blobs block online indexing

How to access SQL Server when you've forget the password

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/How to access SQL Server when you've forget the password
The new RSS feed is here

There is a little known feature of SQL Server 2005 is that when you start SQL Server in single user mode local administrators of the server have sysadmin access to the sql server.

You may feel this a security concern, but if a user can get local admin of your server, and restart your service then having sysadmin access to your SQL Server is the least of your problems.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/How to access SQL Server when you've forget the password

Katmai - Group Policy for SQL Server

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Katmai - Group Policy for SQL Server
The new RSS feed is here

If you've worked in a large AD shop you will have come across Group Policy. A means by which you can make sure each component in a domain has the same settings, behaves the same way, has the same security. This is essential when managing a large server estate.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Katmai - Group Policy for SQL Server

Maintenance plan warning for SP2

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Maintenance plan warning for SP2
The new RSS feed is here

Due to a regression bug introduced in SP2 some of the maintenance plan tasks that can operate on multiple databases may not perform as expected.

If a maintenance plan component uses TSQL of this nature

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Maintenance plan warning for SP2

Native Command Substitution is not supported

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Native Command Substitution is not supported
The new RSS feed is here

In response to a recent support incident we raised we got a response about use of detours in SQL Server.

A detour is a means by which you can change what SQL Server does. This is used by Litespeed to change native backup commands into Litespeed backup commands. Whilst I always thought this a neat idea I always felt it a bit risky. If I want to do a litespeed backup I can easily write the code or use the UI to do so.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Native Command Substitution is not supported

RAID Explained

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/RAID Explained
The new RSS feed is here

Thanks to Tom Moreau for pointing in to this great explanation of RAID

:)

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/RAID Explained

Restoring your SQL Server - Chicken and the Egg

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Restoring your SQL Server - Chicken and the Egg
The new RSS feed is here

Have you tested your DR plan? Do you have one?

Well if you do have one then unless you test it its worthless. There are many nuances that occur in a recovery process. The Storage Engine Team have posted about one suhc nuance.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Restoring your SQL Server - Chicken and the Egg

Do you do perform load testing in Live - Microsoft do

Well Microsoft.com do.

I mentioned the Tech Ed webcasts a few days ago. I unfortunately only caught the end of the Microsoft.com webcast. Well I've just finsihed watching the on demand version and wow. I previously highlighted a talk about amazon.com (Are you running a high volume website) this is another webcast that you should watch.

Its a great insight into what needs to be done to run a high end internet operation. The numbers they are talking about are just scary, 120Gigabits/sec bandwidth, ok so many sites won't be needing this level of scale. However they do provide some great points on,

• Load balancing, NLB v hardware
• SQL HA solutions, Peer to Peer, Mirroring, log shipping
• Clustering
• Global Load balancing

On interesting statement was on testing. I agree that it is very difficult to simulate Live usage, for 3 reasons, generating the amount of traffic is difficult, generating the traffic in a realistic manner is even more difficult, finally can you afford to duplicate your live hardware. So there is a valid reason for doing load testing in a Live environment.

Cross posted from http://sqlblogcasts.com/blogs

Whats worse losing money or losing data?

I mentioned a the storage engine blog recently and was reading the latest post Common bad advice around disaster recovery when I thought about a recent sitauation that occurred for us which applies to another of the storage engine posts about recovery or repair

Paul talks about doing some Root Cause Anlaysis of the problem, however when your problem is stopping your system from operating what is best,

1. Carry on trying to solve the problem to avoid corruption, but prolonging the issue
2. Do a quick dirty fix, that causes corruption, but gets your service working again. Often means you can't do a full RCA.

Our situation is a replication one, where the log reader agent got stuck. It couldn't read past a certain transaction in the log. This meant that no data was being replicated. We run at close to the limit for some of our subscriptions. This means that if anything gets in the way the latency can take quite some time to recover. In this situation we were convinced we could fix the issue and have everything working again, 3 hours later we realised that we could not and so did the fix that resulted in inconsistent data across our replication topology.

The fix meant that data was being replicated again but we had to spend considerable tie reconciling the data, that had been lost (put in the log) in the time we took to try and fix the problem.

As a result of this, we realised that to meet our SLAs we are better doing the dirty fix straight away. Whilst this means that we may have inconsitencies, these should be small (relative to the fix time), data continues to be replicated and so SLAs are met and we shouldn't loose many money/clients.

So the moral of the story is that you need to align ALL your SLAs with ALL your recovery procedures, fix, restore, failover etc.

Cross posted from http://sqlblogcasts.com/blogs