Home » 2014 » June

Monthly Archives: June 2014

Rename or Change SQL Server Standalone Default Instance

Today, We will go through with the steps of renaming or changing SQL Server Standalone Instance.

Step 1 : Check Current Instance & host name

Current Host Name : admin-0783e4076

Current SQL Instance Name : admin-0783e4076

Select @@ServerName ServerName, Host_name() HostName

Step 2 : Rename Host name & reboot the server

Step 3 : Try to connect with SQL server admin-0783e4076 & you will face below error because no instance of server name [ADMIN-0783E4076] exists as server name got changed

Cannot connect to ADMIN-0783E4076.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Step 4 : Try to connect with “.” And Re Check Instance & host name

Select @@ServerName ServerName, Host_name() HostName

We have noticed that SQL instance name is still referring to old name. We need to change it as well.

Step 5 : Drop old server name from server list of SQL

Exec sp_dropserver [ADMIN-0783E4076]

Step 6 : Add new server name as default server in server list of SQL

Exec sp_addserver [Win2K3_1],local

You can check default & other server ddetails from sys.servers.


Step 7 : Restart SQL Services

Step 8 : Try to connect with [Win2K3_1] And Re Check Instance & host name

You are done !

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server 2014 – How to add features to existing instance ?

SQL Server 2014 – How to add features to existing instance ? :-

1) Start installation center

2) Run Global Rule, You need to fix if any failure occur

3) Microsoft update option (Enable windows updates to search & install SQL updates as well)

4) Include product update with installation, (the latest available SQL Server product updates are displayed, when online)

5) Install Setup Files

6) Run installation rules, You need to fix if any failure occur

7) Select instance to add feature

8) Select features to install

9) Final cconfiguration

10) Installation in progress

11) Installation completed successfully

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Fix for index corruption issue || SQL Server 2012 Service Pack 2

ISSUE : FIX for Data loss in clustered index occurs when you run online build index in SQL Server 2012

Microsoft release a fix for above issue and now available for download for SQL Server 2012SP2.

Refer Article :


Download Link :


If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server 2012 || Contained Database

Contained databases are the new feature in SQL Server 2012 and are defined on BOL (http://technet.microsoft.com/en-us/library/ff929071.aspx) as below :

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2012 helps user to isolate their database from the instance in 4 ways.

· Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)

· All metadata are defined using the same collation.

· User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.

· The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.

The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.

Containment setting :-

SQL Server has 2 types of objects or features one that can be contained like user objects, indexes and second that cannot contained, which depends on instance Like login details etc.

1. None : Database settings when all objects inside or outside database boundaries managed by SQL instance.

2. Partial : New feature that comes with SQL Server 2012, where some possible objects managed by databases & other uncontained objects by SQL instances. You can use DMV sys.dm_db_uncontained_entities to check uncontained objects or features of your database.

3. Full : Full containment setting is a condition when database can handle everything by itself. When database not required SQL instance. This feature is not feasible for SQL server till yet.

Advantages of contained databases :-

1. User authentication can be done at database level

2. Contained databases have less dependency on instance then conventional databases. Objects & features of each database can be managed by them self, reduce workload of system database & SQL instance

3. Easier & Faster to migrate databases from one server to another. Errors related to missing users and orphan users are no longer an issue with contained databases

4. Contained database users can be Windows and SQL Server authentication users

5. Contained database user can access only contained database objects. They cannot access system databases and cannot access server objects

6. Bestto be used with HADR (Always On)

7. Maintaining database settings in the database, instead of in the master database increase security & flexibility. Each database owner have more control over their database, without giving the database owner sysadmin permission

8. To close collation issues in contained database. New feature catalog collation introduced with contained database. Now database collation works for user objects & catalog collation works for system objects in database. Catalog collation will be same for all contained databases on all SQL instance, also this collation cannot be changed.

Disadvantages and Limitations of contained databases :-

1. A database owner has more control on contained database, User can create contained database users without the permission of a DBA that can lead to security issues & data theft threat

2. Contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes

3. Before changing containment settings at database level from NONE to PARTIAL , contained databases feature needs to be enabled at instance level

4. To connect to contained database, you need to specify database name in default database option

5. Temporary stored procedures are currently permitted. But can be removed from future versions of contained database

6. Contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account

Issues Faced :-

1) SQL Server instance is running on windows mode and you are using SQL User with password to connect with contained database. You must enabled mixed mode authentication for this

2) Contained database feature is not enabled on SQL Server instance where you have moved your contained database

3) Known Issue(http://support.microsoft.com/kb/2894326) : An application cannot connect to a contained database when connection pooling is enabled in SQL Server 2012 or SQL Server 2014

4) Default database not mention at the time of connecting contained database

Steps to try Contained database :-

1) Enable “contained database authentication”

· By Script

sp_configure ‘contained database authentication’, 1;




· By GUI

o Go to Server Property

o Select Option from False to True

Note : This GUI option will not be available, If you are using SSMS older than SQL Server 2012.

