SQLServerPedia

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|@gfritchey)
• 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.

Steve Jones mentioned recently that SP4 for SQL Server 2005 is on its way because Microsoft closed his connect item.

Well looks like it was released yesterday, you can download it here:

 http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b953e84f-9307-405e-bceb-47bd345baece&displaylang=en

So this month’s blog party is being held by non-other than Steve Jones (blog|@Way0utwest) of SQL Server Central fame. This month’s topic is all about the business, your customers, clients etc asking for something but not really knowing what they are asking for. Or  in my interpretation the business, client, customer, telling you the technology or methodology or tool to use instead of what really they should be telling  you…what the business wants. Steve’s example of in his post sums in it up perfectly.

I have many examples to draw on here, I guess the one that springs to mind the most was a client that told me they wanted to ‘Shrink the Database’ as part of their maintenance routines. When I asked why? that’s self defeating. I was told that the databases were growing uncontrollably and this needed to be included in order to keep the systems up and running and to avoid eating up all available disk space. Smelling a rat I decided to do a little investigation of my own and I found that they had several databases that were in full recovery mode BUT they were not taking regular transaction log backups. When I mentioned this to my clients and explained the difference between simple and full recovery mode and what it meant in terms of point in time recovery we decided that maybe some regular transaction log backup were in fact needed. we then re-sized the log and freed up a lot of space and my client was very happy.

Anyway that’s my post for the last TSQL Tuesday of 2010, It is a very interesting, if not possibly controversial topic for someand I’m sure that many of the community will have examples of their own and I look forward to reading them and how others have overcome these issues.  

As I mentioned in an earlier post I have recently moved my blog to a new domain name. This is something I have been thinking about for while but in truth and with hindsight I did not manage the transition all that well. I let the old domain and hosting run down to expiration before getting my new one up and running which meant that I lost control of the old domain and I wasn’t able to use some of Google’s ‘Change of Address’ features to help the transition of the old domain to the new name. BUT we live and learn and not all is lost. My new domain has been picked up in the Google crawl already and although it is very likely to be six months to recover in terms of Search Engine rankings and SEO traffic I feel the benefit of the change will benefit my brand in the long run. So although is not quite like starting again I do feel like it has been a step backwards but sometimes a step backwards can help you go forwards. This is an opportunity for me to learn a few things and take my blog and my brand forward.

Over the last couple of years in particular I realised the power of blogging and how it adds to your brand. I work as a SQL Server contractor in UK and as such I run and own a company. Contracting generally consist of short term assignments and the contracts that my company secure generally involve  working with clients SQL Server infrastructure. Having a blog can definitely help you secure work and contracts. I will post more on how this has helped me later, probably next year, but my company’s business model is based on selling services or more to the point selling me. So I thought a domain name that included my product name and also allowed me some freedom of topic choices would be beneficial which is why I changed my domain to my name -  gethynellis.com. I can see the benefits of my blog and the value it adds to my brand and my business because of this I follow a few ‘how to blog successfully’ blogs and including http://www.problogger.net to try and keep a breast of good techniques and practices.

I’m not going to go into much detail about why you should blog or how you should go about it if you are a SQL Server professional. I will say that blogs can work effectively for permanent employees too, in effect you are improving your brand and employability with potential employers not only demonstrating and documenting your technical skills but also demonstrating and proving your  communication which are essential skills for any DBA. I may post on this myself later but for now I’m not going to re-invent the wheel,if you are interested in blogging and you are a SQL Server professional go and check out Brent Ozar’s posts on how to start blogging.

It was Brent’s post along with fact I read their blog led me to buying ProBlogger: Secrets for Blogging Your Way to a Six-Figure Income. There will be a review to follow but I picked this up over the weekend and having read the first few chapters it seems like a great book for any blogger, who is serious about blogging to read.

This post will be my 100’s for the year, which was one of my targets at the start of the year. I’d like to say that my first full year blogging has been relatively successful, hopefully I will be able to continue that success into next year with new domain.

