SQL Server – Reasons to Stop the SQL Server

shutdownsql serversql-server-2008-r2

All I've read about is how potentially damaging it is to stop SQL Server because it creates a cold cache and sucks up memory. So why would someone want to stop the SQL Server? If you can provide any links to articles so that I can read more into this I would really appreciate it!

This question was posed by my teacher. Unless it's some trick question, it has me absolutely stumped. His exact question was:

Conduct research using the Internet and learn why someone would want to stop the SQL Server. Explain your answer.

This was in the context of us exploring how to use SQL Server 2008 R2. I'm not sure if he is asking for the obvious answer, or if there is something I'm missing.

Best Answer

Brent listed some invalid reasons for stopping the service, but there are valid reasons too:

Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
Windows patching

Related Solutions

SQL Server Encryption – Options for Data Encryption in SQL Server 2008 R2 Standard Edition

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.

SQL Server – Are Version Numbers Inclusive with Hotfixes?

Unless you have access to the log files for every cumulative update install, the only thing you would know is #3:

Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.

Cumulative updates are, well, cumulative. So are the builds within a CU branch. So 9.00.3182 will contain the fixes from 3179, 3178, 3165, 3122, 3068, etc. Microsoft used to release certain critical fixes individually, but now they have almost completely stopped doing that, opting for a ~60 day Cumulative Update delivery model (security fixes excepted).

The challenge comes when you pass a service pack boundary. There are cases where a fix is released in a cumulative update right before a service pack is released. The service pack code path was frozen at least 60 days earlier (since SPs go through more rigorous testing/regression etc). So, the service pack - even though released later - is missing fixes that won't be in that branch until the first cumulative update for that service pack. This is why you will often see a CU released out of band, almost immediately after a service pack is released.

Then, of course, there is the situation where multiple branches are being maintained, and multiple CUs come out for the lower service pack. There will be fixes there which weren't even known issues when the newer service pack was released.

This means that in many cases a newer build (say, 2005 SP4, 9.00.5000) is missing fixes from cumulative updates for SP3 (say, CU15, which was released several months after SP4). All of those fixes are usually also available in subsequent CUs for the later service pack, but just looking at version number alone wouldn't really tell you that unless you knew which fixes you are trying cross-reference. You'd need a database for that, not a numeric/string compare.

You're kind of playing with fire here, anyway. You're running SQL Server 2005, and not even on the most recent service pack. Unless you have gone through extended support, you are completely on your own. In fact 2008 and 2008 R2 both hit end of mainstream support in about 60 days.

My philosophy is to always be on the absolute latest service pack for any version I am running, and unless there is something prohibitive, always be on the latest CU for that service pack as well. So, rather than worry about whether the fixes for 3179 or 3178 were installed independently, I'd focus more on getting onto a more modern build (SP4 + MS12-070, putting you at 9.00.5324), or a more modern version (and one that isn't about to sunset).

Best Answer

Related Solutions

SQL Server Encryption – Options for Data Encryption in SQL Server 2008 R2 Standard Edition

SQL Server – Are Version Numbers Inclusive with Hotfixes?

Related Question