2) Create partially contained database

· By Script

USE master





· By GUI

o You can select Containment type during creation of new database from option page

o You can change Containment type of existing database from option page

3) Create the SQL user with password in contained database

· By Script

USE [ContainedDB]


CREATE USER [ContainedUser] WITH PASSWORD=N’Password@123′


Exec sp_addrolemember ‘db_owner’, ‘ContainedUser’


· By GUI

4) Create the Widows user in contained database

· By Script

USE [ContainedDB]




· By GUI

5) Migrate existing user to contained

–Create Login

USE [master]




–Create User Mapped with Login

USE [ContainedDB]


CREATE USER [MigrateUser] FOR LOGIN [MigrateUser]


–User created & mapped with login

–User migrated to contained user

USE [ContainedDB]


EXECUTE sp_migrate_user_to_contained

@username =N’MigrateUser’,

@rename = N’keep_name’,

@disablelogin = N’disable_login’;


–User changed from SQL User with password from SQL User with Login

–As a result login disabled on SQL instance

6) Connect to Contained Database

· Enter User ID & Password

o SQL User

o Windows User

· Mention Default Database to Connect

· Connection Established

o By SQL User

o By Windows User

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

ColumnStore Indexe with SQL Server 2012 & 2014

ColumnStore Indexes (Run By Microsoft’s VertiPaq technology) as described by BOL (http://msdn.microsoft.com/en-IN/library/gg492088(v=sql.110).aspx) :

“An xVelocity memory optimized ColumnStore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index. For some types of queries, the SQL Server query processor can take advantage of the ColumnStore layout to significantly improve query execution times. The explosive growth of data warehousing, decision support and BI applications has generated an urgency to read and process very large data sets quickly and accurately into useful information and knowledge. This growth of data volumes and escalating expectations are challenging to maintain or improve through incremental performance improvements. SQL Server ColumnStore index technology is especially appropriate for typical data warehousing data sets. ColumnStore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.”

ColumnStore Indexes is completely a new type of indexes introduced with SQL Server 2012. ColumnStore index is totally different in architecture from previous index structure. Regular index are row store that means regular indexes (B+ index structure) stores data on basis of rows but column store indexes sorts data on basis of column. ColumnStore indexes are basically introduced for OLAP (Data warehousing) systems. New Type of data compression also added called ColumnStore Compression.

Benefits of Column store index :-

1) Each page stores data only on basis of column. That give significant improve in performance when fetching selected columns from table

2) One page one column data, increase the chances of high percentage of data compression because of similar data type & data for same column

3) Does not physically store columns in a sorted order. Instead, it stores data to improve compression and performance

4) ColumnStore Index by default compressed data by New ColumnStore Compression

Restriction of Column store index :-

1) Cluster ColumnStore index cannot be combined with other index types, you can use Non-Cluster index to combine it with other indexes

2) Only Clustered Column index are updatable on the other side Non-Clustered ColumnStore index are read only

3) Non-Clustered CloumnStore Index requires extra storage space for column copy in index

4) Majorly introduced to get performance benefit in OLAP systems, but if required you can use it in OLTP

New Enhancements with SQL 2014 :-

1) SQL Server 2014 supports Clustered ColumnStore Index whereas SQL Server 2012 supports only Non-Clustered ColumnStore Indexes

2) In SQL Server 2012, As soon as you had created a ColumnStore Index on a table, the underlying table was read only, and no changes to the data were allowed. But SQL Server 2014 allowed to have updatable ColumnStore Index

3) Deltastore used with clustered ColumnStore indexes only, to gain performance.

a. Deltastore is a container or storage for rows before it moves to ColumnStore index. Deltastore introduced to improve performance for DML & select operations.

b. Bulk operation having minimum 102,400 rows goes to ColumnStore directly & operation having rows less than 102,400 rows will be moved to deltastore before it moves to ColumnStore

c. When the deltastore reaches the maximum number of rows, it becomes closed. A tuple-move process checks for closed row groups & moves them to ColumnStore

4) Features not compatible with ColumnStore indexes :-

a. Page and row compression

b. Replication

c. Change tracking

d. Change data capture

e. Filestream

Datatype not supported :-

1) binary and varbinary

2) ntext , text, and image

3) varchar(max) and nvarchar(max)

4) uniqueidentifier

5) rowversion (and timestamp)

6) sql_variant

7) decimal (and numeric) with precision greater than 18 digits

8) datetimeoffset with scale greater than 2

9) CLR types (hierarchyid and spatial types)

10) xml

Rebuild ColumnStore index :-

Rebuilding ColumnStore index having not different syntax that regular index Rebuild syntax.


How to create ColumnStore Index? :-

Creating column store index having not different syntax that regular index creation syntax. You just need to mention keyword COLUMNSSTORE to create column store index.

o Create Cluster ColumnStore Index :-


Note : No need to mention columns for index, all columns ill be automatically included in columnstore index.

o Create Cluster Non-ColumnStore Index :-


If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: