I saw an interesting post this morning care of the micro-blogging/social networking site twitter that really got me thinking about blogging.

John Sansom (Blog|@JohnSansom) wrote a very interesting post participating I believe in the second assignment of meme15. Meme15 is a monthly blogging event created by Jason Strate  (Blog|Twitter) that is designed to encourage SQL bloggers to write about blogging, a topic known as meta blogging. The blogging event ensures that several bloggers write about the same subject on a specific date during the month. Feel free to get involved. This is my first post in the said event.

The topics discussed in meme15 are very likely to have a wider scope than just SQL bloggers, if you blog on anything then I think the posts in this meme are likely to provide you with a different way of thinking about how and why you blog

The question this month is why should the average database professional get involved and use twitter. I like John’s take on twitter and the benefits he gets from it and I feel that I get many of the same benefits too. John’s main point on the use of  twitter, is its a place to network globally or locally with people with the same or similar interests to his. In John’s case he spends  time conversing with other people who work with SQL Server, helping out by answering questions that people post on twitter asking for help and no doubt sometimes he receives help when he asks a question. John makes a great point when he’s says you get what you put in and twitter is different things to different people.

If you use Twitter like John uses twitter then you will no doubt  get many benefits from it as a data professional. If you are a data professional with a blog there other benefits too that you can get from twitter if you are prepared to put in a little effort.

What are they? I hear you cry

Let’s be clear here, if you blog you do so for a reason, different bloggers have different reasons for blogging but ultimately everyone blogs to be read by someone. Whatever your reasons for blogging. One of the things that kills off most bloggers after the initial enthusiasm has worn off is the fact they are not getting read or have a very low readership, their visitor numbers are not in the millions that they were expecting  and they simply give up and stop. Twitter can help you build a popular blog.

Twitter can help build traffic to your site

Let’s use John as an example. I have been conversing with John via twitter for a few years now, I don’t actually remember how we came to follow each other but it was definitely after a SQL Server discussion. Through twitter I found John’s blog and I subscribed to his blog via Google reader. No doubt others have done the same. John, via twitter, talks and networks with different people around the globe all with a common interest, SQL Server . John has a SQL Server blog where he can expand and go beyond the 140 character limit that twitter has and write posts and articles with much greater detail. John posts his links to blog posts out to twitter, his followers are likely to have interest in the topic he has written about  and follow the link to the post and get involved in John’s  blog. John’s use of twitter for all the reasons he explains in his post me he has engaged, active and interested people visiting his blog. The type of people who are likely to leave a comment and get involved in discussion

It can help to get your post in front of others

You can benefit from your post being re-tweeted (RT) by your followers so your traffic flow  doesn’t stop with your immediate network, John writes a weekly round up or review of all the blog posts he has read and found interesting during that week. There was a time when my posts used to make his round up but my mentions in there seem to have tailed off over the last few months.Never mind. John’s Something for the Weekend series is a great read basically John publishes a blog post out with links and short review of all the blog posts he has read and found interesting through the week. His post goes out every Friday and if you have had a busy and not had time to stop and read blogs you get a nice place to start with some of the better blogs published that week. Now lets say John publishes a link on twitter, John has several hundred follower potentially if they all retweet his post then he could very well end up with link in front of thousands of people all with a link to someone who likes to tweet about SQL Server. I hope you can see the potential for increasing traffic to your site.

Twitter can give you ideas

If you put the effort into Twitter you will meet many interesting people who share some similar interests to you. The people are likely to post things on twitter that give you ideas. John’s post this morning led me to writing this post!

If you are a data professional then Twitter can have a number of advantages, if you are a data professional with a blog then twitter can be a great place to help build and grow your blog.

If you want to read more about building a successful blog this is a really good book with some really good tips in it ProBlogger: Secrets for Blogging Your Way to a Six-Figure Income

It’s always handy to know if there any problems with your servers before you get into work in the morning. This is why I wrote a program that goes round all our SQL instances at 7am and checks they are all up. If it doesn’t get a reply from one it emails or pages me to let me know. So I know what to expect when I get in and can “hit the ground running”.

I have a table with the names of all the servers and instances and my program simply works its way through the list this code actually check the state of the Windows service. The service name for the default instance is straightforward but you will need to work out the correct name for your named instances.

