Home » SQL Server Administration » Page 7

SQL Server Administration

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 … 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 … 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

sql server DBA 999

Recycling SQL Error Log and increasing max number of Error logs Stored

Recycling Error log is another important thing in case if you are storing login auditing with login success to Error log. With Recycling error log to avoid losing important data one should increase the number of Error logs stored.

For Example: If Error log is growing at a rapid level then it can be recycled every two days and its setting can be changed to keep 50 error log files or higher which would account around 100 days.
1. Set the number of log files to 50 from the default of 6 to do this:
• SQL 2005 – Right click on SQL Server logs folder in Managment studio and choose Configure from the pop up menu.
• Click the … Read the rest

sql server DBA 999

Setting up and Monitoring of Database Mirroring as DR Strategy

Quick Step by Step Procedure involved:

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option
• Perform another restore of the Transaction Log, also with the No Recovery option.
• Go to the Database Properties on individual databases and choose the Mirroring tab.
• Click “Configure Security”
• Click Next on the wizard
• Choose not to have a Witness server since Asynchronous Database Mirroring is used.
• Click Next
• … Read the rest

sql server DBA 999

Dynamic Management Views in SQL Server

Dynamic Management Views in SQL Server exists in SYS schema and their names start with DM_.
To query a DMV, we should prefix the view name with SYS.
Ex: To know total physical memory of the SQL Server we can use:

SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

The Mostly used DMV’s to check the current behaviour of SQL Server are:
1. SQL Server related/Hardware Resources DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV
1. SQL Server Related DMV
This section details the DMV’s associated with SQL Server system.
Below are some of the DMV’s which are related to OS, Disk and Memory.
i.sys.dm_os_sys_info
This view returns the information about the SQL Server machine, available … Read the rest

sql server DBA 999

Creating a Central Management Datawarehouse in SQL Server 2008

What is the Management Data Warehouse?
The Management Data Warehouse(MDW) is a single database/Data Warehouse created inside of a SQL Server 2008 instance, which holds various performance related statistics. The performance statistics in the MDW are gathered via special data gathering routines, known as Data Collections. The MDW can contain Data Collection information from a single instance, or it can hold data collected from multiple instances(Steps shown at the end). The Data Collection process uses SSIS and SQL Agent jobs to gather, schedule and purge information in the MDW.

SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity. Each of these Data Collection definitions identifies the data that … Read the rest

sql server DBA 999

SQL 2008 Transactional Replication and initializing from a backup file

In some environments,running Fresh snapshot will take ages to synchronise the subscribers with publishers. Even if it is done in low activity hours, it will run into business hours because of time takes to take snapshot, copy, restoring snapshot, synchronising transactions again.
There is way in which the replication can be setup by initialising using a backup in SQL Server 2008. This new feature is very useful in environments where it takes a lot of time to run a fresh Snapshot/Re-initialise the replication.
I have followed a following set of steps in setting this up and testing:

Step No Steps Followed
1 Set up the Distributer (This applies to fresh Replication where a database and share to be setup)
2
… Read the rest