New Year, New Opportunities

Like many people I have taken the steps of acquiring a new job during the New Year merry go round. After a brief search I have been offered and accepted a position as a SQL DBA at a well known UK retailer.

There have been lots of posts on forums and mailing lists asking for hints and tips on interviews and sample questions, whilst I think it is unfair to list exact questions I would expect anyone going for a SQL job to have a thorough grounding in the following areas at the very minimum:

  • The structure of a database.
  • Backing up a database, recovery models and backup types.
  • An understanding of the different indexes available.
  • Detail the steps of a maintenance plan.
  • Thorough understanding of T-SQL querying commands.

Other roles such as a BI Analyst or SQL Developer may be more specialised and so could include the following:

  • SSIS/DTS
  • SSAS
  • SSRS

If you are looking for a new job at the moment either through choice or as a victim of the recession then it would be worth having professional help with your CV. Since handing in my notice I have seen first hand at how people look at resume’s whilst managers have been looking for my replacement and from the comments I have heard basic mistakes will put potential employers off. There are a number of companies offering free advice just a click away with your favourite search engine.

Happy New Year 2010

I’d like to wish you all a happy new year in which we can all look forward to the release on SQL 2008 R2 and Powerpoint!

Merry Christmas 2009

I’d like to take this opportunity to wish you and your families a very Merry Christmas!

Installing Anti Virus on a SQL Server

It’s been quite a while since I last posted anything on this site, there’s been a lot of change behind the scenes and have a number of entries to post based on things that have happened in the last few weeks.

Earlier this week I was asked by one of our infrastructure guys when would be a good time to take down one of the servers and push out some new anti virus software using SMS (Server Management System) we arranged a suitable time where the no users would be affected but the real key issue as a DBA is to make sure that the anti virus software does not adversely affect performance.

There are differing schools of thought on whether anti virus should be applied or not because of performance reasons. If you are thinking about adding an AV solution to your SQL Servers or have already done so then please check the Microsoft KB article http://support.microsoft.com/kb/309422 to see what Microsoft recommends on this contentious issue.

Leeds SQL User Group

Join Anthony Brown and Martin Bell tomorrow for an evening on Service Broker and an introduction to SQL’s version of PowerShell. There will be a prize draw for a copy of Windows 7 Ultimate during the evening as well as other goodies.

You will need to register for this event, to do so click on the following link.

Finding Files in ActiveX

This is the second entry in the series on how you can check for the existence of files in SQL Server, in part 1 Finding files in T-SQL we covered how the undocumented sp xp_FileExists could be used.

In this entry I will be introducing an overview of the FileSystemObject which we will be using primarily to check for the existence of files.

In the first example we shall create a job called “TEST – Find Files” with 4 steps which will do the following:

  1. Create a file C:\Test.txt
    In this step we use CreateTextFile method to create our test file.
  2. Check for the existence of the file C:\ThisIsntWhatICreated.txt
    In this step we use the FileExists method to check for our test file.
  3. Delete the file C:\Test.txt
    In this step we use the DeleteFile method to delete our test file.
  4. Create a Failed step for notification purposes.
    This step is just to show that another step can be run if the file does not exist.

The code for this job can be found below:

-- Script generated by Richard Douglas 

BEGIN TRANSACTION

  DECLARE @JobID BINARY(16)

  DECLARE @ReturnCode INT

  SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Test Job]') < 1

  EXECUTE msdb.dbo.sp_add_category @name = N'[Test Job]'

  -- Delete the job with the same name (if it exists)

  SELECT @JobID = job_id

  FROM   msdb.dbo.sysjobs

  WHERE (name = N'TEST - Find File')

  IF (@JobID IS NOT NULL)

  BEGIN

  -- Check if the job is a multi-server job  

  IF (EXISTS (SELECT  *

              FROM    msdb.dbo.sysjobservers

              WHERE   (job_id = @JobID) AND (server_id <> 0)))

  BEGIN

    -- There is, so abort the script 

    RAISERROR (N'Unable to import job ''TEST - Find File'' since there is already a multi-server job with this name.', 16, 1)

    GOTO QuitWithRollback

  END

  ELSE

    -- Delete the [local] job 

    EXECUTE msdb.dbo.sp_delete_job @job_name = N'TEST - Find File'

    SELECT @JobID = NULL

  END