Of course there may be occasion where the service reports as running but actually SQL isn’t responding. An alternative method would be to run a SQL command against each instance and see if there is a SQL response. I use that for DBMS and SSAS  but it doesn’t work  for SSIS and a lot harder for SSRS since it’s a web service.

   1:   

   2:  using System.ServiceProcess;

   3:   

   4:  string strServiceName = "";

   5:  switch (ServerType)

   6:  {

   7:  case "SSIS 2005":

   8:      strServiceName = "MsDtsServer";

   9:      break;

  10:  case "SSIS 2008":

  11:      strServiceName = "MsDtsServer100";

  12:      break;

  13:  case "SSRS":

  14:      strServiceName = "ReportServer";

  15:      break;

  16:  case "SQLServer":

  17:      strServiceName = "MSSQLServer";

  18:      break;

  19:  case "SQLAgent":

  20:      strServiceName = "SQLServerAgent";

  21:      break;

  22:  case "SSAS":

  23:      strServiceName = "MSOLAP";

  24:      break;

  25:   

  26:      }

  27:   

  28:  try

  29:  {

  30:  ServiceController sc = new ServiceController(strServiceName, ServerName);

  31:  switch (sc.Status)

  32:  {

  33:      case ServiceControllerStatus.Running:

  34:  Console.WriteLine(ServerName + "Running");

  35:  break;

  36:   

  37:      default:

  38:  Console.WriteLine(ServerName +  " ***** Error: " + "Service is " + sc.Status);

  39:  break;

  40:  }

  41:      }

  42:      catch (Exception ex)

  43:      {

  44:  Console.WriteLine(ServerName + " ***** Server Morning Check Error:" + ex.Message);

  45:   

  46:      }

Written by Ian Treasure

