Hi We used to perform the server and database maintenance in the weekend but users start using the

server in weekend also. I need to avoid the downtime of the server while the maintenance it may

cause the server reboot. How to handle this situation.

• Edited by Senthil_tm 6 hours 30 minutes ago
• Moved by SathyanarrayananSMVP 5 hours 45 minutes ago Moving to Database Engine

Hello,

It depends on which kind of maintenance you run, which SQL Server edition are you using etc. E.g. if you run an reindex job on an Enterprise edition you can use the ONLINE option, so that the base tables will be still accessible during the reindex. 

This is a far-reaching question, and an important question is your role. Do you work for the IT department in a medium/small company or something else?

Any group that runs one or more service should have an SLA (Service Level Agreement) with the business owners, which spells out when the system should be available, what downtime that is permitted and when, how long time it make take before the system is back up after an unexpected failure etc.

In your situation it seems that you had quite an easy ride, as weekends were available for system maintenance. Now the business wants to use the system on weekends. This means that you need to review your SLA, and most likely it means that your departments needs a bigger budget.

Olaf mentioned online reindexing. This feature is only available in Enterprise Edition, and I would guess you have Standard Edition. So there is a cost right there. I assume that your maintenance work also includes time to apply Windows updates and similar. To avoid downtime you would need something like a cluster, so you can apply fixes on the cluster. This is likely to mean that you need to buy more hardware. Administrating a cluster is more complex than a plain server, so there is a cost for training.

What if, the business is not prepared to cough up the money for this? Simple. Then you stick to your current SLA and users will have to accept that access on the weekend is not guaranteed.

Hi

Do you have Failover Cluster in your environment? It is one of the High Availability options where you can failover to other server and do the maintenance in the primary server and again failback to the primary server and can do the maintenance on the other server. This is not the only method for Failover cluster and there are more benefits available as well.

Below article may help you start understand on Failover Clustering.
http://msdn.microsoft.com/en-us/library/ms189134%28v=sql.100%29.aspx

Cheers

Mani@YourSQLMan.com