Run a multi server Query in SQL server 2008 without using CMS

Hey guys, here is a way which you can use to run the same query on multiple servers without using CMS (Central Management Server).

In one of my article “Central Management Server”, I had explained on how to setup and use CMS.

In this article I will be showing how to run a multi server query without using CMS in SQL server 2008.

This is very simple.

Open the SQL Server 2008 SSMS (SQL Server Management Studio) and register the servers.

Right-Click on the registered Server Group and select “New Query”.

On the newly opened query window, run the below command

Select SERVERPROPERTY('ProductVersion') AS 'Version', 

SERVERPROPERTY('ProductLevel') AS 'Level', 

SERVERPROPERTY('Edition') AS 'Edition'

And here are the results

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

Changing the color of the Status bar in SQL Server 2008 Management Studio

Hey Guys are you bored of seeing the same color on the status bar each time you open the New Query window in your SQL Server 2008 Management Studio?
Here is the way how you can change the color of the status bar. This can be done for each server or for each window.
For Each Server:
Click on “Connect” on select “Database Engine”

Click on Options

And click on the check box “Use Custom Color:” and press “Select”

Now Click “OK” and then “Connect”.

Now right click on the server and choose “New Query” and notice the Status bar of the New Query Window, it will show the Color you choose in the previous step.

For Each Window:

Click on “File”, expand “New” and choose “Database Engine Query” and follow the same steps as above.

Covering Index or Index with Included Columns

Covering Index or Index with included Columns is a new functionality which is introduced in SQL Server 2005 onwards and is an extension to Non-Clustered Indexes.

From SQL Server 2005 onwards, the functionality of the Non-Clustered indexes can be extended by adding non-key columns to the leaf level of the Non-Clustered index. By including non-key columns, we can create Non-Clustered indexes that cover more queries.

This is because the non-key columns have the following benefits:

• They can be data types that are not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

So creating Covering Indexes can significantly improve the query performance because all the columns in the query are included in the index itself, either as key or nonkey columns. And only the index pages and not the data pages will be used in retrieving the data.

Covering indexes can bring in a lot of performance to the query, because it can save a huge amount of I/O operations.

How to Create a Covering Index:

Using T-SQL:

/*This query will create a non-clustered index by name IX_Address_PostalCode

on PostalCode Column and includes the non-key columns AddressLine1,

AddressLine2, City, StateProvince */

USE AdventureWorks;

GO

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON SalesLT.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvince);

GO

/* And the below Query will be covered by the index and gives more performance */

SELECT AddressLine1, AddressLine2, City, StateProvince, PostalCode

FROM SalesLT.Address

WHERE PostalCode BETWEEN '85000' and '90000';

GO

Using GUI:

This article is also available in pdf format for downloading.

Please Click here to get your copy.

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.

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.