About a year ago I started to write a blog, previously to that, for about three years I had been writing a SQL Server themed tips and articles on my website. When I started writing the blog I decided to build it into my existing domain name, that I think was a little mistake on my part. It’s that time of year again when it was time to renew my domain name, I hadn’t written on the actual site for well over a year, people reading the site itself had fallen through the floor as I was no longer paying it any attention,  the hosting costs were creeping up with that particular provider and I found that as I’ve developed and increased my contribution  to the SQL Server community my name has become more and more important. The existing domain name, I felt did not really represent me, it was long, possibly too long and difficult to remember and if you came across it on a Search Engine search you wouldn’t necessarily know that it was my site without clicking the link and visiting the site. So after some deliberation and thought I have decided to go for a new look. I revamped the blog site a month or so back which I think now looks clean and tidy and pretty easy to navigate. Yesterday, which incidentally coincided with my old domain expiring, I started up gethynellis.com and pointed it at my old blog. Nothing has been lost and all my old blog posts have been kept so all is good there, it is now obvious from the domain name whose site you are on and that will go that little bit further in promoting my brand – me! to the wider world. A key factor in my decision to change though was down to the fact that a lot of people read what I write on other sites, like SQLServerCentral.com and SQLServerPedia.com when I started the blog a year ago I followed some of Brent Ozar’s (Blog | Twitter) advice and used feed burner for my feeds and got myself syndicated on a couple of the large SQL Server sites. This allowed my to change the base domain for my feed without needing any of my readers to change their subscriptions. The syndication on SQL Server Pedia uses this feed. I’m not sure if SQL Server central does though I will find out if the posts makes it onto their site. If doesn’t I’ll have to email Steve and ask him to update it.

The Drawbacks and mistakes that I made

Changing the domain name of my blog, technically, was an easy thing to do, in reality though this approach has many drawbacks. It is pretty much like starting again. Even though all my content is still out there on the web any external links to it will be pointing to the wrong place, Which will not help my new domain in the search engine rankings but more importantly if you link to my content on the old domain  that link will no longer exist and your site users will get taken to a horrible error message that they will no doubt find frustrating and I am really very sorry about. If you have links to any of my blog posts can I please ask that you update them with the new URL it’ll help your readers and of course me :-)

I am in the process of updating my old posts which link to other pages  on my blog so I maintain a nice set of internal links that I’m told search engines like. This after years worth of posts, which total at just over 100 posts, is quite a lot of work.

As I type this Google and Bing and other search engines still don’t know about my new domain, hopefully it won’t be long before they pick up the new content. I did look into Google’s “Change of Address” feature in webmaster tools but unfortunately I no longer have access to the old domain so 301 redirects are out of the question, I’m not sure they the old hosting company supported them anyway, and although I verified the old domain in webmaster tools years ago, Google tried to re-verify it when I tried to change the address but unfortunately that failed too. The sub domain my blog was sitting on couldn't be re-directed because it’s not a root level domain so that tool, at least for now unless someone advises me otherwise, seems unlikely to help. I have followed the other advice Google offers when changing a domain name, I have added my new domain to webmaster tools created a site map and updating internal and external links to the site. They also recommend retaining control of the old domain for 6 months after the move, not planning this domain change that far in advance and the fact the old domain has now disappeared I won’t be able to this either. So we’ll see how it goes but I do envisage my visitor numbers falling significantly over the next few months while the Search Engines sort themselves and my new domain out.

This last year I have had just under 10,000 unique visitors to my site (according to Google Analytics), a figure I’m pretty proud of, although another 6 people visiting would have been a great figure to look at! It will be interesting to see how this changes impacts on that figure over the next few months, here’s how the unique visitors numbers over the last year looks on my blog today:

Lessons Learned:

• Give yourself plenty of time with the old domain still running, it will allow you a much smoother transition to your new domain name and keep you much more in control. Although I have been thinking about this for a while, I only really looked into the actual logistics of this when the old domain was about to run out. Then it was too late for me.
• Pick a good domain for your blog right up front, save bothering with all this in the first place.

If you have any further advice to offer me (Or others) about what I should have done differently or anything that I can still do to improve the efficiency of the move to the new domain then please let me know in the comments below.

I will keep you posted in the New Year with and update on how the transition went.

The results of the SQLServerPedia Awards 2010 are out....I was nominated in two categories and I won the "New to SQLServerPedia" award. Being nominated was a nice unexpected surprise and winning is unbelievable, it actually ties in nicely with my blog's one Hundredth post and a nice way to finish my first year writing on the blog. The "New to SQLServerPedia" category had some some greater bloggers nominated and two of those nominated in the "New to SQLServerPedia” category won awards in some of the other categories,  Jason Brimhall (Blog|Twitter) won an award for the "Book Review" category, you can view that post here and Bob Pusateri (Blog|Twitter) won an award in the "Internals" category, you can read Bob’s post here. So there was lots excellent of competition and I can’t believe I actually won. Congratulations to Bob and Jason and all the contestants on on their success . I'm just sorry I couldn't make the trip to Seattle to pick up the award in person.!
I'd like to take this opportunity to thank everyone that voted for me.
I have got my Winners Badge:

