mga MSSQL admin please share your ideas and knowledge

[defender_1611]

---

@nyor: here are the steps on how to audit database.


Step 1: Create a Folder

Create a folder, C:\SQLAudit, as shown below.

PicPaste - 1-laNz9DT3.png

Step2: Create the Server Audit

Create the Server Audit Object using the following command.

CREATE SERVER AUDIT Compliance_Database_Audit
TO FILE (FILEPATH='C:\SQLAudit\', MAXSIZE = 5MB,
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON)

Note: Compliance_Database_Audit is the name of the Server Audit. This command allocates 5MB of disk space for this audit. When the 5MB file is filled up, it automatically rolls over to another file up to a maximum number of 100 Files. When the audit restarts the next time, it evaluates the number of files and delete any older files.

Step 3: Enable the Server Audit

By default, the server audit is disabled. Enable the server audit using the following command.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = ON)

Note: Once the audit starts, the OS allocates 5MB disk space for the audit file.

PicPaste - 2-1wap2Njh.png

Step 4: Add the Database Audit specification

The database audit specification is the list of action groups that the Server Audit will audit. Let's add a couple of action groups when creating the Audit specification.

CREATE DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = OFF)

Note: Compliance_Database_Audit_Spec is the name of the Server Audit specification.

Step 5: Update the Database Audit specification

If you want to add action groups to the existing database audit specification, you could use the following statement.

ALTER DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

Note: You can skip step 4 if you have already listed all of the action groups in Step 4.

Step 6: Enable Server Audit specification

Use the following command to enable Audit specification.

ALTER DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit WITH (STATE = ON)

Step 7: Read Audit data

Execute the following command to read the AUDIT data from the binary file. Refer to Figure 1.2.

SELECT event_time, action_id,
succeeded, session_id, session_server_principal_name, target_server_principal_name,
target_database_principal_name, database_name, database_principal_name, schema_name,
object_name, statement FROM sys.fn_get_audit_file ('C:\SQLAudit\*Compliance_Database_Audit_*.sqlaudi t',default,default);

PicPaste - 3-0aSgjMR0.png

Step 8: Test Audit

We have the following action group added to the Audit specification.

DATABASE_ROLE_MEMBER_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP
DATABASE_PERMISSION_CHANGE_GROUP
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

Let's test the action group by executing the following command.

