SQL Server Patching

Question

Hi,

Which is the best method for SQL Server patching?

We are planning to patch 122 SQL Servers (Windows 2005/2008/R2) Which contains clusters/DB Mirroring/Replication/Log-shipping Servers also.

Answer 1

I would suggest to do it Step by Step :)

We are managing over 4.000 instances and we handle it the following way:

1. Patching all UAT-machines (User Acceptance) to check whether the updates may fail!
2. After a UAT-machine has been patched without errors the dedicated PROD / DR environment CAN be patched, too.

Clusters may have multiple instances installed. So you can patch ALL instances at the same time

Mirroring:
Patch the mirror first.
Than set the databases (if not by default) to SAFE Mode and make a planned failover:

ALTER DATABASE [PrincipalDB] SET SAFETY FULL;
ALTER DATABASE [PrincipalDB] SET PARTNER FAILOVER;

After all databases have been switched to the DR-Server you can patch the "Principal" and fall back.

Replication:
no problems

For DR solutions like Mirroring I would recommend to use DNS-Aliases. Before you do the failover you only have to change the DNS for the database server to the DR. If you work with DNS-Aliases the applicatoins do not need to change their connection settings!

This is not necessary for clusters but can be used for replication the same way if your replicated db is for "hot standby"

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Hi,

Thank you very much.

One doubt, how you patching all those 4,000 instances, i mean are you using any 3rd Party tool for pushing patches or manually installing on each and every server.

Answer 3

Hallo Vijay,

all UAT systems will be done by script.
I do not know your business but I'm working for a bank in Germany and due to reglementations we have to wait with installation of patches 4 - 6 weeks AFTER the UAT systems have been rolled out!

PROD and DR have to be installed manually.

This way of installation makes sence to work "proactive" if any problems occure.

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 4

Thank you very much.

One more question.

Actually i am looking for change but, Max companies looking for guy who having even knowledge in IIS also along with DBA

As a DBA what are the things we should know about IIS?

Answer 5

Hallo Vijay,

i do not really know why IIS should be a preferred knowledge for a dba.
The only point which makes sence to me for knowledge of IIS...

- Reporting Services (only with SQL Server <= 2005)
- Knowledge about Application Pools because of Identiy changes

Otherwise I do not see a reason why a dba should be familiar with IIS!

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 6

Hi Ricken,

i have some questions, can u please answer these questions.

what is activity monitor and why?

if database crashed, and i dnt have master db backup, what are the steps?

Thanks,

satya.