Tuesday, 25 May 2010

Time Off, Recharge, Rethink

Some of you may have noticed that I haven’t posted for a while. The 12th May was the date of my last post. Well, in the last few weeks I have been on holiday. Me and the family took a trip to the USA and the Sunshine state to celebrate my birthday, as I recently turned 30.

We spent time in the theme parks in Orlando riding rollercoaster's and generally having fun, and we took a trip out to Tampa. The weather has been very hot and very sunny and we have all had a great time. We did intend to visit the beach but with the current oil disaster affecting the region we decided to give the beach a miss… which is a shame, I have been before and it is a beautiful place and I hope that the disaster does not have too much of an impact on the region.

My holidays and time off never fail to recharge my batteries and I always return home with fresh ideas and renewed motivation to develop my business and career.

I have few new business ideas that I will share with you shortly. On a career note I will be taking the upgrade exam for the MCITP SQL Server 2008 in the near future. I guess certification is whole debate on its own, before I left a few of the blogs a I follow were discussing the possibility of PASS offering a middle ground certification between MCITP and the MCM. Over the last few weeks I have lost touch with developments here but will pick up properly when I get home.

I also have a few more ideas for blog posts and articles and I will get them written up as soon as I can.

I have scheduled this post to go-live on Tuesday morning at 0705 UK which is the time my flight is scheduled to land at London Gatwick Airport. I then have a drive home and I know my wife (and I definitely will) will want to go and pick up our two golden retrievers, we have both missed them.

Wednesday, 12 May 2010

Are Transaction Rolled Back When CHECKDB Runs?

Ever come across a scary error in the SQL Server log when running CHECKDB against your databases. The error looks something like this:

1 transactions rolled back in database 'CHECKDB' (32). This is an informational message only. No user action is required.


Are transactions rolled back when CHECKDB runs?



No.



The key to this error is the last two sentences, Crash recovery has not been run on your production database. Since SQL Server 2005 an internal snapshot of the database is used for running integrity checks, this snapshot provides CHECKDB with a transitionally consistent snapshot of your your database. This error relates to the snapshot, crash recovery is run against the database snapshot so it is in a transactional consistent state for CHECKDB to run. Your production database is not affected.



Don’t believe me, well I don’t blame you, so checkout Paul Randal(Blog|Twitter) post(s) on the very subject.



Related Posts



SQL Server Integrity Checks

Wednesday, 5 May 2010

SQL Server Integrity Checks – DBCC CHECKDB

I like to run integrity checks against my databases very regularly, sometimes daily if I can get away with it. For some of the larger databases that I manage I change how this is run but on my consolidated instances which can house several different smaller databases I run the following script in a job and schedule the job to run daily, out of hours, using the SQL Server agent. My script looks something like this:

--Gethyn Ellis  April 2009
--runs DBCC checkdb against ALL databases on a server
--Needs to be run against master database i.e. that is where sp_MSForEachdb is found
--To exclude a database add the database name to the NOT IN list in @cmd1
DECLARE @cmd1 VARCHAR(500)


SET @cmd1 = 'if ''?'' NOT IN (''tempdb'') DBCC CHECKDB([?]) WITH NO_INFOMSGS'
EXEC sp_MSforeachdb @command1 = @cmd1



Tuesday, 4 May 2010

SQL Server Replication - Replication Agents

My first post in my replication series gave a brief overview of replication, before I dive into setting up and configuring the different types of replication I wanted to look a little more closely at the respective agents and the role they play in the different types of replication.

Replication Agents

Snapshot Agent - usually resides on the distributor server, it is used in all types of replication. The Snapshot Agent makes a copy of the schema objects and the data in the tables to published and stores them in  snapshot files in the snapshot folder, information about the snapshot  is stored in the distribution database.
Log Reader Agent - This is used in transactional replication. The log reader agent monitors the transaction log of all databases involved in replication. it monitors changes in the data marked for replication in the publication database's  transaction log and sends them to the distributor server. The transactions are held here until they are are ready to be sent to the subscribers.
Queue Reader Agent - is used with transactional replication and updateable subscribers publication type... The agent is run at the distributor and moves changes made at the subscriber back to the publisher.
Distribution Agent - is used in both snapshot and transactional replication. Its role is to move the snapshot and transactions stored at the distributor to the subscribing servers. In pull replication this agent lives on the subscribers and in push scenario it resides on the distributor server.
Merge Agent - surprisingly is used in Merge Replication. Unless you are using MERGE replication you will not be utilising this agent. This agent applies the initial snapshot to the subscriber servers. changes made after the initial synchronisation are monitored and merged to the subscribers ...This is the agent that is responsible for resolving update conflicts.


So these are the replication agents I will try and point out the how they work with each type of replication when we look at setting up and configuring replication.