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. 

SQL Server 2008 Failover Clustering Best Practices

SQL Server 2008 Failover Clustering Best Practices

■ Before installing SQL Server failover clustering, understand the prerequisites and verify that the clustering hardware is supported and certified by both the hardware vendor and Microsoft.

■ Leverage the Cluster Validation Tool included with Windows Server 2008 to ensure that all nodes within the cluster meet the prerequisites for deploying failover clustering.

■ Use identical hardware for all nodes in the cluster. This includes processor, memory, and firmware.

■ Configure Failover Clustering Feature from a Windows Server 2008 perspective prior to SQL Server 2008 Failover Clustering.

■ Ensure disk drive letters are identical on all nodes within the cluster.

■ Avoid having the quorum resource and other cluster resources from sharing the same disk.

■ Ensure that the public network adapter is the first adapter in the network binding list.

■ Disable NETBIOS on the private/heartbeat network adapters.

■ Disable write-back caching on host controllers.

■ Do not configure dynamic disks because clustering supports only basic disk configurations.

■ Determine whether a single-instance or multiple-instance configuration will be implemented. Plan the disk layout accordingly while taking future growth into account.

■ Identify which SQL Server features will be installed.

■ Do not use the same service account for Windows and SQL Server failover clustering.

■ Configure dependencies for shared disks so that they can be recognized and leveraged by SQL Server 2008.

■ Before using multiple instances, understand the impact of multipleinstance configurations and performance degradation on the surviving node if a failover occurs.

■ Change the service accounts only via SQL Server Configuration Manager.

■ Do not configure DTC resources within the same service or application as the SQL Server virtual instances.

■ Use the SQL Server Installation Center to modify a SQL Server failover cluster installation.

■ Ensure that each virtual server name is unique on the Active Directory domain.

■ Understand the benefit and impact associated with the different quorum models included in Windows Server 2008.

■ For advanced SQL Server 2008 failover clustering installations, use the planning and deployment tools included with the SQL Server Installation Center.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Failover Clustering Terminology

Failover Clustering Terminology

■ SQL Server virtual server—A SQL Server virtual server is, in fact, a cluster-configured resource group that contains all resources necessary for SQL Server to operate on the cluster. This includes the NetBIOS name of the virtual server, a TCP/IP address for the virtual server and all disk drives, and vital SQL Server services required to operate in a clustered configuration. In a multiple instance, two or more node clusters and one SQL Server virtual server are created per node, whereas the NetBIOS name and TCP/IP address of the cluster form the virtual server. When failover occurs in this configuration, the entire SQL Server virtual server fails over to the surviving node in the cluster dynamically.

■ Heartbeat—A single User Datagram Protocol (UDP) packet is sent every 500 milliseconds between nodes in the cluster across the internal private network. This packet relays health information about the cluster nodes as well as health information about the clustered application. If there is no response during a heartbeat to indicate that the node is alive, the cluster begins the failover process. In SQL Server 2008, this interval can be changed. This capability is useful when you are using a geographically dispersed cluster.

■ Failover—Failover is the process of one node in the cluster changing states from offline to online, resulting in the node taking over responsibility of the SQL Server virtual server. The Cluster Service fails over a group in the event that node becomes unavailable or one of the resources in the group fails.

■ Failback—Failback is the process of moving a SQL Server virtual server that failed over in the cluster back to the original online node.

■ Quorum resource—The quorum resource, also referred to as the witness disk in Windows Server 2008, is the shared disk that holds the cluster server’s configuration information. All servers must be able to contact the quorum resource to become part of a SQL Server 2008 cluster. In Windows Server 2003, the entire cluster’s health and vitality depends on the quorum drive being available. With Windows Server 2008, a majority quorum model can be leveraged to eliminate this single point of failure.

■ Resource group—A resource group is a collection of cluster resources such as the SQL Server NetBIOS name, TCP/IP address, and the services belonging to the SQL Server cluster. A resource group also defines the items that fail over to the surviving nodes during failover. These items also include cluster resource items such as a cluster disk. It is also worth noting that a resource group is owned by only one node in the cluster at a time.

■ Cluster resource—Cluster resources contain vital information for the SQL Server virtual server and include its network TCP/IP addresses, NetBIOS name, disks, and SQL Server services, such as the System Attendant. These cluster resources are added to services or applications when the virtual server is created to form SQL Server virtual servers. With Windows Server 2008, a clustered resource is known as an application and a service.

■ Dependency—A dependency is specified when creating cluster resources. Similar to a dependency on SQL Server services, a cluster resource identified as a dependency indicates that a mandatory relationship exists between resources. Before a cluster resource is brought online, the resource defined as a dependent must be brought online first. For instance, the virtual server NetBIOS name is dependent on the TCP/IP address; therefore, the TCP/IP address of the virtual server must be brought online before the NetBIOS name is brought online.

■ Majority Node cluster—In this configuration, each node is responsible for contributing one local disk to the quorum disk set that is used as storage disks. This configuration limits the majority node resource to one owner at a time. Because the quorum does not require a shared disk, the solution is typically used for geographically dispersed clusters.

■ Failover Cluster Management—Formerly known as Cluster Administrator, Failover Cluster Manager in Windows Server 2008 is a tool used by cluster and database administrators for accessing, creating, and administering Windows clusters. The Failover Cluster Administrator console is included in Windows Server 2008 and can be launched from any active node within the cluster. Additional administration and management tasks include viewing, creating, and deleting services or applications, cluster resources, and nodes.

■ Cluster witness disk or file share—The cluster witness or the witness file share are used to store the cluster configuration information, and to help determine the state of the cluster when some if not all of the cluster nodes cannot be contacted.

■ LUNs—LUN stands for Logical Unit Number. An LUN is used to identify a disk or a disk volume that is presented to a host server or multiple hosts by the shared storage device. Of course, there are shared storage controllers, firmware, drivers, and physical connections between the server and the shared storage, but the concept is that an LUN or set of LUNs is presented to the server for use as a local disk. LUNs provided by shared storage must meet many requirements before they can be used with failover clusters, but when they do, all active nodes in the cluster must have exclusive access to these LUNs.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Fixed Database-level Roles

SQL Server 2008 Fixed Database-level Roles

Several fixed database-level roles exist in each SQL Server database. Thosepredefined roles are used to grant a predefined set of permissions to database users and, with the exception of the public role, they are not used to assign permissions to individual objects.

db_accessadmin Granted: ALTER ANY USER, CREATE SCHEMA
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
public Granted: SELECT on system views

All database users belong to the public database role by default. It is a best practice to avoid using the public database role when assigning permissions.

SQL Server 2008 Server-Level Roles and the Permissions Associated with Each Role

SQL Server 2008 Server-Level Roles and the Permissions Associated with Each Role

Server Role bulkadmin, Default Permissions Granted: ADMINISTER BULK OPERATIONS


Server Role dbcreator, Default Permissions Granted: CREATE DATABASE


Server Role diskadmin, Default Permissions Granted: ALTER RESOURCES


Server Role processadmin, Default Permissions Granted: ALTER ANY CONNECTION, ALTER SERVER STATE


Server Role securityadmin, Default Permissions Granted: ALTER ANY LOGIN


Server Role serveradmin, Default Permissions Granted: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE


Server Role setupadmin, Default Permissions Granted: ALTER ANY LINKED SERVER


Server Role sysadmin, Default Permissions Granted with GRANT option: CONTROL SERVER


Server Role public, Default Permissions Granted: VIEW ANY DATABASE


Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 best practices for hardening a SQL Server environment:

SQL Server 2008 best practices for hardening a SQL Server environment:

■ When the SQL Server installation is complete, harden the SQL Server environment.

■ Install the most recent critical fixes and service packs for both Windows and SQL Server.

■ When you’re selecting authentication modes, Windows Authentication is a more secure choice; however, if mixed mode authentication is required, leverage complex passwords and SQL Server 2008 password and lockout policies to further bolster security.

■ Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.

■ Create a role-based security policy with the Security Configuration Wizard tool.

■ After SQL Server 2008 is installed, run the SQL Server Configuration Manager tool to disable unnecessary features and services and create policies with Policy Based Management.

■ Install only required components when installing SQL Server.

■ After the server has been hardened, periodically asses the server’s security using the MBSA and SQL Server BPA.

■ For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.

■ Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.

■ Enable a firewall to filter unnecessary and unknown traffic.

