Friday 5 April 2013

Basics of SQL Server 2008 R2 for DBA


List of all database with recovery modes and version information.

'--- SQL 2005
SELECT NAME,COMPATIBILITY_LEVEL,RECOVERY_MODEL_DESC,STATE_DESC  FROM SYS.DATABASES
--- SQL 2000
SELECT NAME,CMPTLEVEL,DATABASEPROPERTYEX(NAME,'RECOVERY')AS RECOVERYMODEL,
DATABASEPROPERTYEX(NAME,'STATUS') AS STATUS FROM SYSDATABASES

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

List database Names with Physical location where the data files reside.
--- SQL 2005
SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,NAME,TYPE_DESC,PHYSICAL_NAME FROM SYS.MASTER_FILES
--- SQL 2000
SELECT DB_NAME(DBID) AS DATABASENAME,NAME,FILENAME FROM MASTER.DBO.SYSALTFILES

e='m 2 n b ��m H p ;margin-bottom:.0001pt;line-height: normal'>How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME
or
SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE with OVERRIDE
go
SP_CONFIGURE
Go


List of all database and its last backup taken on SQL Server .

SELECT db.name,case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),
 MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
 WHERE db.database_id NOT IN (2) GROUP BY db.name
ORDER BY 2 DESC

DBA Concepts



How to update statistics which are outdated?
--- updating usage statistics on SQL Server
EXEC sp_updatestats

------------------------------------------------------------------------------------------------------------------------------------------
How to identify the current SQL version on the server?

SELECT
          SERVERPROPERTY('MachineName') as Host,
          SERVERPROPERTY('InstanceName') as Instance,
          SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
          SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
          Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
      'STANDALONE' end as ServerType,
          @@VERSION as VersionNumber         

------------------------------------------------------------------------------------------------------------------------------------------
How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME
or
SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE with OVERRIDE
go
SP_CONFIGURE
Go

Identify Top 50 queries running with execution count



Identify Top 50 queries running with execution count

Use Master
go
SELECT TOP 50 creation_time, last_execution_time, total_clr_time,total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2  ELSE statement_end_offset END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CLR Time] DESC;
GO

How to configure AWE in SQL 2005/SQL 2008 on 32 bit Operating System?


How to configure AWE in SQL 2005/SQL 2008 on 32 bit Operating System?

Before you configure AWE ensure the database server has sufficient memory , ideal sceanio is Server RAM: 6GB , leave 2 GB for OS and other background running process, configure 4GB as AWE, check if awe is enabled or not first
Following steps needs to be performed
USE MASTER
GO
sp_configure 'show advanced options',1
go
reconfigure
Go
sp_configure 'min server memory (MB)','1024'
go
sp_configure 'max server memory', 4096
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Requirement For DBA


Knowledge, skills, and abilities needed to be successful in this role include:
* Knowledge of SQL Server 2005/2008
* Ability to perform database and SQL performance analysis and to make tuning changes, including but not limited to the use of SQL Profiler
* Expertise in the use and design of SQL Server Analysis Services 2008
* Expertise in the use of SQL Server Reporting Services 2008
* Experience estimating storage requirements for an application
* Excellent verbal and written communication skills
* Ability to work individually or within a team environment
* Ability to take direction and work to specified deadlines
* Displays professionalism and a commitment to quality
* Experience with design and maintenance of data warehouse

Replication In SQL Server 2008 R2


What is replication?

In its simplest form, data in a master site are replicated to other sites in the form of snapshots. A snapshot does not have to contain all the master data—it can, for example, exclude certain columns from a table for security reasons.





What are different types of replication?


1.      Transactional Replication
              Transactional replication replicates the data on one server (the publisher) to another server (the subscriber) with less latency than log shipping.
              Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
              The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
              Incremental changes made at the Publisher flow to Subscribers according to the schedule of the Distribution Agent, which can run continuously for minimal latency, or at scheduled intervals. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.


2.       Merge Replication
                Merge was designed for more of a client-server environment where the client      sometimes syncs up with the server but is disconnected quite often.Changes are tracked by triggers instead of by the log reader agent, and latency isn't as low as it is with peer to peer.


3.      Snapshot  Replication
          Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

   Define below terms used in replication
·        Publisher
·        Subscriber
·        Distributor

Publisher:
Publisher is the server or database that sends its data to another server or database.
Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

Subscriber
A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

Distributor
The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.




What is the difference between Push and Pull Subscription?
Push and Pull, as named in the title of this article,are the two methods available for moving data from the Distributor to the Subscriber(s).  Under the Push method, the Distributor is responsible for queuing data from the Publisher,then propagating it to the Subscriber(s).  Under the Pull method, the Distributor is responsible for queuing data from the Publisher, and it is the job of each Subscriber to connect to the Distributor and grab all queued data ready for replication.  Selecting the incorrect method can lead to serious performance issues, especially at peak times of database use.



Check if column exists or not in the SQL server

--method 1 IF EXISTS(SELECT 1 FROM sys.columns with (nolock)           WHERE Name = N'LoginName'           AND Object_ID = Objec...