mga MSSQL admin please share your ideas and knowledge

[nyor]

---

Diri lng nko gibutang ang thread ky related ra sa programming especially creating stored procedure,functions and triggers.. Anyway, i'm new in handling mssql server databases basic knowledge ra in the process of learning. I hope naai mo share sa ilang mga experience and best practices especially in handling databases like maintaining indexes, performance tuning, log shipping, replication, backup and restore, troubleshooting, disaster recovery, etc..

[rics zalved]

hmmmm...

di lang ko expert sa MSSQL kay lain nga DB ko naay experience which is PostgreSQL, pero universal man pud ang SQL language

ako lang mashare is every table kinahanglan naa jud index para paspas ang query

then backupan jud everyday ang database mga at least 2 separate drives para sure, automatic backup iset sa task scheduler

naa pud times nga magneed mi ug another table nga gitawag namo ug materialized view para less ang query kaysa daghan ipangjoin nga tables unya para mas paspas ang performance

[nyor]

hmmmm...

di lang ko expert sa MSSQL kay lain nga DB ko naay experience which is PostgreSQL, pero universal man pud ang SQL language

ako lang mashare is every table kinahanglan naa jud index para paspas ang query

then backupan jud everyday ang database mga at least 2 separate drives para sure, automatic backup iset sa task scheduler

naa pud times nga magneed mi ug another table nga gitawag namo ug materialized view para less ang query kaysa daghan ipangjoin nga tables unya para mas paspas ang performance

ok ra noon ang backup sir, weekly fullbackup, daily differential nya every 15 minutes ang transaction log backup.. nya stored on a network drive every backup..

[defender_1611]

Let me allow to enumerate it one by one so here it is:

General DBA Best Practices

Day-to-Day

1. Check OS Event Logs and SQL Server Logs for unusual events.
2. Verify that all scheduled jobs have run successfully.
3. Confirm that backups have been made and successfully saved to a secure location.
4. Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should
have 15% or more of free space.
5. Throughout the day, periodically monitor performance using both System Monitor and Profiler/SQL Trace.
6. Regularly monitor and identify blocking issues.
7. Keep a log of any changes you make to servers, including documentation of any performance issues you identify
and correct.
8. Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as
needed.
9. Regularly restore backups to a test server in order to verify that you can restore them. You don’t need to restore
all backups every day, but do so often to ensure that you are confident you have good backups.
10. Take some time to learn something new as a DBA to further your professional development.

Installation

1. Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
2. If possible, install and configure all of your SQL Server instances consistently, following an agreed-upon
organization standard.
3. Don't install SQL Server services on instances that don’t use them, such as Microsoft Full-Text Indexing, Reporting
Services, or Analysis Services.
4. For best performance of SQL Server running under Windows, turn off any operating system services that aren't
needed.
5. For optimum SQL Server performance, you want to dedicate your physical servers to only running a single
instance of SQL Server, along with no other applications.
6. For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate spindles to isolate disk
access patterns.
7. If tempdb will be used heavily, put it on its own separate spindles.
8. Do not install SQL Server on a domain controller.
9. Be sure that SQL Server is installed on an NTFS partition.
10. Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.

Upgrading

1. Run the SQL Server 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.
This is because statistics are not automatically updated during the upgrade process.

Security

1. Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access 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. Instead, use a Windows
Authentication account to access SQL Server as a sysadmin.
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. Disable the guest user account from each user database by using REVOKE CONNECT FROM GUEST.
13. Don’t use 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. Configure 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. In SQL Server 2005 and earlier, remove the BUILTIN/Administrators group to prevent local server administrators
from being able to access SQL Server. In SQL Server 2008, the BUILTIN/Administrators group does not exist by
default.
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.
24. When using distributed queries, use linked servers instead of remote servers. Remote servers only exist for
backward compatibility.
25. Do not browse the web from a production SQL Server instance.
26. Instead of installing virus/antispyware protection on a SQL Server, perform 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 Red Gate SQL Backup Pro.
29. Only enable C2 auditing or Common Criteria compliance if required, as they add significant performance
overhead.
30. Consider running a SQL Server security scanner against your SQL servers to identify security holes.
31. Consider enabling SSL or IPSEC for connections between SQL Server and its clients.
32. If using SQL Server 2005/2008, enable password policy checking.
33. If using SQL Server 2008 in a high security environment, consider implementing Transparent Data 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/2008 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.

