Home » Archives for February 2012

February 2012

sql server DBA 999

Different Restore options in SQL Server 2008

Different Restore options in SQL Server 2008:
1. Restore can be done for an entire database from a full database backup (a complete restore).
2. Restore can be done for a part of a database (a partial restore).
3. Restore can be done for specific files or filegroups to a database (a file restore).
4. Restore can be done to specific pages to a database (a page restore).
5. Transaction log Restore can be done onto a database (a transaction log restore).
6. We also have a option to Revert a database to the point in time captured by a database snapshot.

Restoring a full databaseThe following example restores a full database backup from … Read the rest

sql server DBA 999

Different Backup options in SQL Server 2008

Different Backup options in SQL Server 2008:
1. Backup’s can be taken for a complete database, or one or more files or filegroups.
2. If the Databases are in Full recovery model or bulk-logged recovery model(discussed below) then backup’s can be taken for the transaction log (BACKUP LOG).

MIRROR TO Statement: Specifies a set of up to three secondary backup devices, each of which will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.

Backup Set Options

COPY_ONLY: Specifies that the backup is a copy-only backup, which does not affect the … Read the rest

sql server DBA 999

SQL Server 2008 BACKUP LOG WITH TRUNCATE_ONLY

BACKUP LOG WITH TRUNCATE_ONLY is no longer a recognized BACKUP option. This command get rid of the contents of your SQL Server’s transaction log without really backing it up. This command will to invalidate the point in time recoverability of the database as this breaks the log chain.

This command is used by Database administrators when they end in situations where getting rid of the transaction log is only way by which he can shrink their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.

Microsoft always recommends that DBA’s must switch to simple recovery mode getting rid of the transaction logs instead of truncating logs by above command if no Point in Time recovery is required. … Read the rest

sql server DBA 999

SQL Server 2008 Authentication modes

Before we go into SQL Server 2008 Authentication modes, it is better to have quick look at Active Directory Services.
Active Directory (AD) is a directory service created by Microsoft for Windows domain networks. It is included in most Windows Server operating systems. Server computers on which Active Directory is running are called domain controllers. There can be more than one Domain Controllers in a Organization which are replicated internally. Active Directory serves as a central location for network administration and security. It is responsible for authenticating and authorizing all users and computers within a network of Windows domain type, assigning and enforcing security policies for all computers in a network and installing or updating software on network computers.… Read the rest

sql server DBA 999

Changing the Service Accounts Used by SQL Services

Firstly its good to know the types of startup accounts:

Local User Account: This user account is created in your server where SQL Server is installed, this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has limited access permissions to the resources in the server.

Local System Account: This is a builtin windows account that is available for configuring services in windows. This account is a high privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows. This account has … Read the rest

sql server DBA 999

SQL Server TCP/IP Static and Dynamic Ports

SQL Server TCP/IP Static and Dynamic Ports must be configured/known by a DBA.

If Windows Firewall is active in a environment then all the ports closed except the ones which are given exceptions.
In some environments only the inbound connections are restricted,the outbound connections are not.
Exceptions to Firewall can either be given per Binaries or by Port.

To avoid connectivity issues with SQL Server, DBA should configure the Ports to Static where possible.
When a new SQL Server instance is installed if its Default instance then its given static port 1433.
But if the SQL Server instance is a named instance then it will give a Dynamic port to the SQL Server which may change when SQL Services are … Read the rest

sql server DBA 999

Starting SQL Server Service with minimal configuration for issue with Tempdb location

Starting SQL Service with minimal configuration for issue with Tempdb location

If a SQL instance starting up as a service cannot find the storage device/ Disk location where the tempdb files are stored then the instance will fail to startup.
Since the service cannot be started, the tempdb location can not be reconfigured from SSMS in a normal way. The only way to Reconfigure TempDb File locations is through command-prompt window by Connecting to SQLCMD with minimal configuration.

Steps involved in this activity are:
1.Connecting to SQLCMD with minimal configuration.
2.Reconfigure the tempdb location
3.Stop the command-prompt instance
4.start the service from SQL Configuration manager.

To start a SQL instance in a command-prompt window, cd to the instance’s Binn directory … Read the rest

sql server DBA 999

SQL Server 2008 Recovery Models and Transaction log usage

SQL Server 2008 Recovery Models
In SQL Server 2008 there are 3 recovery models you can use: simple, bulk-logged, and full. These recovery models really just define how log management behaves so you can have what you need to perform your desired backups.
As the name implies the focus is really on recovery of databases in case of Disaster occurs.

Simple Mode

If a database is set for Simple Recovery mode than it means that you don’t bother to take log backups. Only Backups available will be Full Backups of Database which you will use in case of Disaster occurs. SQL Server forces the log to truncate when the database writes data to the disk (we call that a checkpoint). … Read the rest

sql server DBA 999

SQL Server Raid Configurations and Recommendations

A SQL Server DBA given choice to select his Disk configuration should know the different Raid configurations and should select them appropriately.

Common RAID Levels used are:
RAID 0
RAID 0 which is also called a strip set. It is Very fast but have no redundancy.
A RAID 0 array must have 2 or more disks. A RAID 0 array has no redundancy. As data is written to the array it is spread across the disks for maximum speed. This RAID level should never be used for a SQL Server. In the event of a failure of a single disk in the array all data is lost. To calculate the space created from a RAID 0 array multiple the number … Read the rest

sql server DBA 999

SQL Server 2008 Auditing Functionality

SQL Server 2008 in-built Auditing Functionality has a lot of advantages over traditional way of auditing:

1. SQL Server 2008 Auditing is Easy to Setup and Administer.
2. SQL Server 2008 Auditing can be enable at the Instance and Database Levels.
3. SQL Server 2008 Auditing includes many predefined activities that you can audit, including DML and DDL activity.
4. SQL Server 2008 Auditing is very lightweight compared to older ways of capturing this information which used Traces. It internally uses Extended Events engine to capture audit data. This results in fast performance plus minimum overhead compared to using SQL Trace to capture activity.
5. SQL Server 2008 Auditing has a high Granularity in auditing like SELECT, INSERT, UPDATE, DELETE, … Read the rest