How to change server collation

The Server Collation acts as the default collation for all the system databases on that instance of SQL Server and also for the newly created user databases.
The Collation for an instance is specified during the setup of SQL Server, whereas this can be changed at any point of time by rebuilding the master database and specifying the new collation.
This operation will overwrite the system databases and hence it is strongly recommended to have a complete system backup before proceeding with this activity.

Before you proceed,

• Make sure you have backup of all user database, jobs, logins, maintenance plans, etc.. 
• Drop / Detach all user databases
• Rebuild Master database by specifying new collation

For SQL Server 2005,

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters

start /wait setup.exe /qb INSTANCENAME=SQL2005 REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=yourSApassword SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

This will start the GUI for setup

Once the Installation of Prerequisites is completed, you will be presented with the below screen

Click "Yes"

Once this configuration is completed, it will automatically close the GUI.
Now you can verify the change of collation by executing the below commands

SELECT SERVERPROPERTY('collation') AS [Server Collation]

For SQL Server 2008, SQL Server 2008 R2, SQL 2012,

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=adminaccount /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

Wait for the configuration to complete

Verify the change of collation by executing the below commands

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Once the activity of changing the collation is completed,

• Recreate / Attach the users databases
• Make sure to verify / recreate the jobs, logins, maintenance plans, etc.. 

Best Practices for tempdb

The tempdb is a system database which is available for all the users connected to that instance of SQL Server.
This database is used to hold

• Temporary User Objects
• Internal Objects created by the Database Engine
• Row Versions that are generated by data Modifications

Since tempdb is used by all users and the system by itself for all their temporary operations, it is very much important that we optimize the tempdb and follow best practices for getting better performance out of it.

Below are some best practices that can be followed for tempdb

• Create the number of data files for tempdb based on the number of CPU's present on that system. Example: if the system has 4 CPUs then create 4 data files for tempdb with one Log file.
• Place tempdb files on the fastest available Drive.
• Isolate tempdb on a separate disk from other databases.
• Make all the data files of tempdb the same size.
• Disable autogrow option for all tempdb files and make sure you have enough space in them.
• Make sure to Commit or Rollback the transactions and if not done, then any space allocated for that transaction may not be released.

Piecemeal Restore

What is Piecemeal Restore?
Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?
Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

T-SQL Query to find List of Tables that do not have Primary Key

We know that Primary Key is a must for setting up articles in Transactional Replication.
This query list those tables which do not have Primary Key in that database.

USE <DatabaseName>
GO
SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
Order by name
GO

Update statistics for all user database

In one of my previous post "What is Statistics in SQL Server", I had explained about what is Statistics.
Today, I am posting a Query which will update statistics on all user database in that Instance.

EXEC sp_MSForeachdb 'USE [?];
IF ''?'' not in (''master'',''model'',''msdb'',''tempdb'',''distribution'') 
 AND DATABASEPROPERTYEX(''?'',''Updateability'') = ''READ_WRITE''
BEGIN
Print ''Updating statistics for database "'' + ''?'' + ''"''
EXEC sp_updatestats
END'

This Query will exclude the system database and those database which are not in Read_Write status.