Job Maintenance

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.
3. Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
4. If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
5. Periodically (daily, weekly, or monthly), rebuild or reorganize the indexes of your databases to remove logical
fragmentation and wasted space.
6. Periodically, as part of your scheduled database maintenance tasks, 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 SQL Server service, you may find that the current SQL Server log gets very large and takes
a long time to load and view. You can close the current error log and create a new one by running
sp_cycle_errorlog or DBCC ERRORLOG, so that the log doesn’t get overly large. 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.

SQL Server Configuration Settings

1. SQL Server configuration settings should remain at their default settings. Any changes to these settings should only
be made by an experienced DBA who understands the pros and cons of making changes.
2. If you are using the 32-bit version of SQL Server, and if you are using 4 GB or more of RAM, ensure that you have
all the AWE settings correctly set.
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. Exceptions to this rule of thumb can come into play if you use
32-bit AWE memory, or 64-bit memory, where you may have to change the “maximum server memory” to an
amount that is less than the physical amount of RAM in your server.

Database Settings

1. Leave the “auto create statistics” and “auto update statistics” options on for all user databases. Only in very rare
cases should these be turned off, and if they are turned off, then you must manually update the statistics yourself.
2. Don't be tempted to use the “auto shrink” database option, as it can waste SQL Server resources unnecessarily and
contribute to index fragmentation. Instead, if you need to shrink a database, do so manually.
3. 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.

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.

High Availability Best Practices

General High Availability

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 RAID-enabled direct attached storage 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 from a high availability standpoint.
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 thorough 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.

Disaster Recovery

1. You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
2. As your SQL Servers change over time, don’t forget to update your disaster recovery plan.
3. Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not
assume a DBA will be rebuilding the servers.
4. Fully test your disaster recovery plan at least once a year.
5. Review all the best practices you know and implement them as appropriate. Remember, as DBAs, we are
guardians of the organization's data. This is a huge responsibility.

Backup

1. All OLTP production databases should be set to use the full recovery model. This way, you can create transaction
log backups on a periodic basis.
2. Whenever possible, perform a daily full backup of all system and user databases.
3. For all OLTP production databases, perform regular transaction log backups, at least once an hour.
4. Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
5. Periodically perform test backup restores to ensure that your backups are good and can be restored.
6. Encrypt your backups in case they should become “lost.”
7. Store backups offsite and in a secure location.
8. If using SQL Server encryption, be sure to back up the appropriate service master keys, database master keys, and
certificates.
9. If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much
space on your storage device, consider a third-party backup program, such as Red Gate SQL Backup Pro.
10. Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You
don’t want to be looking this information up during an emergency.

Clustering

1. Detailed planning is critical to the success of every SQL Server cluster installation. Fully plan the install before
performing the actual install.
2. An expensive cluster is of little value if the supporting infrastructure is not also fault tolerant. For example, don’t
forget power redundancy, network redundancy, etc.
3. Run only a single instance of SQL Server per node. Whether you have two or eight nodes in your cluster, leave at
least one node as a failover node.
4. Cluster nodes must not be domain controllers, and all nodes must belong in the same domain and should have
access to two or more domain controllers.
5. All cluster hardware must be on the Microsoft Windows Clustering Hardware Compatibility List, and certified to
work together as part of a cluster.
6. Since clustering is not designed to protect data (only SQL Server instances), the shared storage device used by the
cluster must incorporate fault tolerant technology. Consider log shipping, database mirroring, or SAN replication
to further protect your production databases.
7. Before installing Windows Clustering and SQL Server Clustering, be sure that all drivers and software are up-to-
date, including the latest service packs or hot fixes.
8. Each node of a cluster should have identical hardware, drivers, software, and configuration settings.
9. Fiber channel shared arrays are preferred over SCSI when building a cluster, and Fiber channel has to be used if you
include more than two nodes in your cluster.
10. In conventional clusters, the quorum drive must be on its own fault-tolerant, dedicated, disk of 500MB or larger
11. Once the cluster has been installed, test it thoroughly for every possible failure scenario.
12. Do not run antivirus or antispyware on a SQL Server cluster.
13. Before you begin to install Cluster Services or SQL Server clustering, determine your virtual names and gather
your IP addresses ahead of time. This makes it easier when it is time to enter this information when installing and
configuring your cluster.
14. Monitor active production clusters on a daily basis, looking for any potential problems. Periodically test failover on
production servers to ensure all is working well.
15. Once you have a stable SQL Server Cluster running, be very leery about making any changes to it, whatsoever.