■ At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.

■ If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.

Note from book "Microsoft SQL Server 2008 Management and Administration".

Best practices for backing up and restoring SQL Server 2008

Best practices for backing up and restoring SQL Server 2008:

■ Define and document an SLA relevant to the SQL Server 2008 environment.

■ Test the backup and recovery plan on a periodic basis and also before production to validate that it is operational and the SLA can be met.

■ Select the appropriate recovery model for all systems and user databases. Use the Full recovery model for mission-critical databases that need to be restored to the point of failure in the event of a disaster.

■ Isolate database and transaction log files on separate spindles for recovery purposes.

■ Save the backups locally on a redundant disk drive separate from the online databases and back up to tape on a regular basis.

■ If database or transaction log backups are stored locally on disk, do not store them on the same volumes as the database and transaction log files. If a drive or volume failure occurs, both the files and backups could be lost.

■ For retention and recovery purposes, the backups stored to disk should be committed to tape.

■ Commit to doing frequent backups if the system is an online transaction processing (OLTP) environment. An OLTP database is also known as the databases residing in the SQL Server Database Engine.

■ Try to schedule backups when SQL Server is not in the process of being heavily updated.

■ Use maintenance plans to streamline, automate, and schedule backups for all system and user databases.

■ For large databases, consider introducing additional files or filegroups and include a combination of full, differential, filegroup, and transaction log backups to reduce backup and restore times.

■ Speed up the backup process by selecting multiple backup devices.

■ Leverage the new Backup Compression feature to minimize the backup footprint on disk. However, first test the impact in a dedicated lab before rolling out into production.

■ When restoring the database to the point in failure, remember to first back up the tail-log and then conduct the restore.


Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Maintenance Practices Best Practices

Some important best practices from the chapter include the following:


■ DBAs should fully understand all maintenance activities required and implemented within the SQL Server environment.

■ Use the Maintenance Plan Wizard to automate and schedule routine maintenance operations.

■ When creating maintenance plans with the wizard, leverage the features included in SQL Server 2008 and create independent schedules for subtasks.

■ Maintenance tasks should be scripted, automated, and fully documented.

■ Maintenance tasks should be conducted during nonpeak times or after hours, such as on weekends and after midnight.

■ When you configure the order of the maintenance tasks, backups should be executed first, and then other tasks that change the database.

■ Do not include the Shrink Task when creating Maintenance Plans. Manually shrink the database if needed during nonpeak hours.

■ Maintenance tasks should be grouped into daily, weekly, and monthly schedules.

■ Schedule and conduct routine maintenance tasks on a daily, weekly, and monthly basis.

■ For a large enterprise environment running many SQL Servers, take advantage of subplans and the multiserver maintenance plan.

Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 DBA Routine Maintenance Tasks: Daily, Weekly, Monthly/Quarterly

SQL Server 2008 DBA Daily Routine Maintenance Tasks
Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:
■ Check that all required SQL Server services are running.
■ Check Daily Backup logs for success, warnings, or failures.
■ Check the Windows Event logs for errors.
■ Check the SQL Server logs for security concerns such as invalid logins.
■ Conduct full or differential backups.
■ Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
■ Verify that SQL Server jobs did not fail.
■ Check that adequate disk space exists for all database files and transaction logs.
■ At least monitor processor, memory, or disk counters for bottlenecks.

SQL Server 2008 DBA Weekly Routine Maintenance Tasks
Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine. The following list details these weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables and views by reorganizing indexes.
■ Reorganize data on the data and index pages by rebuilding indexes.
■ Update statistics on all user and system tables.
■ Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
■ Manually grow database or transaction log files if needed. Adjust automatic growth values if needed.
■ Remove files left over from executing maintenance plans.

SQL Server 2008 DBA Monthly or Quarterly Maintenance Tasks
Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don’t require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.
■ Conduct a restore of the backups in a test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics and compare them to baselines.
■ Review and update maintenance documentation.
■ Review and install SQL Server patches and service packs (if available).
■ Test failover if running a cluster, database mirroring, or log shipping.
■ Validate that the backup and restore process adheres to the Service Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account passwords.

From book "Microsoft SQL Server 2008 Management and Administration".

Best Practices on Managing SQL Server 2008 Indexes

