:::: MENU ::::

Posts Categorized / DBA best practices

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, High Availability

DBA Best Practices for General High Availability

Due to growing data dependency of organizations, high availability is imperative. While implementing any configuration for high availability follow following base lines.

  1. Physically protect your SQL Servers from unauthorized users.
  2. Physically document all of your SQL Server instances. Incorporate effective change management.
  3. Always use a RAIDed array or SAN for storing your data.
  4. Use SQL Server clustering, database mirroring, or log shipping to provide extra fault tolerance.
  5. Replication is not an effective means to protect your data.
  6. Ensure that your entire IT infrastructure is redundant. It is only as strong as its weakest link.
  7. Always use server-class hardware, and standardize on the same hardware as much as possible.
  8. Use hardware and software monitoring tools so you can quickly become aware of when problems first arise.
  9. After testing, apply all new service packs and hot fixes to the OS and SQL Server.
  10. Cross-train staff so that there are multiple people who are able to deal with virtually any problem or issue.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, High Availability, Replication

DBA Best Practices for Replication

Following best practices will help you to configure a successful replication

  1. Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
  2. Ideally publishers, distributors, and subscribers should be on separate physical hardware.
  3. Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
  4. Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed.
  5. Use default replication settings, unless you can ensure that a non-default setting will actually improve replication performance or other issues. Be sure that you test all changes to ensure that they are as effective as you expect.
  6. Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
  7. Periodically, validate data between publishers and subscribers.
  8. Regularly monitor replication processes and jobs to ensure they are working.
  9. Regularly monitor replication performance, and performance tune as necessary.
  10. Add alerts to all replication jobs so you are notified of any job failures.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization

DBA Best Practices for Database Settings

If you are going to play with database settings then wait and read following base lines as best practices

  1. Unless you know exactly what you are doing and have already performed impartial experiments that prove that making SQL Server configuration changes helps you in your particular environment, do not change any of the SQL Server configuration settings.
  2. In almost all cases, leave the “auto create statistics” and “auto update statistics” options on for all user databases.
  3. In most cases, the settings for the “maximum server memory” and the “minimum server memory” should be left to their default values. This is because the default values allow SQL Server to dynamically allocate memory in the server for the best overall optimum performance. If you use AWE memory, then this recommendation is to be ignored, and maximum memory needs to be set manually.
  4. Many databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the “auto shrink” database option, as it can waste SQL Server resources unnecessarily. Instead, shrink databases manually.
  5. Don’t rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, SQL Server Agent scheduled Jobs

DBA Best Practices for Job Maintenance

Jobs are a great automation tool in SQL Server and like every great resource should be used with care and planing. Follow following base lines to get more out of this facility.

  1. Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
  2. When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
  3. If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
  4. Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in all your database. This will rebuild the indexes so that the data is no longer logically fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. Reindexing tables will also update column statistics.
  5. Don’t reindex your tables when your database is in active production, as it can lock resources and cause your users performance problems. Reindexing should be scheduled during down times, or during light use of the databases.
  6. At least every two weeks, run DBCC CHECKDB on all your databases to verify database integrity.
  7. Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.
  8. If you rarely restart the mssqlserver service, you may find that the current SQL Server log gets very large and takes a long time to load and view. You can truncate (essentially create a new log) the current server log by running DBCC ERRORLOG. Set this up as a weekly job.
  9. Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, Logins and Users, Security and Permissions

DBA Best Practices for SQL Server Security

Following best practices may be implemented as base line for standard security of SQL Server

  1. Ensure the physical security of each SQL Server, preventing any unauthorized users to physically accessing your servers.
  2. Only install required network libraries and network protocols on your SQL Server instances.
  3. Minimize the number of sysadmins allowed to access SQL Server.
  4. As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
  5. Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
  6. Give users the least amount of permissions they need to perform their job.
  7. Use stored procedures or views to allow users to access data instead of letting them directly access tables.
  8. When possible, use Windows Authentication logins instead of SQL Server logins.
  9. Use strong passwords for all SQL Server login accounts.
  10. Don’t grant permissions to the public database role.
  11. Remove user login IDs who no longer need access to SQL Server.
  12. Remove the guest user account from each user database.
  13. Disable cross database ownership chaining if not required.
  14. Never grant permission to the xp_cmdshell to non-sysadmins.
  15. Remove sample databases from all production SQL Server instances.
  16. Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
  17. Avoid creating network shares on any SQL Server.
  18. Turn on login auditing so you can see who has succeeded, and failed, to login.
  19. Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications.
  20. Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
  21. Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
  22. Run each separate SQL Server service under a different Windows domain account
  23. Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything.
  24. When using distributed queries, use linked servers instead of remote servers.
  25. Do not browse the web from a SQL Server.
  26. Instead of installing virus protection on a SQL Server, perform virus scans from a remote server during a part of the day when user activity is less.
  27. Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
  28. Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro.
  29. Only enable C2 auditing or Common Criteria compliance if required.
  30. Consider running a SQL Server security scanner against your SQL servers to identify security holes.
  31. Consider adding a certificate to your SQL Server instances and enable SSL or IPSEC for connections to clients.
  32. If using SQL Server 2005, enable password policy checking.
  33. If using SQL Server 2005, implement database encryption to protect confidential data.
  34. If using SQL Server 2005, don’t use the SQL Server Surface Area Configuration tool to unlock features you don’t absolutely need.
  35. If using SQL Server 2005 and you create endpoints, only grant CONNECT permissions to the logins that need access to them. Explicitly deny CONNECT permissions to endpoints that are not needed by users.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, Upgrade SQL Server

DBA Best Practices for Upgrading

DBA Best Practices for Upgrading

  1. Run the Upgrade Advisor before upgrading. Make any necessary changes before performing the upgrade.
  2. Perform a test upgrade of your test SQL Servers before you upgrade your production servers. And don’t forget to test your applications with the new version also.
  3. Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
  4. Don’t upgrade SQL Server clusters in place. Instead, rebuild them on new hardware.
  5. If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases using either UPDATE STATISTICS or sp_updatestats. This is because statistics are not automatically updated during the upgrade process.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

Consult us to explore the Databases. Contact us