Server Performance and Activity Monitoring How-to Topics

SQL Server performance info:

http://msdn.microsoft.com/en-us/library/ms191511(v=sql.90).aspx

SQL Server SYNONYM

First introduced in SQL Server 2005.

SQL Server SYNONYM's can be very useful and can be created for

• Tables
• Views
• Assembly Stored Procedures, Table Valued Functions, Aggregations
• SQL Scalar Functions
• SQL Stored Procedures
• SQL Table Valued Functions
• SQL Inline-Table-Valued Functions
• Local and Global Temporary Tables
• Replication-filter-procedures
• Extended Stored Procedures

Benefits:

• SYNONYMs provide a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations:

• SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
• Obviously consumes possible object names, as you can not create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
• SYNONYM can not be referenced in a DDL statement

If we use this option frequently, then keping in mind the facts that:

• you can delete a SYNONYM without getting any warning that it is being referenced by any other database object
• the object for which the SYNONYM is being created is checked at run time. It is not checked at creation time.
• It makes it imperitive that we should plan and monitor synonyms bit more than other objects/references in SQL Server.
• An additional limitation is that you can't use TRUNCATE TABLE on a synonym.
• The SSIS Data Profiling task cannot see synonyms

SQL Server synonym links:
http://www.mssqltips.com/sqlservertip/1576/benefits-and-limitations-of-using-synonyms-in-sql-server-2005/
http://connect.microsoft.com/SQLServer/feedback/details/311079/expand-synonym-to-other-entities-database-linked-server
http://msdn.microsoft.com/en-us/library/ms187552.aspx

Remote Diagnostic Agent (RDA)

1. Remote Diagnostic Agent (RDA) is a command-line diagnostic tool that is executed by an engine written in the Perl programming language. RDA provides a unified package of support diagnostics tools and preventive solutions. The data captured provides Oracle Support with a comprehensive picture of the customer's environment which aids in problem diagnosis.

2. Oracle document: Remote Diagnostic Agent (RDA) 4 - Getting Started [ID 314422.1]

3. RDA supports different platforms: Windows, UNIX, Linux

4. RDA supports lots of Oracle products: Oracle RDBMS Server (Standard and Enterprise Editions), etc

5. RDA download: a zip file for specific platform

6. Installation on Solaris: unzip

7. run RDA
--check perl version: perl -V
--rda.sh - Use this command if Perl is not available.
--rda.pl - Use this command if Perl is available.
--./rda.pl -S: set up setup.cfg
--run RDA report: ./rda.pl -v
  --ask for password for system to connect to the database

Oracle changePerm.sh script in versions 9.2.0.8, Oracle 10g

changePerm.sh is a script that allows users that do not belong to the "dba" group to access and execute files in the ORACLE_HOME directory.


The script is located in the ORACLE_HOME/install directory on Unix and Linux systems (there is no equivalent for Windows). This script ships with Oracle Database versions 9.2.0.8, Oracle 10g.

The changePerm.sh file is no more available nor necessary for Oracle database server 11.1.0.x because in 11g, permissions under the Oracle Home (including "others") are set correctly/relaxed. (oracle note 834626.1)


===================================
Before change:
$ ls -ld $ORACLE_HOME/lib
drwxr-x---   3 oracle   dba         5632 Nov 27 09:30 /fs/oracle/product/10204ee/lib

Running script:

$ cd $ORACLE_HOME/install

$ ls -l

total 964

-rwxr-xr-x   1 oracle   dba        11380 Nov 22  2007 changePerm.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed1.sh

-rw-r-----   1 oracle   dba          618 Nov 26 14:19 envVars.properties

-rw-r-----   1 oracle   dba          131 Nov 26 13:51 envVars.properties.bak

-rwxr-xr-x   1 oracle   dba           38 Apr 19  2005 install.excl

drwxr-x---   2 oracle   dba          512 Nov 26 11:55 jlib

-rw-r-----   1 oracle   dba       437788 Nov 26 14:19 make.log

