Imagine this … you have just been given the responsibility to manage another SQL Server that you know nothing about …
Ok, maybe we don’t really have to use our imagination in this scenario, since this happens all the time to DBA’s. For example, perhaps you have started a new job (which was recently my case) … or maybe your company acquired another company and its IT resources … or possibly there was a SQL Server instance under someone’s desk that now IT has to take care of.
What should a DBA do in those situations? Well, in this post, I am going to try to explain some of the first steps that I normally do when I take over a SQL Server.
Check for proper database maintenance running
- Backups – Determine if the recovery models and the backup strategy match. For example, if a database is in the FULL recovery mode, are there log backups being taken? (https://sqlbadboy.wordpress.com/2014/09/02/what-is-the-status-of-the-backups/)
- Maintenance plans or solution – Determine if there is a comprehensive strategy to handle the database maintenance. This could be a set of maintenance plans or something like Ola Hallengren’s maintenance solution (https://ola.hallengren.com/). Whatever the case, it should include backups (see previous point), indexing optimizations, integrity checks, and cleanup (database and server).
Check for some standard configurations on the databases
- Set page verification to checksum – Setting this option can help detect corruption. (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/29/enabling-checksum-in-sql2005.aspx)
- Reconfigure database autogrowth – Adjusting these options can improve performance when a database grows and can help to contain VLF problems. Generally, I use a fixed amount to grow by instead of percentages. (https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/)
Check for some standard configurations on the SQL Server instance
- Check max memory setting – Determine the amount of memory that is needed by your SQL Server instance. (http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/)
- Setup Mail/Operators/Alerts – Set up database mail, the operators to be alerted, and standard alerts. The reason this is important is because it provides an easy way to get notified of corruption, job failures, and major outages. (http://www.brentozar.com/blitz/configure-sql-server-alerts/)
- Examine execution of sp_blitz and fix issues – This stored procedure comes from Brent Ozar in his first responder’s kit (http://www.brentozar.com/blitz/). It runs a quick health check of the system and gives internet links in the output that will help you to resolve issues. Also, some of the checks I listed above are included as checks in this script.
Well, these are some of the basic first steps I take when I inherit a SQL Server. Of course, more tuning and adjustments will need to be made as you come to understand the load and usage of the server. But, this is just a start.