Thursday, April 29, 2010

Migrating SQL Jobs

About once a year I am faced with the fact to migrate one of our SQL Servers to a better, bigger, newer server. Usually it's not a big deal because for the most part it can be done by backing up and restoring the database. How about migrating the SQL Jobs though? Sure, that can be done as well by backing up and restoring the MSDN database but if you are also upgrading SQL Server that may not be a wise idea.

There are a few ways this can be done. SSIS has a Transfer Jobs Task but I ran into a problem with that as most of the jobs had the server specified like this: @server=N'SERVERNAME' so the task was not able to transfer those jobs. Individually scripting out SQL Jobs is possible of course if you only have a handful. In my case I had 87 SQL Jobs and frankly, I am too lazy for that much work. So I found a shortcut.

Click Jobs in Management Studio and then click F7. That will pull up Object Browser with the list of all the SQL Jobs. Highlight all and then right click and select Script Job As>Create To>New Query Window. That will script out all your jobs. Once you have them scripted out you can just a search and replace for server names or other variables you need to change. Then you change the connection to your new SQL Server, hit Execute and you are done!

In my case I was almost done. I also had some Maintenance Jobs to transfer as well. Those are basically SSIS packages kept on SQL Server as opposed to in the File System. The solution is to export them to the file system, change the connection and then import them into the new SQL Server. Here are the steps to accomplish that.

  1. Connect to your server's Integration Services in SSMS. You can do that by hit Connect>Integration Services. Once you are connected you will find the maintenance plan packages under Stored Packages>MSDB>Maintenance Plans.
  2. Export all packages. You can do that by right clicking each package then selecting Export Package. On the dialog box that box up you need to select File System in the Package location section. Then navigate to the location where you want to save your package. I left the default in the Protection level section.
  3. Open the packages in notepad and change the connection information. Just search for the name of the server and replace it with the name of the new server. This step is necessary because you are unable to edit the Local Server Connection in the package editor. You may also change other things as well such as backup locations and such. Save the file.
  4. Import the package. Connect to your new servers Integration Services. Navigate down to the location where the Maintenance Plans are located which should be the same as in the source server (see step 1.) Right click Maintenance Plans and select Import package. Select File System in the Package location then in the Package path using the ellipsis button navigate to the location where you exported your packages to. The Package name will be automatically filled in although you could change it.
  5.  If by accident you skipped step 3, don't fret, you can still do it. You will have to repeat step 4 again and this time you will be overwriting your existing package.
  6. You probably want to double-check on each package to make sure there are no other adjustments need to be done. Connect to the new SQL Server's database engine this time and right-click Modify on the maintenance plans. (Management>Maintenance Plans)
That's all there is to it. I hope I saved someone time with this post.

Monday, April 19, 2010

Timers in Windows Services

Occasionally I have to create a windows service. In most windows services I need to create a timer to execute a certain method periodically. Since I don't work with windows services often enough I always get stumped at the best method to create the timer. I know that by dragging the Timer object from the Toolbox adds the timer from the Windows Forms namespace. That timer has a Tick even which I haven't been able to make work in a Windows Service project. So if you create a windows service in Visual Studio 2008 and you need a timer this is the code you need to add to your class for the service:

Private WithEvents Timer1 As New System.Timers.Timer()

Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
    'Some code
End Sub

Afterwards you can set the elapse time of the timer and enable it in your service start event:

Protected Overrides Sub OnStart(ByVal args() As String)

    Timer1.Interval = 60000 '60000 = 1 minute

    Timer1.Enabled = True

End Sub

Happy coding!