Hopefully my statue/trophy will be winging its way to me in due course (Pics to follow).
You can check out all the results here: http://pulse.sqlserverpedia.com/awards/

I wasn’t going to make a special effort for the one hundredth post on my blog, I was intending to let it slip by without much of a mention…But I couldn’t help myself. I have maintained a SQL themed website since 2007 without much luck in term of hits and visitors. When I started out in 2007 I bought some hosting that included a content management system, but that was clunky and at best difficult to use. In three years of it running and writing for that site I probably churned about 50 articles/posts…tops. Late last year (2009) after following along and reading some of the well known bloggers out there like Steve Jones (Blog|Twitter) and Brent Ozar (Blog|Twitter) and just before attending the SQLBITS conference at the Celtic Manor (home of the 2010 Ryder Cup) I decided to start blogging. My reason being, I was writing for my website and now on my blog mainly for my own benefit. I find writing helps me learn and remember technology much better than reading alone, it forces you to think about a subject and test and experiment and use the technology so you know what you are writing is accurate…in short it makes you do the work. You learn from the work you do, writing makes you understand and remember more clearly than reading and practicing and using alone. You always have documentation on hand, that you wrote, for future reference. It also stands as a good example of what you know and what technical skills you have. I saw people like Steve and Brent churn out more posts in a week on their respective blogs than I would be able to do in a month on my website. So I started a blog and incorporated it into the website, (they actually share a domain name) and I started writing on the blog. I have been doing it for a year and I have hit my one hundredth post. I feel like I have learnt a lot in the last year!
But it doesn’t stop here, my blog has had some minor success, when I started it, obviously visitors numbers were non-existent very small, but through some help, from people like Steve and Brent who syndicated my blog on their very large and very successful SQL Server websites and some sound SEO advice from my good friend Mandeep Kaler my visitor numbers grew, slowly, but they grew. I then found out about #TSQL2SDAY the monthly blog party, the brain child of Adam Mechanic (Blog|Twitter), and I started writing in a few of those, I found these improved my exposure to the other bloggers in SQL Land and again my visitor numbers crept up. I checked Google Analytics today for the first time how many unique visitors have been to my blog this past year…I’m about 800 unique visitors shy of 10,000! WOW! I was shocked and I still am, I didn’t know that so many people had been to my blog and I hope that those people got something useful from it.
I recently saw an an announcement on SQLSERVERPEDIA,  that they were to hold a blogger awards to coincide  with the PASS summit. The fact I’m not attending the PASS summit, even though I would love too meant I didn’t pay as much attention to these awards, I saw post from various well-known bloggers, promoting their nomination but I have been busy and not had time to catch up on my reading, until today I saw a post today by Grant Fritchley that mentioned he was up for nomination for his post on Powershell, I liked Grant’s post on powershell, so I thought I would pop along to the sqlserverpedia.com website, have a look at the nominations and probably, in all likelihood vote for Grant. I clicked the link, the page loaded and the first entry I saw was a post called “Policy Based Management - Disable Autoclose” I thought to myself, I wrote a post with a similar title not so long ago, I then looked at the bio and the author and I was shocked to find that it was actually my post up for nomination. I’m sorry Grant I didn’t vote for you in that category, although I’m way behind you in the count so I don’t think I will prove much competition for you.
Having shamelessly voted for myself in “Automation and Management” I then, after I got over the initial excitement of being nominated, thought I would check out some of the other categories, I did vote for Grant in the “Series” category though for his series “An Introduction to Indexes” series I looked at the the “New to SqlServerPedia” category and to my shock and amazement I found that I was nominated there too!!! Again though I’m lagging way behind the rest of field , not that that bother me at all, I wasn’t even expecting a nomination, I have been shocked to have been nominated, not once but twice!
That said, people are on the campaign trail and I don’t want to lose by a country mile so if you like to vote for me or my post then you can do so:
New to SqlServerPedia
Automation and Management
I’ll be honest, these nominations have been a fantastic way for to round off my first year blogging properly and my one hundredth post, so standby for the next one hundred.

I have been catching up on some blog posts and I read a few posts by well known bloggers like Grant Fritchely and Mike Walsh about voting for their posts in the SQLServerPedia blog awards. So I visited the links they provided to have look at what was what and who was nominated and then maybe vote. I was stunned to learn that I have been nominated in two categories!
I have an entry in the management and automation category for my Policy Based Management post. I’m in there with along with Grant and a few others.  I have also been nominated in the New To SQLServerPedia category. WOW! I still pretty stunned to be honest, I’m in there with some great writers.
So if you would like to vote then a list of all the categories can be found here. And If you did happen to vote for me, then that would be fabulous.

I got asked a question today that I though was quite interesting. If my backup starts at 6pm and it runs for say 30 minutes what happens to the transactions that occur during the 30 minutes that the backup is running?

The short answer:

All committed transactions that run during the duration of the backup will be included in the full backup, open transactions will not be included.

The proof:

Please note, that your database will get around 784MB in size and the backup created will be roughly the same size, so before running these scripts, make sure you have enough space on the box you testing it on.

I will create a dummy database called buptest for the purpose of this test

USE master
go
CREATE DATABASE buptest
GO
ALTER DATABASE buptest SET RECOVERY SIMPLE

I have put the database into simple recovery mode just to keep the log manageable, I am not worried about point in time recovery for this demo.

Next we will create two tables to hold some test data

USE buptest
go

CREATE TABLE t1 (id INT, val CHAR(8000))
go

CREATE TABLE t2 (id INT, val CHAR(8000))
go

Next we will populate our table t1 with some data, 100,000 rows. I’m doing this so the backup will run for long enough to allow me to execute some transactions against the database whilst the backup is running. I don’t know of another way of doing this…If you do, please leave me a note in the comments.

DECLARE @i INT
SET @i = 0
WHILE @i < 100000
BEGIN 

INSERT INTO t1
VALUES (@i, REPLICATE('a',8000))
SET @i=@i+1
END 

The next step has two parts, I want to kick off a full backup in one query window and then in a second query window I want to run two transactions that each insert a row into the t2 table. The first transaction will be committed before the backup completes the second transaction will be committed after backup has finished. So I suggest if you are following along, you open two query windows, paste the backup code below into the first window, paste the insert transaction into the second window (You will see that the second commit statement in commented out), switch to the first window, start the backup, quickly switch to the second window, fire the script making sure that the commit statement is commented out. Wait for the backup to complete. When the backup has completed uncommented the commit transaction statement and commit the currently open transaction.

The script to run the backup

--We then take a backup of the database
--While this is running fire the second script below
BACKUP DATABASE buptest TO DISK = 'c:\Backup\buptest.bak'

While the backup script above is running (it took about a minute on my laptop to complete) run this:

USE buptest
GO

--Run in a new query window
BEGIN TRANSACTION
INSERT INTO t2 
VALUES (1, 'gethyn')
COMMIT TRANSACTION


BEGIN TRANSACTION
INSERT INTO t2 
VALUES (2,'Ian')

When the backup completes run the final commit transaction in the second window. This ensures that the second transaction was not committed prior to the backup completing.

COMMIT TRANSACTION

If you run

SELECT * FROM t2

We get two rows returned.

1	Gethyn
2	Ian

Next we run a restore of our database from the backup taken

USE master
go
ALTER DATABASE buptest SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE buptest FROM DISK = 'c:\Backup\buptest.bak' WITH replace

Then re-run the same select

SELECT * FROM t2

We see that only the first inserted record is present.

1

Gethyn

Hence only committed transaction are included in a full database backup

In order to be able to execute bulk operations you need to certain level of privilege both on the database (table) you are performing the operation against and the instance you are performing the operation on.
If you don't have the necessary permissions you will get an error similar to the following:
Msg 4834, Level 16, State 4, Line 5
You do not have permission to use the bulk load statement.
This is what one of my users got earlier today when trying to perform a BULK INSERT. To be able to do this successfully they will need the ability (permission) to INSERT records into the table in question.
In our case the following script will do the trick:

USE orderdemo
go
GRANT INSERT ON dbo.orders_BULK  TO bulktest
go

They will also need Administer Bulk Operations permission, you can give that permission by executing the following, this is a server level permission:

USE master
go 
GRANT ADMINISTER BULK OPERATIONS TO  bulktest

The user will need ALTER table permissions in certain circumstances.


This information is available in BOL but I came across the need to give this permission today and I thought I would share it.