Saturday, March 31, 2012

Configuring P2P Replication in SQL Server 2012

Peer-to-Peer replication configuration in SQL Server 2012 has under gone a change. First now p2p can be see in the main replication wizard. So with SQL Server 2012, you don’t have to right click and select Peer-to-Peer and it is simply going through the wizard.


Service Accounts in SQL Server 2012

It is recommended to have different user accounts for each SQL Server services. For example, SQL Server service needs to have a separate account from SQL Server Agent Service.

However, practically many DBAs use same account for all the services. With SQL Server 2012, by default you will get different accounts while installing as shown in following image.

user accounts

Tuesday, March 27, 2012

Using Stored A Procedure with Temp Tables for SSIS Packages

Some stored procedures are complex and so require temporary tables or table variables. This FAQ tells you how you can do it.

Sunday, March 25, 2012

Preview of March User Group

We had another trail in this month edition of user group which is Lighting Talks. Honestly, I was not a fan of this before so I refrain from participating in it . However, it was nice to see few new presenters came forward to present their ideas.

Then I did my first ever presentation on SQL Server 2012. My idea was not to do any demos and just run through new features of SQL Server 2012. But when I saw audience is not happy, I came up with little demo of File Table which was well accepted by the audience with many question.

Then all MVP show began by discussing Career Guidance for DBA. All four of us came with our experience about interviews and Career paths.

Tuesday, March 20, 2012

March 2012 Meet-up

The SS SLUG will be running its March 2012 edition of its user group meeting tomorrow (March 21st) at Microsoft Sri Lanka. We have three sessions including an experiment with Lightning talks lined up for you...

Session #1

Anything-SQL Lightning Talks

2 minute back to back presentations on anything SQL Server related by SS SLUG members. 

Session #3

Tips on boosting your career

Join the local SQL Server  MVPs with tips and stories about how you can boost your career.

Session #2

SQL Server 2012 - What's New by Dinesh Asanka (MVP)

SQL Server 2012 was just released 2 weeks ago. Join Dinesh as he walks through what's new in it:

Dinesh blogs at

Microsoft Sri Lanka
11th Floor
DHPL Building
No. 42
Nawam Mawatha
Colombo 2

March 21st (WEDNESDAY), 2012
6:00 PM Onwards

Everyone are welcome. Entrance: FREE


More Information: SS SLUG Home Page

Hope to see you there...


February 2012 Meet-up Highlights


Tuesday, March 13, 2012

Verify Whether a SQL Server Agent Job is Running

When I posted an faq about Finding Running SQL Server Agent Jobs I had few emails and queries on how verify whether a job is running by using only a TSQL script. This FAQ will verify if the job exists and whether or not it is running.

Wednesday, March 7, 2012

Collation is Missing when Scripting the Database

You might know that you are not allow to modify the Collation after creating a database.

So you use the scripting option for the database you will see that collation is missing in the script.

Following is the script database create with Danish_Norwegian_CI_AS collation.


However, you can see that collation is not mention in the above script.

If you want to script you need to explicitly mention the COLLATE as shown below.


Collation Conflict

You can create a database with a different collation to the server collation. This is one issue you will face in case database collation is different from the server collation.


Then let us create a table with few data,


Let me use temp table to use this data.


So you are using temp table and actual table. (This is a hypothetical scenario just to present this idea).

Last query will return following error.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation.

This is due to the temp database has the server collation, so the workaround will be at the time of creating temp table, you have to explicitly mention the collation.


Tuesday, March 6, 2012

'Microsoft's next-generation data platform features customer management'

Microsoft Corp. today announced that the latest version of the world's most widely deployed data platform, Microsoft SQL Server 2012, has released to manufacturing. SQL Server 2012 helps address the challenges of increasing data volumes by rapidly turning data into actionable business insights. Microsoft has previously made a commitment to help customers manage any data, regardless of size, both on-premises and in the cloud. This latest version moves further towards those goals and allows Microsoft to compete in the growing cloud hosting space, where many competitors are offering managed cloud, hybrid cloud and community cloud services that provide higher levels of feedback and management for their customers. Along these lines, Microsoft also disclosed further details today regarding its plans to release an Apache Hadoop-based service for Windows Azure.

More at

Monday, March 5, 2012

Call For Lightning Talks and more




After the successful feedback we got regarding the Whirlwind DBA Demos at the February 2012 meet-up, we are doing something equally special at the March 2012 meet-up as well...

WHAT IS IT? We have planned for Anything-SQL Lightning Talks.
HOW DOES IT WORK? Each presenter talks for just 2 minutes with or without slides. Simple, no?
WHO SPEAKS? We need a lot of speakers to make the session successful. We need YOU.
ABOUT WHAT? Anything related to SQL Server. Things you just learned, experiences, tips, stories, anything related to SQL Server. Beginner or Advanced - you decide.
WHEN? March 21, 2012 - 6:00PM
WHAT NEXT? If you are interested, please sign-up using this
[SIGNUP FORM] on or before March 13, 2012 - 12:00 PM (IST)

Friday, March 2, 2012

Can we set model database to READ ONLY ?

When I saw this question, I thought “NO WAY”. However, I gave it a try.

To my surprise, I was able to set READ ONLY to model database.

Then I created the database and it was a read only database which didn’t surprise me.


You can see, I was able to to set model to read only and when I created a new database it is read only.

However, after restarting SQL Server , tempdb will not be in READ ONLY.

This is the surprise.

If you check , it says you can’t set model database to read only mode.