Best Practices on Managing SQL Server 2008 Indexes

■ Managing and optimizing indexes is an ongoing process because performance can suffer both with the lack of indexes and poorly implemented and managed indexes.
■ Create clustered indexes on columns that are frequently used and are lean data types. For example, the clustered index could be an identity column. However, it is not uncommon to create the clustered index on a column used in WHERE, ORDER BY, and GROUP BY clauses and in joins.
■ Nonclustered indexes are dependent on the clustered index. Be careful when disabling a clustered index because the nonclustered indexes are also automatically disabled.
■ A table can have only one clustered index. Take care to ensure that the key length of the clustered index doesn’t exceed 900 bytes.
■ Use nonclustered indexes to cover frequent or costly queries that are not covered by the clustered index. As many as 999 nonclustered indexes can be created on a table.
■ Take care to ensure the key length of the nonclustered index doesn’t exceed 900 bytes. Add columns as nonkey include columns to place additional data into the index.
■ In order to alleviate performance degradation caused by fragmentation, maintain indexes by either updating or rebuilding indexes.
■ If the tempdb is stored in a different filegroup or on a different set of physical disks, use the option to sort the index in the tempdb for a performance improvement.
■ Leverage the Enterprise Edition’s online indexing feature to eliminate downtime when removing fragmentation from indexes.
■ When deleting all indexes on a table, remember to remove the clustered index last. If the clustered index is removed first, any nonclustered indexes are unnecessarily maintained as part of the removal process.
■ Spatial indexes should be created to increase the response time of spatial queries.
■ Leverage Filtered indexes to improve performance and reduce maintenance costs on columns that have nulls.

From book "Microsoft SQL Server 2008 Management and Administration".

How To Test If A TCP Port Is Open on UNIX

Sometimes we need to check if a certain TCP port is open. The following are some ways to do it.

$ netstat -an | grep 5100
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN

SUCCESS:
$ echo | telnet server_name 5100
Trying 100.10.10.10...
Connected to server_name.
Escape character is '^]'.
Connection to server_name closed by foreign host.

FAIL:
$ telnet  server_name 5100
Trying 100.10.10.10...
telnet: Unable to connect to remote host: Connection refused

SQL Server 2008 Database Engine Best Practices

According to book "Microsoft SQL Server 2008 Management and Administration":

■ Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into Transact-SQL syntax.
■ Unless there is a specific need to do otherwise, it is a best practice to allow SQL Server to dynamically manage the minimum and maximum amount of memory allocated to SQL Server. However, if multiple applications are running on SQL Server, it is recommended to specify minimum and maximum values for SQL Server memory. Then the application cannot starve SQL Server by depriving it of memory.
■ The preferred authentication mode is Windows Authentication over SQL Server Authentication because it provides a more robust authorization mechanism.
■ Use Change Tracking to ensure accountability and compliance on databases and database objects.
■ Leverage the compression technologies to reduce storage utilization.
■ Configuring SQL auditing is recommended to capture both failed and successful logins.
■ Do not set the database to automatically shrink on a regular basis because this leads to performance degradation and excessive fragmentation over time.
■ The first Database Engine administration task after a successful SQL installation should involve tuning and configuring the server properties.
■ Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases.
■ Database files, transaction log files, and operating system files should be located on separate volumes for performance and availability.
■ When multiple database files and transaction log files exist, organize them through the use of filegroups.
■ Create basic reports in Management Studio to better understand the SQL Server environment.
■ Automate administration tasks by using SQL Server 2008 Agent jobs.
■ Review the other break-out chapters in the book for more information on items such as hardening a SQL Server infrastructure, encryption, Policy Based Management, Resource Governor, backups, and maintenance plans.

RMAN connect target database: RMAN-00571, RMAN-00569, ORA-01031

Got the following error when connect to the target database:


