RSS

Category Archives: Configuration

Dedicated Administrator Connection [DAC] in SQL Server

Introduction

DAC [Dedicated Administrator Connection ] is feature added from SQL Server 2005 version . Database administrator use this fetaure to connect to a SQL Server instance When the database engine not responding to the regular connection so the database administrator use this fetaure to connect to the instance to solve the problem Instead of rebooting  the SQL Server.

By Defult this feature is disable in All SQL Server version From 2005 to the New version 2012.

Note :  your SQL Server browser must be Runing

enable the DAC using below T-SQL command

Use master

GO

sp_configure ‘show advanced options’ , 1

GO

sp_configure ‘remote admin connections’, 1

G O RECONFIGURE

GO

enable the DAC using SSMS

1- write click on your SQL Server Probirties >> Factes >>  Service Area Configuration >> RemoteDacEnabled

Change it from False To True

Now try to connect by write ADMN: before your instance name like this

ADMIN:Mostafa-PC\SQLSERVER2008

then your User name and your password or connect windows Authentication but if you try to connect by the default way SQL Server will give you this Error

Dedicated administrator connections are not supported. (ObjectExplorer)

 

So if you want to connect with this Feature you must make this steps

Go to File >>> New >>> Database Engine Query >>>> Admin:InstanceName

 then your Session will open

Note : You can not open 2 Session

 
2 Comments

Posted by on February 19, 2012 in Configuration, Configuration

 

sp_configure to set the value of the backup_compression_default setting

This Feature in SQL Server 2008 , 2012

USE master

Go

 EXEC sys.sp_configure N’backup compression default’, N’1’

 GO

 RECONFIGURE WITH OVERRIDE

 GO

 
Leave a comment

Posted by on February 8, 2012 in Configuration, Configuration

 

Configuring SQL Server Surface Area before using OPENROWSET

–Configuring SQL Server Surface Area before using OPENROWSET

USE

[master]

GO

 

sp_configure

‘show advanced options’,1

GO

 

reconfigure

withoverride

GO

 

sp_configure

‘Ad Hoc Distributed Queries’,1

GO

 

reconfigure

withoverride

GO

 

 
Leave a comment

Posted by on February 5, 2012 in Configuration, Configuration

 

Using DBCC UPDATEUSAGE to correct inaccuracies(Copy from another blog)

The DBCC command can be run on a database to correct inaccuracies of all objects in that database or you can also correct the inaccuracies of a single table by including that table name. If you would like to run the command for a particular database, then you can run the following command

DBCC UPDATEUSAGE (‘DATABASENAME’)

You can also run the below command to correct the inaccuracies for the current database

DBCC UPDATEUSAGE (0)

Remarks : I take this Information Copy from this Blog : http://learnsqlwithbru.com/2012/02/02/using-dbcc-updateusage-to-correct-inaccuracies/

 
Leave a comment

Posted by on February 3, 2012 in Configuration, Configuration

 

Ole Automation Procedures to delete file from PC

if you want to write Script to delete file from  your pc by SQL Server must be Configure your SQL server to allow this option :

exec sp_configure ‘Ole Automation Procedures’, 1

go

reconfigure

go

 

 
1 Comment

Posted by on January 20, 2012 in Configuration, Configuration

 

Change The Server Collation after installation

After you finish installation for SQL server may be you want to change the Server collation or database Collation so leat’s see what will we do if we want to make this :

1- if you want to know what’s your database collation know

SELECT name, collation_name

FROM sys.databases WHERE name = ‘test’   — put your database name here

2- If you want to get a list of all available collations on the server using

SELECT * FROM ::fn_helpcollations()

3- Alter Database Collation

ALTER DATABASE test           — put your database name here

COLLATE Arabic_CI_AS        — replace with whatever collation you need

4- IF you want to know what’s your server collation

SELECT SERVERPROPERTY (‘Collation’)

 
1 Comment

Posted by on January 18, 2012 in Configuration, Configuration

 

Enable xp_cmdshell using sp_configure

—- To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1

GO

—- To update the currently configured value for advanced options.

RECONFIGURE

GO

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

 
Leave a comment

Posted by on January 16, 2012 in Configuration, Configuration