-rw-r-----   1 oracle   dba          102 Nov 26 14:19 portlist.ini

-rw-r--r--   1 oracle   dba          124 Nov 26 14:19 readme.txt

-rw-r--r--   1 oracle   dba         7609 Jan 12  2007 restrict.lst

-rwxr-xr-x   1 oracle   dba          822 Nov 26 14:16 rootdeletenode.sh

-rw-r--r--   1 oracle   dba        10031 Nov 26 14:16 rootlocaladd

-rw-r-----   1 oracle   dba            0 Jun  7  2005 seed.log

-rw-r--r--   1 oracle   dba         2808 Jul 14  2005 templocal

drwxr-x---   2 oracle   dba          512 Nov 26 14:19 unix

drwxr-x---   2 oracle   dba          512 Nov 26 14:13 utl

$ ./changePerm.sh

-------------------------------------------------------------------------------

Disclaimer: The purpose of this script is to relax permissions on some of the

files in the database Oracle Home so that all clients can access them.

Please note that Oracle Corporation recommends using the most restrictive file

permissions as possible for your given implementation.  Running this script

should be done only after considering all security ramifications.

-------------------------------------------------------------------------------

-n Do you wish to continue (y/n) [n]:

y

Spooling the error log /tmp/changePerm_err.log...

Finished running the script successfully

After change:

$ ls -ld $ORACLE_HOME/lib
drwxr-xr-x   3 oracle   dba         5632 Nov 27 09:40 /fs/oracle/product/10204ee/lib

===================================
Reference: http://www.orafaq.com/wiki/ChangePerm_sh

EXCEL tips

1. How to enter soft break in EXCEL
ALT+ENTER

2. How to fill an Excel column with consecutive numbers without typing each one separately?

Put 1 in the first row, first column.
Put 2 in the 2nd row, first column.
Highlight both. Then drag your cursor down. They will auto-number themselves.
or
Click and drag the first value that enter into the first cell.

Then go to Edit / Fill / series .. and click on "step value" and enter 1.

Oracle Database Automatic Startup and Shutdown on UNIX

Reference:
http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php

• Automating Shutdown and Startup (11.2)
• Automating Shutdown and Startup (10.2)
• Automating Startup and Shutdown (10.1)
• Automating Database Startup and Shutdown (9.2)

Oracle 10gR2 and RMAN duplicate and errors: ORA-19870, ORA-19505, ORA-27037

When coping backup files from target database server to auxiliary database server, then trying to duplicate a database on a new server using Oracle RMAN 10gR2, the following error occurred:

channel ORA_AUX_DISK_1: reading from backup piece /backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck

ORA-19870: error reading backup piece /backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck

ORA-19505: failed to identify file "/backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck"

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

failover to previous backup

Solution: Add backup files to the Recovery Catalog

Cataloging Older Files in the Recovery Catalog

If you have datafile copies, backup pieces or archive logs on disk, you can catalog them in the recovery catalog using the CATALOG command. When using a recovery catalog, cataloging older backups that have aged out of the control file lets RMAN use the older backups during restore operations. 

For example:

RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';
RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', 
     '/disk1/arch_logs/archive1_732.dbf';
RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

You can also catalog multiple backup files in a directory at once, using the CATALOG START WITH command, as shown in this example:

RMAN> CATALOG START WITH '/disk1/backups/';

Reference: http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmrepos.htm#1006296

Principals - SQL Server

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

The SQL Server sa Login

---

The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.

public Database Role

---

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted topublic on that securable.

INFORMATION_SCHEMA and sys

Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and they cannot be modified or dropped.

Certificate-based SQL Server Logins

---

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

• ##MS_SQLResourceSigningCertificate##
• ##MS_SQLReplicationSigningCertificate##
• ##MS_SQLAuthenticatorCertificate##
• ##MS_AgentSigningCertificate##
• ##MS_PolicyEventProcessingLogin##
• ##MS_PolicySigningCertificate##
• ##MS_PolicyTsqlExecutionLogin##