SQL Server 2005/2008 Mirroring

1. The principal database and the mirror database should be on separate physical hardware, and ideally, in different
physical locations.
2. The witness server should be on separate physical hardware, and be on a separate network (best if at a third
location).
3. Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect
performance of the production database being mirrored.
4. Use high availability mode whenever possible, and high performance mode only when required.
5. For ease of administration, the hardware, along with the OS and SQL Server configuration, should be identical (at
least very similar) between the two servers.
6. While a fast connection is not required between mirrored servers, the faster the connection, and the better quality
the connection, the better.
7. You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead
caused by the mirroring process itself.
8. Thoroughly test database mirroring before putting it into production.
9. Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
10. Develop a formal operational and recovery procedure (and document) to support mirroring. Periodically test the
failover process to ensure that it works.

Log Shipping

1. If you don’t currently employ clustering or database mirroring for your SQL Servers because of cost, consider
employing log shipping to help boost your high availability. It provides reasonably high availability at low cost.
2. If you take advantage of SQL Server log shipping capability, you will want to keep the log shipping monitoring
service on a SQL Server of its own, not on the source or destination servers participating in log shipping. Not only
is this important for fault tolerance, but because the log shipping monitoring service incurs overhead that can affect
the performance of the source and destination servers.
3. Monitor log shipping daily to ensure that is working successfully.
4. Learn what you need to know to fix log shipping if synchronization is lost between the production and backup
databases.
5. Document, and test your server recovery plan, so you will be ready in case of a server failure.

My apology if this is too long to read.

- - - Updated - - -

allthough daghan pa kaayo kog tips and advices pero as of now kana lang sa kay ga sugud paman ka as DB Admin.

[makal34]

basic lng sah sa DB.. unya nlng nang pang master inig maabot nka ug 3-6 months dha nka start ug explore

[nyor]

Let me allow to enumerate it one by one so here it is:

General DBA Best Practices

Day-to-Day

1. Check OS Event Logs and SQL Server Logs for unusual events.
2. Verify that all scheduled jobs have run successfully.
3. Confirm that backups have been made and successfully saved to a secure location.
4. Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should
have 15% or more of free space.
5. Throughout the day, periodically monitor performance using both System Monitor and Profiler/SQL Trace.
6. Regularly monitor and identify blocking issues.
7. Keep a log of any changes you make to servers, including documentation of any performance issues you identify
and correct.
8. Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as
needed.
9. Regularly restore backups to a test server in order to verify that you can restore them. You don’t need to restore
all backups every day, but do so often to ensure that you are confident you have good backups.
10. Take some time to learn something new as a DBA to further your professional development.

Installation

1. Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
2. If possible, install and configure all of your SQL Server instances consistently, following an agreed-upon
organization standard.
3. Don't install SQL Server services on instances that don’t use them, such as Microsoft Full-Text Indexing, Reporting
Services, or Analysis Services.
4. For best performance of SQL Server running under Windows, turn off any operating system services that aren't
needed.
5. For optimum SQL Server performance, you want to dedicate your physical servers to only running a single
instance of SQL Server, along with no other applications.
6. For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate spindles to isolate disk
access patterns.
7. If tempdb will be used heavily, put it on its own separate spindles.
8. Do not install SQL Server on a domain controller.
9. Be sure that SQL Server is installed on an NTFS partition.
10. Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.

Upgrading

1. Run the SQL Server 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.
This is because statistics are not automatically updated during the upgrade process.

Security

1. Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access 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. Instead, use a Windows
Authentication account to access SQL Server as a sysadmin.
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. Disable the guest user account from each user database by using REVOKE CONNECT FROM GUEST.
13. Don’t use 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. Configure 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. In SQL Server 2005 and earlier, remove the BUILTIN/Administrators group to prevent local server administrators
from being able to access SQL Server. In SQL Server 2008, the BUILTIN/Administrators group does not exist by
default.
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.
24. When using distributed queries, use linked servers instead of remote servers. Remote servers only exist for
backward compatibility.
25. Do not browse the web from a production SQL Server instance.
26. Instead of installing virus/antispyware protection on a SQL Server, perform 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 Red Gate SQL Backup Pro.
29. Only enable C2 auditing or Common Criteria compliance if required, as they add significant performance
overhead.
30. Consider running a SQL Server security scanner against your SQL servers to identify security holes.
31. Consider enabling SSL or IPSEC for connections between SQL Server and its clients.
32. If using SQL Server 2005/2008, enable password policy checking.
33. If using SQL Server 2008 in a high security environment, consider implementing Transparent Data 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/2008 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.

Job Maintenance

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.
3. Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
4. If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
5. Periodically (daily, weekly, or monthly), rebuild or reorganize the indexes of your databases to remove logical
fragmentation and wasted space.
6. Periodically, as part of your scheduled database maintenance tasks, 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 SQL Server service, you may find that the current SQL Server log gets very large and takes
a long time to load and view. You can close the current error log and create a new one by running
sp_cycle_errorlog or DBCC ERRORLOG, so that the log doesn’t get overly large. 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.

SQL Server Configuration Settings

1. SQL Server configuration settings should remain at their default settings. Any changes to these settings should only
be made by an experienced DBA who understands the pros and cons of making changes.
2. If you are using the 32-bit version of SQL Server, and if you are using 4 GB or more of RAM, ensure that you have
all the AWE settings correctly set.
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. Exceptions to this rule of thumb can come into play if you use
32-bit AWE memory, or 64-bit memory, where you may have to change the “maximum server memory” to an
amount that is less than the physical amount of RAM in your server.

Database Settings

1. Leave the “auto create statistics” and “auto update statistics” options on for all user databases. Only in very rare
cases should these be turned off, and if they are turned off, then you must manually update the statistics yourself.
2. Don't be tempted to use the “auto shrink” database option, as it can waste SQL Server resources unnecessarily and
contribute to index fragmentation. Instead, if you need to shrink a database, do so manually.
3. 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.

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.

High Availability Best Practices

General High Availability

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 RAID-enabled direct attached storage 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 from a high availability standpoint.
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 thorough 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.

Disaster Recovery

1. You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
2. As your SQL Servers change over time, don’t forget to update your disaster recovery plan.
3. Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not
assume a DBA will be rebuilding the servers.
4. Fully test your disaster recovery plan at least once a year.
5. Review all the best practices you know and implement them as appropriate. Remember, as DBAs, we are
guardians of the organization's data. This is a huge responsibility.

Backup

1. All OLTP production databases should be set to use the full recovery model. This way, you can create transaction
log backups on a periodic basis.
2. Whenever possible, perform a daily full backup of all system and user databases.
3. For all OLTP production databases, perform regular transaction log backups, at least once an hour.
4. Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
5. Periodically perform test backup restores to ensure that your backups are good and can be restored.
6. Encrypt your backups in case they should become “lost.”
7. Store backups offsite and in a secure location.
8. If using SQL Server encryption, be sure to back up the appropriate service master keys, database master keys, and
certificates.
9. If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much
space on your storage device, consider a third-party backup program, such as Red Gate SQL Backup Pro.
10. Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You
don’t want to be looking this information up during an emergency.

Clustering

1. Detailed planning is critical to the success of every SQL Server cluster installation. Fully plan the install before
performing the actual install.
2. An expensive cluster is of little value if the supporting infrastructure is not also fault tolerant. For example, don’t
forget power redundancy, network redundancy, etc.
3. Run only a single instance of SQL Server per node. Whether you have two or eight nodes in your cluster, leave at
least one node as a failover node.
4. Cluster nodes must not be domain controllers, and all nodes must belong in the same domain and should have
access to two or more domain controllers.
5. All cluster hardware must be on the Microsoft Windows Clustering Hardware Compatibility List, and certified to
work together as part of a cluster.
6. Since clustering is not designed to protect data (only SQL Server instances), the shared storage device used by the
cluster must incorporate fault tolerant technology. Consider log shipping, database mirroring, or SAN replication
to further protect your production databases.
7. Before installing Windows Clustering and SQL Server Clustering, be sure that all drivers and software are up-to-
date, including the latest service packs or hot fixes.
8. Each node of a cluster should have identical hardware, drivers, software, and configuration settings.
9. Fiber channel shared arrays are preferred over SCSI when building a cluster, and Fiber channel has to be used if you
include more than two nodes in your cluster.
10. In conventional clusters, the quorum drive must be on its own fault-tolerant, dedicated, disk of 500MB or larger
11. Once the cluster has been installed, test it thoroughly for every possible failure scenario.
12. Do not run antivirus or antispyware on a SQL Server cluster.
13. Before you begin to install Cluster Services or SQL Server clustering, determine your virtual names and gather
your IP addresses ahead of time. This makes it easier when it is time to enter this information when installing and
configuring your cluster.
14. Monitor active production clusters on a daily basis, looking for any potential problems. Periodically test failover on
production servers to ensure all is working well.
15. Once you have a stable SQL Server Cluster running, be very leery about making any changes to it, whatsoever.

SQL Server 2005/2008 Mirroring

1. The principal database and the mirror database should be on separate physical hardware, and ideally, in different
physical locations.
2. The witness server should be on separate physical hardware, and be on a separate network (best if at a third
location).
3. Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect
performance of the production database being mirrored.
4. Use high availability mode whenever possible, and high performance mode only when required.
5. For ease of administration, the hardware, along with the OS and SQL Server configuration, should be identical (at
least very similar) between the two servers.
6. While a fast connection is not required between mirrored servers, the faster the connection, and the better quality
the connection, the better.
7. You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead
caused by the mirroring process itself.
8. Thoroughly test database mirroring before putting it into production.
9. Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
10. Develop a formal operational and recovery procedure (and document) to support mirroring. Periodically test the
failover process to ensure that it works.

Log Shipping

1. If you don’t currently employ clustering or database mirroring for your SQL Servers because of cost, consider
employing log shipping to help boost your high availability. It provides reasonably high availability at low cost.
2. If you take advantage of SQL Server log shipping capability, you will want to keep the log shipping monitoring
service on a SQL Server of its own, not on the source or destination servers participating in log shipping. Not only
is this important for fault tolerance, but because the log shipping monitoring service incurs overhead that can affect
the performance of the source and destination servers.
3. Monitor log shipping daily to ensure that is working successfully.
4. Learn what you need to know to fix log shipping if synchronization is lost between the production and backup
databases.
5. Document, and test your server recovery plan, so you will be ready in case of a server failure.

My apology if this is too long to read.

- - - Updated - - -

allthough daghan pa kaayo kog tips and advices pero as of now kana lang sa kay ga sugud paman ka as DB Admin.

salamat ani sir, sa day-to-day noon halos nabuhat na nko.. about sa installation naa rmn ko test server ky ad2 mn ko mag simulate ug disaster rocovery.. sa ugrade wla mn nko na testing.. sa maintenance ang pag rebuild ug reorganize sa indexes wla sad nko nasuwayan..

[defender_1611]

salamat ani sir, sa day-to-day noon halos nabuhat na nko.. about sa installation naa rmn ko test server ky ad2 mn ko mag simulate ug disaster rocovery.. sa ugrade wla mn nko na testing.. sa maintenance ang pag rebuild ug reorganize sa indexes wla sad nko nasuwayan..

mao nay mga basic sa pag administer ug DB sir.

[rics zalved]

taasa ana @defender_1611 wui hehe, db administrator jud ka bay hehe

[defender_1611]

taasa ana @defender_1611 wui hehe, db administrator jud ka bay hehe

ahahahah...dugay naman kong db administratrator bay....

[nyor]

@defender_1611 sir, unsa.on pag audit sa database?