use master
go
/****** Object: Login [MyLogin] Script Date: 01/10/2010 03:47:53 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin')
DROP LOGIN [MyLogin]
GO
Create Login MyLogin with password = 'P@ssWord'
go
/****** Object: Login [MyLogin2] Script Date: 01/10/2010 03:48:10 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin2')
DROP LOGIN [MyLogin2]
GO
Create Login MyLogin2 with password = 'P@ssWord'
go
USE [master]
GO
/****** Object: Database [MyDB] Script Date: 01/10/2010 03:48:19 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO
Create database MyDB
go
use MyDB
go
USE [MyDB]
GO

/****** Object: Table [dbo].[mytable] Script Date: 01/10/2010 03:48:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO

create table mytable (id int)
go
USE [MyDB]
GO
/****** Object: User [MyLogin] Script Date: 01/10/2010 03:48:56 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin')
DROP USER [MyLogin]
GO
create user MyLogin for Login MyLogin
go
USE [MyDB]
GO

/****** Object: User [MyLogin2] Script Date: 01/10/2010 03:49:13 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin2')
DROP USER [MyLogin2]
GO

create user MyLogin2 for Login MyLogin2
go
USE [MyDB]
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin'
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_droprolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_changedbowner N'MyLogin2'
GO

Now let's see if these actions have been logged to the binary file under c:\SQLAudit by executing the followoing command.

SELECT event_time, action_id,
succeeded, session_id, session_server_principal_name, target_server_principal_name,
target_database_principal_name, database_name, database_principal_name, schema_name,
object_name, statement FROM sys.fn_get_audit_file ('C:\SQLAudit\*Compliance_Database_Audit_*.sqlaudi t',default,default);

PicPaste - 4-8vw8mamg.png

Step 9: Check the Audit metadata

Use the following commands to view metadata information on the server audit and audit specification.

SELECT * FROM sys.server_audits
SELECT * FROM sys.Database_audit_specifications

PicPaste - 5-clKKamOr.png

View metadata information on the server audit and audit specification

You also check the extended events that are created by SQL server related to this audit using the following Dynamic management view command.

select s.name as [session name], se.event_name as [event name],
p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address

PicPaste - 6-GSraMCgz.png

check the extended events that are created by SQL server

Step 10: Stop Server AUDIT

Server audit can be stopped using the ALTER SERVER AUDIT command a shown below.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = OFF)

Note: When the audit is stopped, SQL Server releases the free space on the audit files to the Operating System.

PicPaste - 7-vZ7vCW6n.png

just click the link to see the screenshots.

[nyor]

@nyor: here are the steps on how to audit database.


Step 1: Create a Folder

Create a folder, C:\SQLAudit, as shown below.

PicPaste - 1-laNz9DT3.png

Step2: Create the Server Audit

Create the Server Audit Object using the following command.

CREATE SERVER AUDIT Compliance_Database_Audit
TO FILE (FILEPATH='C:\SQLAudit\', MAXSIZE = 5MB,
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON)

Note: Compliance_Database_Audit is the name of the Server Audit. This command allocates 5MB of disk space for this audit. When the 5MB file is filled up, it automatically rolls over to another file up to a maximum number of 100 Files. When the audit restarts the next time, it evaluates the number of files and delete any older files.

Step 3: Enable the Server Audit

By default, the server audit is disabled. Enable the server audit using the following command.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = ON)

Note: Once the audit starts, the OS allocates 5MB disk space for the audit file.

PicPaste - 2-1wap2Njh.png

Step 4: Add the Database Audit specification

The database audit specification is the list of action groups that the Server Audit will audit. Let's add a couple of action groups when creating the Audit specification.

CREATE DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = OFF)

Note: Compliance_Database_Audit_Spec is the name of the Server Audit specification.

Step 5: Update the Database Audit specification

If you want to add action groups to the existing database audit specification, you could use the following statement.

ALTER DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

Note: You can skip step 4 if you have already listed all of the action groups in Step 4.

Step 6: Enable Server Audit specification

Use the following command to enable Audit specification.

ALTER DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
FOR SERVER AUDIT Compliance_Database_Audit WITH (STATE = ON)

Step 7: Read Audit data

Execute the following command to read the AUDIT data from the binary file. Refer to Figure 1.2.

SELECT event_time, action_id,
succeeded, session_id, session_server_principal_name, target_server_principal_name,
target_database_principal_name, database_name, database_principal_name, schema_name,
object_name, statement FROM sys.fn_get_audit_file ('C:\SQLAudit\*Compliance_Database_Audit_*.sqlaudi t',default,default);

PicPaste - 3-0aSgjMR0.png

Step 8: Test Audit

We have the following action group added to the Audit specification.

DATABASE_ROLE_MEMBER_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP
DATABASE_PERMISSION_CHANGE_GROUP
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

Let's test the action group by executing the following command.

use master
go
/****** Object: Login [MyLogin] Script Date: 01/10/2010 03:47:53 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin')
DROP LOGIN [MyLogin]
GO
Create Login MyLogin with password = 'P@ssWord'
go
/****** Object: Login [MyLogin2] Script Date: 01/10/2010 03:48:10 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin2')
DROP LOGIN [MyLogin2]
GO
Create Login MyLogin2 with password = 'P@ssWord'
go
USE [master]
GO
/****** Object: Database [MyDB] Script Date: 01/10/2010 03:48:19 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO
Create database MyDB
go
use MyDB
go
USE [MyDB]
GO

/****** Object: Table [dbo].[mytable] Script Date: 01/10/2010 03:48:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO

create table mytable (id int)
go
USE [MyDB]
GO
/****** Object: User [MyLogin] Script Date: 01/10/2010 03:48:56 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin')
DROP USER [MyLogin]
GO
create user MyLogin for Login MyLogin
go
USE [MyDB]
GO

/****** Object: User [MyLogin2] Script Date: 01/10/2010 03:49:13 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin2')
DROP USER [MyLogin2]
GO

create user MyLogin2 for Login MyLogin2
go
USE [MyDB]
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin'
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_droprolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_changedbowner N'MyLogin2'
GO

Now let's see if these actions have been logged to the binary file under c:\SQLAudit by executing the followoing command.

SELECT event_time, action_id,
succeeded, session_id, session_server_principal_name, target_server_principal_name,
target_database_principal_name, database_name, database_principal_name, schema_name,
object_name, statement FROM sys.fn_get_audit_file ('C:\SQLAudit\*Compliance_Database_Audit_*.sqlaudi t',default,default);

PicPaste - 4-8vw8mamg.png

Step 9: Check the Audit metadata

Use the following commands to view metadata information on the server audit and audit specification.

SELECT * FROM sys.server_audits
SELECT * FROM sys.Database_audit_specifications

PicPaste - 5-clKKamOr.png

View metadata information on the server audit and audit specification

You also check the extended events that are created by SQL server related to this audit using the following Dynamic management view command.

select s.name as [session name], se.event_name as [event name],
p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address

PicPaste - 6-GSraMCgz.png

check the extended events that are created by SQL server

Step 10: Stop Server AUDIT

Server audit can be stopped using the ALTER SERVER AUDIT command a shown below.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = OFF)

Note: When the audit is stopped, SQL Server releases the free space on the audit files to the Operating System.

PicPaste - 7-vZ7vCW6n.png

just click the link to see the screenshots.

sir, salamat kaau ani imong tubag nga detail kaau.. mahimo ni nga notes nako.. pero karon pko nka hibaw nga dili d.ai available ang auditing sa current namo nga sql gigamit 2008 nga standard.. ako lng nya ni e.try sa 2012.. pwede nmn ka ma instructor ug training sa sql sir.. ky kato instructor nmo pag training simple rmn kaau ang gipang tudlo.. haha

[defender_1611]

i was once an instructor before.

[rics zalved]

DI na si @defender_1611, Database Instructor ba hehe

[defender_1611]

DI na si @defender_1611, Database Instructor ba hehe

aw. ahahaha....

[nyor]

i was once an instructor before.

sir, asa akong na bantayan isa nlng ga offer ug SQL nga training ky ang uban sa manila na mo training ky mga DBA pud ang trainer.

[aninsky]

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.

mga master dara ang link ana basin ganahan mo...

https://www.simple-talk.com/sql/data...dba-checklist/

[defender_1611]

sir, asa akong na bantayan isa nlng ga offer ug SQL nga training ky ang uban sa manila na mo training ky mga DBA pud ang trainer.

o kato akoa sa manila man pod to.

[nyor]

mga master dara ang link ana basin ganahan mo...

https://www.simple-talk.com/sql/data...dba-checklist/

bitaw sa, maau nimo pagpangita..

[jhappy]

usa pd every system should be added in our monitoring servers , nagios for alerts and cacti for performance visualization to name a few