Client and Database Server

---

By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.

For more information about the SQL Server implementation of Kerberos support, see Kerberos Authentication and SQL Server.

from: http://msdn.microsoft.com/en-us/library/ms181127(v=sql.105).aspx

BUILTIN\Administrator and SQL Server 2008

BUILTIN\Administrator

By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.

If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server. Accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group.

Refer to: http://msdn.microsoft.com/en-us/library/cc280562(v=sql.105).aspx

Permissions of SQL Server Fixed Database Roles

Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server. 

Fixed database roles are provided for convenience and backward compatibility. 

All fixed database roles are granted with Server-level permission VIEW ANY DATABASE.

Fixed database role	Database-level permission
db_accessadmin	Granted: ALTER ANY USER, CREATE SCHEMA Granted with GRANT option: CONNECT
db_backupoperator	Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader	Granted: SELECT
db_datawriter	Granted: DELETE, INSERT, UPDATE
db_ddladmin	Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
db_denydatareader	Denied: SELECT
db_denydatawriter	Denied: DELETE, INSERT, UPDATE
db_owner	Granted with GRANT option: CONTROL
db_securityadmin	Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
dbm_monitor	Granted: VIEW most recent status in Database Mirroring Monitor Important The dbm_monitor fixed database role is created in the msdb database when the first database is registered in Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role.

For more info: http://msdn.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

Changing SQL Server Database Owner

Changing the SQL Server Database Owner

In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database.

Ownership of the system databases cannot be changed.

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.

ALTER AUTHORIZATION ON DATABASE::[my_db] TO [sa];

Reference:

Changing the Database Owner: http://msdn.microsoft.com/en-us/library/ms190909(v=sql.105).aspx