BEGIN

  -- Add the job

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'TEST - Find File', @owner_login_name = suser_sname(), @description = N'No description available.', @category_name = N'[Test Job]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job steps

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Create File', @command = N'Function Main(), @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2

  FileName = "C:\Test.txt"

  Dim fso, f1

  Set fso = CreateObject("Scripting.FileSystemObject")

  Set f1 = fso.CreateTextFile(FileName, True)

  Set fso = Nothing

  Set f1 = Nothing

End Function'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Find File', @command = N'' Clean Up, @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 4, @on_fail_action = 4

Function Main()

      Dim FSO

      Dim File

      Dim SourceFile

      Set FSO = CreateObject("Scripting.FileSystemObject")

      FileName = "C:\ThisIsntWhatICreated.txt"

      If NOT FSO.FileExists(FileName) Then

             Call Err.Raise(60001, "File Not found")

      End If

      '

      Set File = Nothing

      Set FSO = Nothing

End Function'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Delete File', @command = N'Function Main()' Check if file exists to prevent error' Clean Up, @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

      Dim oFSO

      Dim sSourceFile

      Set oFSO = CreateObject("Scripting.FileSystemObject")

      sSourceFile = "C:\Test.txt"

      '

      If oFSO.FileExists(sSourceFile) Then

            oFSO.DeleteFile sSourceFile

      End If

      '

      Set oFSO = Nothing

End Function

'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Job Failed', @command = N'select 1', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 2, @on_fail_step_id = 0, @on_fail_action = 2

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the Target Servers

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

GOTO   EndSave

QuitWithRollback:

  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

 
The first time you run this job it should fail (unless of course you have the file C:\ThisIsntWhatICreated.txt) having run steps 1,2 and 4. If you change the second step to now look for the file C:\Test.txt for job should succeed in running the first three steps.

This has been a very simple overview of a few of the methods of the FileSystemObject, there are many more things you can do for example you may only want to proceed if the file is a certain size or of a certain date, with the FileSystemObject you can do all this and more. In fact I’ll show you how you can check the file size as well, this time we’ll use DTS (I realise it’s now two versions out of date but from the people I have spoken to lots of companies still use SQL 2000, even SQL 7 and still develop in DTS).

You can download the package here.

If you don’t want to download the package then the code below is the crux of it:

'**********************************************************************

'  Visual Basic ActiveX Script

'************************************************************************

 

Function Main()

 

    Dim FSO

    Dim File

    Dim SourceFile

 

    Set FSO = CreateObject("Scripting.FileSystemObject")

 

    FileName = DTSGlobalVariables("FileName").Value

 

    If FSO.FileExists(FileName) Then

        Set File = FSO.GetFile(FileName)

 

        If File.Size = 0 Then

            Main = DTSTaskExecResult_Success

        Else

            Main = DTSTaskExecResult_Failure

        End If

    Else

        Main = DTSTaskExecResult_Failure

    End If

 

    ' Clean Up

    Set File = Nothing

    Set FSO = Nothing

End Function

As you can see it’s pretty similar to the ActiveX we used in step two of the job with a couple of differences. The first being that we are using a global variable for the filename, this is so we could use it in a loop, read it from a table or any number of other things.

The second is that we are now also using the GetFile method that allows us to check the properties of the file itself. In this scenario we are looking at the size and saying if the file size is 0 then succeed, else fail.

To find out more about how to use the FileSystemObject you can refer to the MSDN documentation here. I hope you have found this an interesting introduction to the FileSystemObject and thank you for reading.

SQLBits review

It’s now been a week since SQLBits V started in Newport, Wales, so it’s about time I wrote a little on it.

First of all I have to say a big thank you to all the organisers, speakers and sponsors without whom the event would not have been possible. The facilities at the conference centre was absolutely fantastic and the staff were really friendly. You can see why it was picked to host the 2010 Ryder Cup competition. The only criticism I would have over the setup was that the tiered seating at Manchester University made a massive difference, especially in the busier sessions.

For me SQLBits V started Thursday morning at 6am with a drive down to Newport to help on the registration desk at 8:30. Originally I had volunteered to help out on the Saturday but after an email from Chris Webb asking to help on the Thursday I set my alarm a little earlier to help out. If these guys can put their lives on hold to organise such a wonderful event it’s the least I could do.

