SQL Server – Policy Based Management

SQL Server 2008 has a new feature called Policy Based Management, also know as Declarative Management Framework (DMF), to tackle the problem of standardizing your SQL server instance. Although Policy Based Management can be used just to alert an administrator when an object is out of compliance, depending upon the type of policy, you can also enforce compliance by preventing changes that would violate a policy. Policy Based Management introduces the following new objects that are used to design and check for compliance:

• Facts
• Conditions
• Policies
• Policy Targets
• Policy Categories

For more information click here

SQL Server – Distributing and Partitioning

Key Points of Distributing and Partitioning

• Partition allow you to divide a table or index into multiple filegroups.
• Table and index are partition horizontally, based on rows by specifying a partitioning column.
• To create a portioned table or index you need to perform the following actions:
• Create a partition function
• Create a partition schema mapped to partition function
• Create a table or index on the partition schema
• SPLIT function to add a new boundary point and hence partition.
• MERGE function to remove a boundary point and hence partition.
• SWITCH function to move a partition of data between tables.

SQL Server – Backup all the sql server databases

Sharing with you script to backup all the SQL server databases


Script:


DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName NVARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘D:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113)
SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,'model’,'msdb’,'tempdb’)

–according to requirement we can filter databases in where clause

SET ROWCOUNT 1