ALTER AUTHORIZATION (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187359(v=sql.105).aspx

guest user should not be disabled in the msdb database in SQL Server

guest user should not be disabled in the msdb database in SQL Server

SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. This recommendation does not apply to master, msdb, and tempb system databases.

In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database.

More info: http://support.microsoft.com/kb/2539091

Viewing Database Metadata in SQL Server 2008 R2

You can view database, file, partition, and filegroup properties using a variety of catalog views, system functions, and system stored procedures.

The following table lists the catalog views, system functions, and system stored procedures that return information about databases, files, and filegroups.

Views	Functions	Stored procedures and other statements
sys.databases	DATABASE_PRINCIPAL_ID	sp_databases
sys.database_files	DATABASEPROPERTYEX	sp_helpdb
sys.data_spaces	DB_ID	sp_helpfile
sys.filegroups	DB_NAME	sp_helpfilegroup
sys.allocation_units	FILE_ID	sp_spaceused
sys.master_files	FILE_IDEX	DBCC SQLPERF
sys.partitions	FILE_NAME
sys.partition_functions	FILEGROUP_ID
sys.partition_parameters	FILEGROUP_NAME
sys.partition_range_values	FILEGROUPPROPERTY
sys.partition_schemes	FILEPROPERTY
sys.dm_db_partition_stats	fn_virtualfilestats
sys.dm_db_file_space_usage (Transact-SQL) (tempdb only)
sys.dm_db_session_space_usage (Transact-SQL)(tempdb only)
sys.dm_db_task_space_usage (Transact-SQL) (tempdb only)

Some columns in the sys.databases catalog view and properties in the DATABASEPROPERTYEX function may return a NULL value if the specified database is not available. For example, to return the collation name of a database, the database must be accessed. If the database is not online, or the AUTO_CLOSE option is set to ON, the collation name cannot be returned.

SQL Server Version Components and Numbering

SQL Server Version Components and Numbering

The standard product version format for SQL Server is MM.nn.bbbb.rr where each segment is defined as:

• MM - Major version
• nn - Minor version
• bbbb - Build number
• rr - Build revision number

In each major or minor release of SQL Server, there is an increment to the version number to differentiate it from earlier versions. This change to the version is used for many purposes. This includes displaying version information in the user interface, controlling how files are replaced during upgrade, applying service packs, and also as a mechanism for functional differentiation between the successive versions.

For example, SQL Server 2008 R2 is a minor upgrade of SQL Server 2008. This means that it shares the same major version number with SQL Server 2008, and has an increment to the minor version. 

The product version for SQL Server 2008 R2 is 10.50.bbbb.rr. Compare this with the product version for SQL Server 2008 which is 10.0.bbbb.rr.

List of Differences between SQL Server and Oracle

List of Differences between SQL Server and Oracle

1. In SQL Server, only one instance runs per platform.

2. SQL Server tablespaces are called databases, and lie half way between an oracle tablespace and an oracle instance.

3. Five databases always exist: master (=system); tempdb (=temp); distribution (for distributed transactions); model (the template for all new database/tablespaces); and msdb (automatic scheduled jobs information). Three sample databases are also created on install, but can be safely deleted: pubs; northwind; user1.

4. SQL Server databases comprise one or more datafiles, as in oracle, but also have one or more online redo log files. This is the principal difference between SQL Server and oracle; the idea of the temporary tablespace, system tablespace, and each schema tablespace having their own set of online redo logs takes some getting used to.

5. SQL Server has logins and users. Logins are specific for a SQL Server instance. Users are specific to a database. A login will have one user for every database it has access to. Logins have systemic privileges (create database, etc.), Users have DDL and DML privileges. Standard practice is to name users the as their parent login - this is done by default.

6. Users with execute permission on a procedure / select permission on a view will fail to use this if there is broken ownership with referenced objects, unless they are explicitly given access to the referenced table.

7. Revoke (neutral) and Deny (strong) are both available in place of Oracle Revoke. The difference is whether or not they can be overridden by role or user privileges. Deny appears as a red cross, Grant as a green tick, and Revoke does not appear in Enterprise Manager privilege box. [p109]

8. Objects can have their schema changed in SQL Server without being rebuilt, using sp_changeobjectowner object, owner. But SQL has to be rewritten to specify the right user, especially with broken ownership chains, so probably this is more trouble than it is worth.

9. SQL Server does not have synonyms.

10. Standard Practice on SQL Server is to have all objects, even views and stored procedures, owned by the dbo user. Other schemas would tend to have their own database/tablespace. However, this is just standard practice, and does not have to be followed. But problems could arise be with lack of synonyms and broken ownership chains.

11. SQL Syntax is slightly different. SQL Server books online has full syntax with a search capability. However DDL and DBA operations should be done by the Enterprise Manager GUI, which has wizards available. SQL Server Enterprise Manager is vastly superior to the equivalent Oracle GUIs. A few operations cannot be done through Enterprise Manager: Column permissions have to be modified through SQL; Filegroups have to be created through SQL.

12. In DDL, whitespace and special characters must be covered with []. E.g.: REVOKE ALL ON [order details] FROM PUBLIC; DENY CREATE DATABASE TO Eva, [Corporate\ErikB], Ivan.

13. SQL Server has its equivalent of v$ and dba_ views. It also has a collection of system stored procedures which return v$ information or actually perform system DDL.

14. SQL Server has application roles in addition to standard roles. Application roles are password identified while standard roles are always enabled for a user. When an application role is enabled, no other privileges are apparent, with the exception of public privileges. Application roles would typically be enabled and disabled through a VB front end script. Syntax is: exec sp_setapprole 'approle_name', 'password'

15. Databases have a primary datafile (*.mdf), possibly some secondary datafiles (*.ndf); and one or more on line redo log files (*.ldf).

16. There is no reason to give a database more than one datafile, except for backup/recovery streamlining for very large database/tablespaces.

17. SQL Server has a fixed block size of 8k, and a fixed extent size of 64k. This may not mean an end to defragmentation requests, since there is still a reorganise utility. The reorganise utility can be scheduled to run after backup, and does hot reorgs. Small tables can, apparently, share an extent.

18. Rows cannot span blocks, so the maximum row size is 8k. This means chaining does not happen in SQL Server, but problems will occur if a row physically cannot fit into 8k.

19. The default size of on line redo log files is 25% of the total size of all datafiles. It is also recommended that autoextend be switched on on online redo log files.

20. A maintenance wizard will decide whether or not to grow or shrink on line redo log files, among other things.

21. Autoextend can be in extensions of a fixed size or a percentage of current size.

22. Autoshrink is available, but recommend that it is switched off and shrinking is done by maintenance jobs after backups.

23. Databases can be dbo use only (in development phase) (=restricted session); and can be in single user mode (when doing restores, etc.).

24. Truncate log on checkpoint is equivalent to noarchivelog mode in Oracle. Truncating the log on backup is equivalent to archivelog mode in Oracle.

25. On line redo logs are not archived, except on backup. They will therefore grow to much larger sizes than oracle On line redo logs.

26. By default, a database is created with one filegroup, named default. Microsoft recommend that filegroups should only be used for backup purposes. Performance should be handled by striping, even with tables and indexes.

27. Unlike our practice with Oracle on NT, but like our practice with Oracle on Sun: Microsoft recommend on line redo log files should be on separate physical disks, with separate disk controllers, from the datafiles. This is recommended both for performance and for fault tolerance. [p160]

28. During an online backup: cannot create or alter databases; create indexes; perform nonlogged operations such as bulk copy and writetext. These will be failed if attempted after backup is started, or cause backup to stop these are already running.

29. Three types of backup: full backup (=online backup); online redo log backup (=archive); and differential backup. The last type is completely new to SQL Server. It backs up just those blocks which have been modified since last full backup.

30. Create Index statements force the data and index filegroups to be backed up simultaneously.

31. There are no rollback segments in SQL Server. Rollback information is obtained from the online redo logs. This should improve performance.

32. SQL Server is read inconsistent. This will improve performance for some SQL jobs, but produce inconsistent results.

33. Standby databases can be up and available read-only on SQL Server. On Oracle7 they are unavailable.

34. SQL Server creates snapshot disk files, rather than snapshot log tables.

35. SQL Server has transactional replication, which replicates data by monitoring redo logs. block changes for replication are specifically marked in the redo logs. This is like snapshot replication, but with constant data copying. So it provides the same functionality as distributed database triggers, but with fewer potential problems.

36. A distribution database stores distribution history, and in transactional replication, also keeps the information culled from the redo logs for propagation.

37. SQL Server does not have parallel Server, although this may be possible via NT operating system.

38. SQL Server does not have sequences. Instead columns can be given the identity property.

39. By default, autocommits instantly, rollback unavailable.

40. With begin transaction...commit/rollback transaction statements, get commit, rollback, endpoint functionality. However, because SQL Server does not have rollback segments, modified rows in a transaction are locked. Users cannot even select from the entire table in most cases.

Best Practices Every DBA Must Know

Best Practices Every DBA Must Know
By Brad M. McGehee

Installing & Upgrading SQL Server
* Generally, when installing a new SQL Server instance:
• Use the newest hardware firmware and OS drivers.
• Use the newest OS version with latest SP and patches.
• Use the newest SQL Server version with latest SP & Hot Fixes.
• Test, and once stable, put into production & be wary of making changes.
* Generally, when upgrading an existing SQL Server instance:
• Don’t upgrade unless you have a good reason to upgrade. If your instance is working well, don’t mess with it.
• For example, upgrade if you need new features, or have problems with an old installation, or need to upgrade hardware.
• It is always safer to upgrade to a new server with a fresh installation of the OS and SQL Server than to upgrade in place. This allows you to test more effectively, and also gives you an easy “back out” option.


Security Basics
• Don’t give users more permissions than they need to perform their job. (Critical. Sounds simple, often hard.)
• Don’t use the SA account for anything. Assign it a complex password, and keep it handy just in case. Instead, use a domain account that is a member of the sysadmin role.
• Don’t allow an application to use the SA or a sysadmin account to access SQL Server.
• Use Windows Authentication security whenever possible. (Applicable for in-house development).
• Don’t give vendors sysadmin access to your servers.
• Log off or lock your SQL Server (or workstation) when done.


General Server Configuration
• Ideally, SQL Server instances should run on a stand-alone server (physical or virtual) with no other major apps running on it. Small monitoring apps or utilities are generally OK.
• Avoid multiple instances unless you have a really good reason to use them. Consider virtualization instead.
• Unnecessary SQL Server services should be uninstalled or turned off.
• Ideally, don’t run antivirus/antispyware software locally. If your organization’s policy requires running antivirus/antispyware software locally, exclude MDF, NDF, LDF, BAK, TRN and BCP files.


SQL Server Property Settings
• SQL Server includes many instance-wide property settings. (e.g. sp_configure)
• Don’t change any of these defaults unless you know exactly what the effect of the change is.
• There are a few of these settings that are commonly changed from their defaults, and generally you need to perform
baselines before and after the change to see if performance and HA increased or decreased because of the change.
• If you make a change, only make one change at a time so that you can see its effect independent of other variables.

Memory Configuration
• Ideally, use the 64-bit version of the OS and SQL Server.
• If using the 32-bit version of SQL Server, and if using 4 GB or more of RAM, ensure that /3GB switch and AWE memory are correctly configured. Correct settings depend on available RAM.


Data and Log File Management
• Remove physical file fragmentation before creating new or expanding existing MDF or LDF files.
• When creating new MDFs and LDFs, pre-size them to eliminate/minimize autogrowth events.
• MDF files should be located on their own disks.
• LDF files should be located on their own disks.
• BAK and TRN backup files should be located on their own disks.


Instant File Initialization
• Enable instant file initialization, which prevents MDF files from being zeroed out when they are grown, allows MDF files to be created quickly. LDF files are not affected.
• Speeds up CREATE DATABASE, ALTER DATABASE, RESTORE DATABASE, Autogrowth.
• Requires SQL Server 2005/2008, and Windows Server 2003/2008 (or higher version).
• Instant file initialization is turned on if the SQL Server (MSSQLSERVER) service account has been granted the SE_MANAGE_VOLUME_NAME permission by adding the service account to the Perform Volume Maintenance Tasks security policy. Members of the local Windows Administrator group automatically have this right.

Don’t Shrink Files
• If you properly size your MDFs and LDFs, then you should very rarely have to shrink a file.
• Don’t schedule periodic database or file shrinking operations.
• If you must shrink a database (MDF file):
– Do so manually
– Rebuild the indexes after the shrink is complete
– Schedule these steps during the slow time of the day
• Benefits of not automatically shrinking files:
– Eliminates grow and shrink syndrome
– Reduces physical file fragmentation
– Reduces resources used for these operations, allowing more important tasks to use them


Tempdb Management
• Pre-size tempdb so autogrowth doesn’t have to happen often (8MB is default, which is very low).
• Set autogrowth to avoid many growth spurts, use a fixed amount that minimizes autogrowth use. (10% is default,
which causes lots of autogrowth).
• If tempdb is very active, locate it on its own disks.
• If very active, consider dividing the tempdb into multiple physical files so that the number of files is 1/4 to 1/2 the number of CPU cores, up to 8 files. Each physical file must be the same size.


Database Property Settings
* Don’t change database property settings unless you have a very good reason. Some key ones:
• Auto Create Statistics: On
• Auto Update Statistics: On
• Auto Shrink: Off
• Autogrowth: Leave on. Use mainly for catching mistakes. File growth should be managed manually. Use fixed amount that minimizes autogrowth occurrences.
• Recovery Mode: Set to full for all production databases so transaction log backups can be made.
• Page Verify: Use Checksum (2005/2008), don’t turn off.
• Compatibility Level: Should be set to match current server version, unless there are compatibility problems.

Configuring Jobs—General
• If your production servers don’t have any jobs, then there is a problem, as all servers need jobs.
• Try to schedule jobs so they don’t interfere with production.
• Try to prevent jobs from overlapping.
• Set alerts on jobs so you are notified if they fail.
• Check jobs daily to verify that they have run correctly (not hung, not run abnormally long, etc).
• If you use the Maintenance Plan Wizard, be careful to use it properly. If misused, it can create maintenance jobs that hurt performance.


Create Index Rebuilding/Reorganize Job
• Indexes need to be rebuilt or reorganized regularly to minimize fragmentation and reduce wasted space.
• Only Reorganize or Rebuild indexes that need it, don’t defrag all indexes all the time (unless perhaps a database is small).
• Some general recommendations (from BOL). Use as a starting point to determine what is best for your server.
– If fragmentation is less <5 alone.="alone." leave="leave" p="p" then="then">– If fragmentation is >5% and <30 consider="consider" p="p" reorganize.="reorganize.">– If fragmentation >30%, consider Rebuild.
• Use sys.dm_db_index_physical_stats to help you determine if an index should be rebuilt or not.
• Reorganize or Rebuild jobs should ideally be scheduled as a SQL Server Agent job using a custom T-SQL script you create to meet your environment’s specific needs.
• Perform as needed to minimize negative effect of index fragmentation, but not more than required.


Create Data Corruption Detection Job
• Ideally, run DBCC CHECKDB as frequently as practical.
• If you have a problem, you want to find it as soon as possible to reduce the risk of data loss.
• Create an appropriate job to run this (or similar) command:
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS;
Note: Consider using PHYSICAL_ONLY option for large or busy production servers to reduce run time.
• Don’t use the DBCC CHECKDB repair option unless you fully understand its implications, as data loss will most likely occur.


Implement a Backup/Restore Strategy
• Create a job to perform full backups daily on all system and user production databases, plus log backups hourly (or similar variation). Include differential backups if appropriate.
• If a database uses the bulk or full recovery model, you must back up the transaction log to keep it from growing uncontrollably.
• Backup using RESTORE WITH VERIFYONLY to help verify backup integrity. (Does not guarantee good backups.)
• Periodically test backups to see if they can be restored.
• Set up an appropriate backup retention policy.
• Store backups securely and off-site (not on same disk array or SAN).
• If you have a limited backup window, or have limited disk space, use backup compression. Can be a big time saver.

Set Up Alerts for Critical Errors
• Create a SQL Server Event Alert for all events with a severity of 19 [fatal] and higher.
• Have alerts sent to you or whoever is responsible for day-to-day monitoring.
• Consider a third-party alerting tool if SQL Server Alerts doesn’t meet all of your needs.


Create a Disaster Recovery Plan
• You must create a document that outlines, step-by-step, in detail, how you will recover your SQL Servers in the case of any problem, small or large.
• You need to practice using the plan so you are familiar with it and can easily implement it.
• Keep Microsoft SQL Server’s Product Support phone number handy. Paste it near your computer.
• Remember: Most “disasters” are small, such as a corrupted database. Big “disasters” occur very rarely, if ever. But you need to be prepared for both.


Document Everything
* Yes, documentation is very boring, but it is very critical to being a successful DBA. Be sure to document:
• The installation and configuration of each instance.
• The installation and configuration of any application that uses SQL Server as its back end (as related to SQL Server).
• Troubleshooting tasks, as the same problem may reoccur, and you don’t want to reinvent the wheel.
• Any time any change is made to any instance for any reason.
* Be sure that documentation is easily available to everyone who needs access to it.

Test Everything
• Before you make any change on a production SQL Server, be sure you test it first in a test environment.

RDL: SQL Server reporting services XML file

RDL: SQL Server reporting services XML file

• RDL stands for Report Definition Language. 
• RDL is a file extension for an XML file used by Microsoft SQL Server reporting services. 
• RDL files contain calculations, charts, images, graphs and text and can be rendered into a variety of formats as needed. 
• RDL files can be created by Microsoft Visual Studio, third party tools, or with a text editor.