July 2010 | SansSQL

Wednesday, July 21, 2010

SQL Server 2008 Service Pack 2 CTP is now available

SQL Server 2008 Service Pack 2 CTP is now available for download and testing.
SQL Server 2008 SP2 CTP contains the cumulative updates up to SQL Server 2008 SP1 cumulative update package 8, and fixes to issues that have been reported through the customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting. 

To Download SQL Server 2008 Service Pack 2 CTP Click Here.
To get the list of bugs that are fixed in SQL Server 2008 Service Pack 2 Click Here.

Thursday, July 15, 2010

Dedicated Administrator Connection (DAC) in SQL Server

The DAC Dedicated Admin Connection allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted. This is a feature available in SQL 2005 onwards.
To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name.
Note: Object Explorer cannot connect using the DAC.
If you are trying to connect to object explore using DAC you will receive an error message as shown below.

Connecting to a server using DAC (Management Studio):
1.       Open SQL Server management Studio.
2.       Make sure no other DAC are open. If open, close them.
3.       Click on “File” Menu, expand “New” and select “Database Engine Query”


 .      Now type your ServerName along with Admin: as shown below
     Ex: Admin:SansLaptop

1.       Use an account which has sysadmin privilege and click Connect.

For SQL Server 2005, we need to enable the DAC by using sp_configure or Surface area configuration for features.
Exec sp_configure 'remote admin connections',1
Go
Reconfigure
GO
OR

Using SQLCMD:
1.       Open run and type the below command
       sqlcmd -A -d Test -E -S  SansLaptop
-A represents DAC or Admin
-d represents Database Name
-E represents Integrated Security
-S represents Server Name.
Here also you need to use an account which has SysAdmin privilege.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Tuesday, July 13, 2010

Resource Database in SQL Server 2008

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sysschema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.
Physical Properties of Resource:
The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.
Backing Up and Restoring the Resource Database:
SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
Note: After restoring a backup of mssqlsystemresource.mdf, you must reapply any subsequent updates.
Accessing the Resource Database:
The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.
To determine the version number of the Resource database, use:
SELECT SERVERPROPERTY('ResourceVersion');
GO
To determine when the Resource database was last updated, use:
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
To access SQL definitions of system objects, use the OBJECT_DEFINITION function:
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
GO

Thursday, July 8, 2010

SQLCMD Mode - Run your queries against several servers in a single query window.

To run your queries on different servers using the same query editor, you have to 
  1.  First connect to a server
  2. Open a new query
  3. And now click on “Query” menu.
  4. Choose “SQL CMD Mode”

Once you click on “SQL CMD Mode”, the query editor will go into command line mode.
Now type :Connect ServerName. This command will establish a connection to the server you have specified and then it will execute your query on that server.

Results:
Now if you go to “Messages” Tab, you will see the below result text (self explanatory).