Gethyn posted on the use of execute permissions a while ago (db_executor at http://www.gethynellis.com/2010/04/dbexecutor-role.html). Briefly, this approach uses a role – db_Executor – to which users are given membership. The execute permission is granted to the role.
 
I am using this approach to control access to stored procedures. Some developers started to complain that they could not execute their stored procedures. The first thing that I did was to check if their user was a member of the db_Executor role. It was, which was a surprise. I then started to check that the role had executor permissions. This was not so easy to do.
 
After a little digging, I used sp_helpprotect as follows.
 
In SQL Server Management studio, I ran:
 

sp_helprotect 'execute'

GRANT EXECUTE TO [db_executor]

At the end of last week I received an email from my friend and colleague Sharon Dooley telling me her “Lightening Talk” had been accepted and she would be speaking at the Summit in October. Sharon has many years experience in the IT industry, a former SQL Server MVP, currently working as a trainer and course author specializing in SQL Server. She is a volunteer with PASS and is the Virtual Chapter Lead for the DBA virtual chapter. Sharon is also involved in the Women in Technology virtual chapter.

A couple of weeks ago Sharon and I were chatting about the PASS Summit and even though she hadn’t submitted a full session she was considering submitting a lightening talk. Sharon doesn’t currently maintain a blog so I said if she did submit a talk and it was accepted I would promote her “Lightening Talk.” Sharon has a really awesome query that returns a user’s permissions; she will be demonstrating this query during her talk. She also promised to share the query with us after the Summit so those of us who can’t get to Seattle can use it too. So we’ll have an excellent guest post to look forward to later in the year.

There will be more details on Sharon’s talk to follow but Sharon has asked me if I would mention the PASS Women in Technology Luncheon at which she is also on the Panel. The 9th Women in Technology Luncheon and Panel discussion will take place at the PASS Summit in Seattle on Thursday October 13, 2011. This year's topic is: Make Yourself Heard: How to Ask for What You Want at Work. In 2003 the first Women in Technology panel discussion was held at the PASS Community Summit. Since that inaugural panel, the WIT event has been a highlight of the Summit, with captivating speakers and lively discussion on topics relating to women in the field of technology. Participants at these panel discussions were eager to continue networking and learning from each other which led to the launch of the WIT Virtual Chapter. You can find more information on the Women in Technology Luncheon and the virtual chapter in general here (http://wit.sqlpass.org/ ) You can follow the Chapter on twitter @PASS_WIT or following the #PASSWIT hash tag.

Last week I had an interesting telephone call from a someone who I connected with on linkedin.com. On Linkedin I’m what they call an “open networker” which means I won’t click on ‘I don’t know you’ if you send me a connection request. If I don’t want to connect with you then I’ll simply archive your message/request.  I use linkedin.com primarily for work and business. I use it to connect with fellow DBAs and database professionals  and I also connect with recruiters/agents whose business is to fill SQL Server contract roles. At the time of writing I’m well over 500 connections but have a fair way to go before I get to 1000. If you would like to connect please do feel free to send me a request here.
A SQL person sent me a connection request which I accepted  and he then sent me an email and then phoned me up asking if I would help him out. The crux of the phone call was around him getting more experience using SQL Server. He has a Virtual Lab set up with a SQL Server in it, he wanted me to help him recreate real life examples of some of the problems a DBA faces.
Now having thought about this for a second, I decided that although I could help and create some contrived examples it would be much better for this person to solve full on real life examples. I told this person that although I could help with this request his best bet at gaining more exposure to SQL Server and more of the problems that people can experience with different aspects of the product would be to help try and solve some of these problems himself. I directed him at the forums on SQLServerCentral.com and over on MSDN and suggested that he use his environment to try and help people with their problems. Even if he didn’t know how to solve that particular problem himself, reading the posts and suggested solutions and then working through the problem in his own environment would help him learn.
I guess the purpose of this post is this, in helping other people solve their SQL problems, you gain great experience in aspects of SQL Server that you may never come across in your normal day job, which can only help build your knowledge and experience.
Feel free to send me a Linkedin connection request.

I wrote a post a while back that showed how you can grant execute permission ‘carte blanche’ for a database role in SQL Server. You can read that post here. This post is going to build on that concept of using database roles for groups of users and allocation permissions to the role. I recently had a situation where a tester wanted permission, for themselves and the rest of the testing team, to look at the definition of all the stored procedures on a specific database, strangely enough for testing purposes. I thought for a while on how best to grant this permission, I did not want to grant the VIEW DEFINITION permission at the server level or even the database level. I just wanted to grant for all the store procedures that existed in the test database at that time. This is the solution I came up with:
Create a database role in the specific database called db_viewspdef

CREATE ROLE [db_viewspdef]
GO

USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_viewspdef', N'DOM\TesterGroup'
GO

SELECT  *
FROM    sys.objects
WHERE   type = 'P'
ORDER BY name

SELECT  'GRANT VIEW DEFINITION  ON ' + s.name+'.'+d.name + ' TO [db_viewspdef]'
FROM    sys.objects d
INNER JOIN sys.schemas s ON d.schema_id =s.schema_id
WHERE   type = 'P'
ORDER BY d.name

Back in November, I was lucky enough to be nominated and then got enough votes to win the New to SQLServerPedia category in the SQLServerPedia.com’s inaugural blogger awards. Quest sent all  those nominated a nice prize pack of notepad, pen, T-shirt and best of all some “BuckyBalls” which was a pretty cool prize in it’s own right. The winners of the respective categories also got an additional gift or trophy. And here’s mine. It arrived this morning, I was out but the UPS guy left it with my neighbour who had the honour of presenting it to me, here it is.

The winners cube. Very cool.

There was an interesting question posted on a forum recently, asking how can you tell which sqlservr.exe process (shown in task manager) is mapped to which specific instance of SQL Server running on a server, if you have a multi-instance server and the account running each separate SQL Service is the same. I won’t go into the details of the post, but I thought the answer worth of a post of my own. As with most things in SQL Server there is more than one way to obtain this information.

Let’s assume that you, like me, have two instances running on you server: when you go to task manager you will see two sqlservr.exe processes listed under the processes tab

The first thing that I would do is add a column called PID to this view. Select <View> <Select Columns…> from the toolbar menu. Select the PID (Process Identifier) column checkbox and select <OK>

This will give you the process ID of each process running on your machine and displayed in task manager including your two sqlservr.exe processes.

Now you have the process id of each process you can either:

• Open up SQL Configuration Manager
• On the SQL Services tab right click on the SQL instance that you want to match up to a process
• Select <Properties> and click the <Services> tab
• On the general tab you should see a process id property

The other alternative, is to connect to each instance in SSMS (Management Studio) and open the SQL Server log. When the service starts up it writes the process ID to the SQL Server log. So if you go to the start of your log file you should see an entry similar to the following:

I published a post yesterday where I listed my top 5 bloggers from the worldwide SQL community for 2010. The long and short of it, these were the people whose blog’s I had read the most throughout the year. There are many other great blogs and bloggers out there that I read regularly. When that post published and fed out to twitter it prompted a few people, whose blogs I do follow and value, to give me a bit of stick . Pretending to be  all hurt and upset. I won’t name any names (John Sansom Blog|@johnsansom) but it did get me thinking.

There are many great bloggers based here in the UK several of which were recently nominated in the sqlserverpedia.com annual blog awards for 2010 (a couple of us won in our categories too).  I realised that although I read many of these UK based blogs and several were considered for the top 5 bloggers 2010 post, none of them made my list! So having thought about it I decided to make my own UK specific list. I was going to go for the top 5 approach, but after a little more thought I decided to compose a list of all the UK based SQL blogs that I follow. This is my list to date (no particular order):

• John Sansom Blog|Twitter
• Tony Rogerson Blog|Twitter
• Jen Stirrup Blog|Twitter
• Simon Sabin Blog|Twitter
• Christian Bolton Blog|Twitter
• Gavin Payne Blog|Twitter
• Neil Hambly Blog|Twitter
• Allan Mitchell Blog|Twitter
• Justin Hostettler-Davies Blog|Twitter
• Mark Broadbent Blog|Twitter

Undoubtedly I will have missed someone off that I should have included, and for that I am truly sorry. That little oversight on my part is easily rectified. Simply leave a comment on this post with the bloggers name, blog URL and twitter account (if one exists). I will then update this list and add you to my feed reader.

Go on then, who have I missed?

Hopefully my title gives away what this post is about. This my list of the top five people or more precisely their blogs that have made most use out in the year gone, I am not going to put them in any specific order they all add to my knowledge in different ways:

• Brent Ozar(Blog|@BrentO)
• Paul Randal (Blog|@PaulRandal)
• Steve Jones (Blog|@Way0utwest)
• Grant Fritchey (Blog|Twitter)
• Andy Warren (Blog|@SQLAndy)

Also it is worth noting that besides these guys there are many other great blogs and bloggers out there that I read and fully appreciate, I needed a cut-off that wasn’t that deep but if you do blog and write keep going, keep writing and keep blogging.

Who would be in your top five, would it differ from mine? let me know in the comments below.