RMAN> connect target sys/****@mydb;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

Solution:

RMAN Denies Logon to Target Database: Scenario

RMAN fails with ORA-01031 (insufficient privileges) or ORA-01017 (invalid username/password) errors when trying to connect to the target database:

% rman
Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2003, Oracle.  All rights reserved.

RMAN> CONNECT TARGET sys/mypass@inst1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN Denies Logon to Target Database: Diagnosis

RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target as SYSDBA, you must do one of the following:

• Be part of the operating system DBA group with respect to the target database (that is, have the ability to connect with SYSDBA privileges to the target database without a password).
• Create a password file with the orapwd command and the initialization parameter REMOTE_LOGIN_PASSWORDFILE.
• Make sure you are connecting with the correct username and password.

If the target database does not have a password file, then the user you are logged in as must be validated with operating system authentication.

RMAN Denies Logon to Target Database: Solution

Either create a password file for the target database or add yourself to the administrator list in the operating system.

Reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtroub.htm

Ways to Check oracle sid and database name

Ways to Check oracle sid and database name

1. Get the name of the instance
select sys_context('userenv','instance_name') from dual;

2. global_name is granted to PUBLIC, so anybody can query it.
select * from global_name;
select global_name from global_name;

3. use ORA_DATABASE_NAME
select ora_database_name from dual;

4. user v$ views
select name from v$database;
select instance_name from v$instance;

ORA-00821 ORA-01034 ORA-27101, SGA_target and SPARC T series database servers

Problem: 


When migrating a database from an older Solaris database server to a new Solaris T3/T4 series database server, SGA_Target has to be increased from 350MB t0 900MB to have the database started up. Otherwise ORA-04031 error would occur. 

After spending sometime researching, the following Oracle Support article explains the issue and gives a workaround.

Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Goal

Migrated a database server from a server with 8 CPU's to a server with 128 (virtual) CPU's - that is, 2 CPU's, each with 8 cores which each handled 8 threads = 2x8x8 = 128.

So Solaris (on SPARC T series) is presenting Oracle with 128 CPU's. This makes Oracle adjust it's memory demands upwards, meaning that before the databases can ran fine with 320 MB of ram (sga_target) now needs to have up around 800-900 MB of RAM?

Is there anyway to minimize the memory usage?

Fix

Calculating the sga_target should consider the following:

The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)

So the SGA size will mainly depend on CPU_COUNT.

Bug 8316255 was filed for this issue and initially closed as 'Not a Bug' with the comments:

"CPU_COUNT should not be changed . This is not a bug.
Reducing CPU_COUNT has side effects due to the fact that the per cpu power of CMT may not be sufficient to sustain high load ( e.g. cache fusion messages ).
Also, some of the dependent parameters such as gc latches would be underconfigured ". 

Currently, a fix has been produced which added the notion of the raw cpu threads and effective cpu threads.
This fix is only available in the 11.2.0.3 Bundle Patch 1 for Exadata Database and will be included in 12.1.
Please refer to:
Note 8316255.8 - Bug 8316255 - Wrong discovery of number of CPUs in SPARC T series.
Note 13343057.8 - Bug 13343057 - 11.2.0.3 Bundle Patch 1 for Exadata Database

For other systems, a way of reducing memory needs would be to set a manual value for DB_CACHE_SIZE (which depends on CPU_COUNT) and keep the old value of SGA_TARGET or not using ASMM (not using SGA_TARGET).




Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Who controls load balancing with multiple CPUs, Oracle or OS?

Who controls load balancing with multiple CPUs, Oracle or OS?

Answer:

Load Balancing is not done by Oracle. The Operating System manages CPU control and usage.

The only references to CPU parameters in the init.ora file for Oracle are:    
--CPU_COUNT: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams039.htm#REFRN10023
--PARALLEL_THREADS_PER_CPU: http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams183.htm

Neither of the above parameters are involved with Load Balancing of the CPU.

Reference: How to Load Balance Oracle with Multiple CPUs [ID 158090.1]

Oracle Drop Database Command

I did not know this command before until someone mentioned to me the other day! I am used to manually dropping databases on UNIX.

Anyhow, here is some info about this command.

DROP DATABASE

The DROP DATABASE command deletes these files from operating system.
* Datafiles
* Online Redo Log Files
* Controlfiles
* SPFILE (if it exists)

The DROP DATABASE command does not delete the following files:
* init.ora (text version of the Oracle initialization file)
* password file
* entries in listener files
* entries in oratab file


If the operating system is Windows, then it does not delete the Windows service for the target database instance.

Reference: Drop Database Command Versus Remove an Oracle Database Manually [ID 443822.1]