WHILE (exists(SELECT * FROM #temp WHERE flag=0))

BEGIN

SELECT @name=name FROM #temp WHERE flag=0
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH INIT
UPDATE #temp SET flag=1 WHERE flag=0

END

SET ROWCOUNT 0

DROP TABLE #temp

if anybody have any other idea please share.

SQL Server – Understanding the System Database of SQL Server

We all know there are some system databases available in SQL Server. But we should have understanding about system databases. Here just try to give you understanding of system databases very briefly.

There are four system databases available in SQL Server 2008

• Master
• Model
• msdb
• tempdb

Master Database


The master database records all the system-level information for a SQL Server system.The master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In short, Master database is the logical repository for the system objects residing in the sys schema.

Model Database

The model database is used as the template for all databases created on the instance of SQL Server. Template can include specific settings and all sorts of useful stuff.


Msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs. If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost.

Tempdb Database

The tempdb database is a workspace for holding temporary objects or intermediate result sets, temporary table creation/processing. The TempDB is recreated every time when ever SQL Server restarts


For more Detail go to below URL

http://msdn.microsoft.com/en-us/library/ms178028%28v=SQL.105%29.aspx
 

SQL Server – Linked Server – Mapped all users with one statement

Hello Friends,
I have setup the Linked server with the help of script here. My one of colleague helps me to write the script with we can map all user with one statement. This script will map all local SQL server users with remote user that you provide in the script.

You don’t need to map all user manually.

Script:

EXEC master.dbo.sp_addlinkedserver @server = N‘dsql2k’, @srvproduct=N”, @provider=N‘SQLOLEDB’, @datasrc=N‘servername’

EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N‘dsql2k’,@useself=N‘False’,
@locallogin=NULL,@rmtuser=N‘sa’,@rmtpassword=‘XXXXXX’


if anybody have any other experience, please share as comment.

Transaction Isolation Level

What is default Isolation level for SQL Server 2008 or Crystal Report?

Many people have same above question about Isolation Level, may we have same question. So here share the brief answers of this question.

Transaction Isolation Level:
In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.

Default Isolation level for SQL Server 2008 is “Read Committed”

SQL Server 2008 has following transaction isolation level

Read Committed
Read Uncommitted
Repeatable Read
Serializable
Snapshot

Learn More about TRANSACTION ISOLATION LEVEL (Transact-SQL)

Crystal Report Isolation Level

The default isolation level for database reads in Crystal Reports 9.0 is set to Read Committed

Crystal Reports .Net 1.1 Patch –> Problem ID # ADAPT00194820

http://search-pdf-files.com/pdf/193365-read-leel-error-description-isolation

SQL Search

Dear Friends,

I have tried the Utility SQL Search, it is really helpful for me…
Thought it will be handy to all of us and it is a free tool from Red Gate.
SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases.

Click here for more detail.

SQL Server Editions

Dear friends

Most of time we have a question about difference between the SQL server editions, Lot of detailed stuff available over the internet but here sharing with you the brief information about the each SQL server editions so that we can understand easily about all the editions.

SQL server 2008 is available in the following editions


Enterprise:
Designed for largest organizations and those needing to leverage the full power of SQL server 2008 platform. Learn More


Standard:
Designed for small and midsized organizations that do not need all the capability available in SQL Server 2008 Enterprise. Learn More


Workgroup:
Suitable for small departmental project with a limited set of features. Learn More


Express:
A freely re-distribution version of SQL server that is ideal for learning and building desktop and small server applications with small number of users. Learn More

Available as a free download on Microsoft Site: Download Now


Compact:
Designed for emended Databases. Learn More


Developer:
Designed for use by developer in creating SQL server applications. SQL Server 2008 Developer editions has all the features and capability as SQL server 2008 Enterprise, except that is not allowed to be used in production environment. Learn More

After that want to share with you all the Compare Edition Features

Welcome SQL Server 2011-Denali – CTP1

Dear friends,

A new release of SQL Server is available, and if you follow blogs or tweets or check Microsoft’s SQL Server website. You might have seen mention of the new version, code-named “Denali” SQL Server 2011 – CTP1 Released. CTP stands for Community Technology Preview


It’s available in both 32-bit and 64-bit version. May you are getting excited about the new version. So that‘s why I am sharing with you all…

• What‘s New SQL 2011
• Microsoft SQL Server 2011 Requirements
• SQL Server 2011 – Download
• SQL Server 2011 Installation Guide

If anybody have any experience about this, please share as comment

Query Optimization with Linked Server

Hello Friends,

I have faced a problem in SQL queries with Linked server; I just want to share with you all.

Let me explain with example as below

Say, we have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.

Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server


Now the problem is the some triggers and stored procedures which need to insert the values from SQL 2008 to SQL 2000 databases using Linked server. Now stored procedures and triggers are taking very long time to execute in this scenario.

But the same stored procedures and triggers are working fine when all the databases are on same SQL server i.e. SQL server 2000.

So the problem comes into the picture when we introduce the Linked Server.

So the solution of this problem, we optimized our stored procedures and triggers like remove the cursors, optimize the joins etc.

Note: As per above scenario query optimization is required with linked server. Whether both SQL server 2000 and SQL 2008 on same physical machine or on different machine.

If anybody has any other experiences about this, please share as comment.

SQL Server – Fix orphaned SQL users

Once you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the users.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the database user

So before accessing database we need to maps an existing database user to a SQL Server login. For that use sp_change_users_login to link a database user in the current database with a SQL Server login.

First we have to identify the “orphan” users. Below will lists the orphaned users:

EXEC  sp_change_users_login  ‘Report’
Go

If you have login ID myUser in security of Server and you db have user mydbUser then

EXEC  sp_change_users_login  ‘Update_One’,  ‘mydbUser’,  ‘myUser’;
GO
If you want to create a new login id and password for this user, fix it by doing:

Exec  sp_change_users_login  ‘Auto_fix’, ’mydbUser’, ’myUser’, ’mypassword’

For detail see the
http://msdn.microsoft.com/en-us/library/ms174378.aspx

Add the value into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables.
These columns automatically assign a value for each new row inserted.
But sometime Identity columns  missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table.


USE mytempDB
GO
CREATE TABLE myTable
(
myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
myValue NVARCHAR(30) NOT NULL
)
GO

INSERT myTable(myIdentity, myValue)
VALUES (5, ‘Varinder Sandhu’)

–Result =  Error because IDENTITY is OFF
–Below enable IDENTITY_INSERT

SET IDENTITY_INSERT myTable ON

INSERT myTable(myIdentity, myValue)
VALUES (5, ‘Varinder Sandhu’)

SET IDENTITY_INSERT myTable OFF

Note:
Once we enabled IDENTITY_INSERT on a table you must specify a value for the identity column.

–If IDENTITY_INSERT is OFF then use  below insert statement for above example

INSERT myTable(myValue)
VALUES (‘Varinder Sandhu’)

Find EDITION, VERSION and SERVICEPACK information of SQL Serve

Hello Friends

Generally, we choose the  @@VERSION to get the version information for SQL Server, but using this function we can’t get the Service Pack information installed…

Alternative way

Get Editions of SQL Server


SELECT SERVERPROPERTY(‘EDITION‘) AS EDITION

Get Version Detail of SQL Server


SELECT SERVERPROPERTY(‘PRODUCTVERSION‘) AS VERSION

Get the Service Pack installed on SQL Server


SELECT SERVERPROPERTY(‘PRODUCTLEVEL‘) AS SERVICEPACK

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY not working with Linked Server or Remote server ???

Hello Friends,


I have faced a problem using SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY with Linked server.

Let me explain with example as below

Question: 

We have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.
Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

Now the problem is the some triggers and stored procedures which need to insert the values generated from SQL 2008 to SQL 2000 databases using functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are not working in this scenario.

We are fixing this problem by writing another select statement for selecting identity based on unique parameters in that scope.

We are not aware why this is happening and if there is any solution available.


Answer:


The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

Same for SCOPE_IDENTITY, IDENT_CURRENT also......

If anybody has any idea, please share as comment…

SQL SERVER 2008 – Enable xp_cmdshell

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

 
Alternative way

1. Click the Start button.
2. Select All Programs.
3. Navigate to the Microsoft SQL Server 2008 folder.
4. Right Click on Server name then click facets options
5. In the Facets Option choose Surface Area Configuration in dropdown
6. At the bottom of the window, mark True for xp_cmdshell.
7. Click OK.

SQL - Index

UNIQUE 

 

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique. The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created. 

 

CLUSTERED 

 

Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures. 

 

A view with a unique clustered index is called an indexed view. Creating a unique clustered index on a view physically materializes the view. A unique clustered index must be created on a view before any other indexes can be defined on the same view. For more information, see Designing Indexed Views. 

Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created. 

If CLUSTERED is not specified, a nonclustered index is created. 

 

NONCLUSTERED 

 

Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order.

 

For More Deatil

The partner transaction manager has disabled its support for remote/network transactions

First verify the “Distribute Transaction Coordinator” Service is
running on both database server computer and client computers
1.      Go to “Administrative Tools > Services”
2.      Turn on the “Distribute Transaction Coordinator” Service if it is not running If it is running and client application is not on the same computer as
the database server, on the computer running database server
1.      Go to “Administrative Tools > Component Services”
2.      On the left navigation tree, go to “Component Services > Computers
> My Computer” (you may need to double click and wait as some nodes
need time to expand)
3.      Right click on “My Computer”, select “Properties”
4.      Select “MSDTC” tab
5.      Click “Security Configuration”
6.      Make sure you check “Network DTC Access”, “Allow Remote Client”,
“Allow Inbound/Outbound”, “Enable TIP” (Some option may not be
necessary, have a try to get your configuration)
7.      The service will restart
8.      BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN’T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
“Security Configuration” setting, make sure you check “Network DTC
Access”, “Allow Inbound/Outbound” option, restart service and computer
if necessary.
On you SQL server service manager, click “Service” dropdown, select
“Distribute Transaction Coordinator”, it should be also running on
your server computer.
Hope it helps,

Reference URL

http://msdn.microsoft.com/en-us/library/aa561924%28v=bts.20%29.aspx

ASP.NET SQL Server Registration Tool – Aspnet_regsql.exe

This tool is a part of Microsoft .Net framework and like other tools this tool is also located in [drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server.The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database. You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server installation, and installing or removing the database elements for the membership, role management, profile, Web Parts personalization, and health monitoring features. You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove, using the options listed in the table below.


See the Reference URL for Detail

List of datatypes in SQL Server

We are using very few data type in SQL Server like INT, varchar,datetime etc
But there are currently 37 datatype supported by SQL Server 2008

See the below scripts

• exec sp_datatype_info
• select * from sys.types 
• exec sp_datatype_info_90 
• exec sp_datatype_info_100

SQL Server 2011-Denali

SQL Server code-named “Denali”

Microsoft SQL Server code-named “Denali” empowers organizations to be more agile in today’s competitive market. Customers can efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and new user experiences expand the reach of BI to enable meaningful insights. With SQL Server code-named “Denali” customers will benefit from the following added investments:

• Enhanced Mission-Critical Platform: an enhanced highly available and scalable platform.
• Developer and IT Productivity: new innovative productivity tools and features.
• Pervasive Insight: expanding the reach of BI to business users and end-to-end data integration and management.

You can download CTP1 right away right now and install on your machine.

Book Online of SQL Server 2011 “Denali” is available here.
Installation guide for SQL Server 2011 “Denali” available here.

Reference URL