After what seemed like a never ending queue of people wanting name tags and goody bags it was time to sit down to an all day session by Donald Farmer on PowerPivot (project Gemini). Donald was in scintillating form the quote of the day had to  be regarding storage: “If it’s on-site the it’s on premises, if it’s on a cloud it’s on promises” classic stuff. 

As a brief overview the following topics were covered:

  • Self Service Reporting
  • Master Data Services/Management
  • Introduction to PowerPivot
  • Data Analysis Expressions (DAX)

If you are interested in PowerPivot it is due for release in the 1st half of 2010, possibly in March/April it does however have a dependency on Office 2010 being released first so may be delayed. Donald provided a number of links where you can find further information about the product.

http://powerpivotpro.com
http://powerpivotgeek.com

I was unable to attend the sessions on Friday due to a lack of cover at work, but I was back on the Saturday. Like Thursday I arrived early to help out on the registration desk where I turned to my left at one point and was astonished to find Darren Green of SQLDTS.com fame standing right next to me and a little later James Rowland-Jones came to the desk with a box, unfortunately it contained a replica retro Arsenal shirt. I’m now in two minds whether to buy his next book ;)

As the room monitor for room 4 I was unable to pick and choose the sessions to attend, but this didn’t matter as I was treated to five great sessions in the SQL Server 2008/R2 category

  • Put Your Feet Up : Simplified Management using the Enterprise Policy Management Framework – Martin Cairney
  • Introduction to the Microsoft BI Technology Stack – Dr John Tunnicliffe
  • Using Perfmon and Profiler – Brent Ozar
  • An introduction to Master Data Services – Ian Marritt
  • SQL Server optimization stuff you won’t find on Google (yet) – Andre Kamman

My favourite sessions were those by Brent Ozar and Andre Kamman there seems to be a distinct difference in the presentations done by those over the water compared to the British presenters. Brent’s performance was very polished and you could tell that the talk had been done many times before. Andre and Henk’s presentation was very laid back especially when you consider that they had to almost wing their presentation due to laptop problems.

I highly recommend SQLBits to anyone who has anything to do with SQL Server be it the casual system administrator with one instance hosting a third party database through to senior DBA’s who manage hundreds of instances. The best thing is that due to sponsorship the Saturday sessions are absolutely free, you even recieve a free goody bag and refreshments throughout the day.

 Massive thanks once again to all the organisers they did a fantastic job, I’m now looking forward eagerly to SQLBits VI.

SQLBits V session agenda now online

For those of you that have registered the agenda’s for SQLBits V is now available online.

http://www.sqlbits.com/information/newagenda.aspx

Hope to see a few of you there!

SSMS scripting editor

Thanks go to Tony Rogerson and Justin Langford for a great evening at Reading last night. It was the first time I had been to the Microsoft Campus so was a bit in awe of everything. Justin and Tony both gave great sessions, but the tip I am going to talk about was almost a throwaway comment by Tony. During his session on partitioned tables he suddenly perrformed some kind of sourcery that split the scripting window in SSMS into two editable windows. I knew this could be done in Excel but must admit I had never seen the bar that allows you to do this in SSMS.

If you’re not entirely sure what I’m talking about then hopefully the next two screen shots will be able to help make things clearer.

Page split

I have circled the bar that allows you to create the split window by simply pulling it down . In the next screenshot you will see how this can be used.

 

 

 

 

 

In this screenshot I ran a simple query to return all table names in the form of a select statement per table and pasted the results into the query window to fill it up nicely.

PageSplitInUSe

It is worth noting that both windows are completely editable and if you type in one window it will replicate in real time in the second window as it is just a virtual split on the same object. This morning I noticed that the same bar is also available inside the scripting editor in Visual studio should you use that editor instead of SSMS.

Everyone I have spoken to about this at work today either said “wow” or “cool”. I guess it’s a case of small things please small minds.

User Group Reminder: Reading

There is a SQL user group meeting in Reading tomorrow night hosted by MVP Tony Rogerson and Justin Langford. Tony will be covering “An Introduction to the SQL Server architecture and also High Availability (Partitioning and File Groups (backup, restore, partial restore))” while Justin will be resenting a session on Virtualising SQL Server.

Click here to go to the site directly or click here to view a list of upcoming SQL events across various affiliations.