SQL Agent Won’t Start After Switching the Service Account

My problem started shortly after I switched the service account that powered the SQL Services. On the initial install I used my domain login during the install. Then I received the good old change password nag and changed my password three or four days later. Life went on just peachy until I had to reboot. So in order to avoid this again I decided to use a different domain account. Using SQL Server Configuration Manager I changed all the services to use the new domain account and began the process of starting each service leaving the agent as the last. To my surprise I received a nice little error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

At this point I thought a reboot might resolve this which it didn’t. So I decided to listen to the error message and search through the error logs. In the Windows Event Viewer I found the following:

Login failed for user ‘xxxxx\sqlservice’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

After reading this I thought the SQL permissions were incorrect so I double checked those and they turned up fine. So I proceeded on. This time to the SQL error logs in the instance directory. In my case “C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV08R2\MSSQL\Log”.

I opened the ERRORLOG with notepad and began reviewing the contents and I found some interesting information. Essentially it stated:

Error: 18456, Severity: 14, State: 38.
Login failed for user ‘XXXXX\sqlservice’

Now I am thinking perhaps I mistyped the password which seemed like a viable action seeing the rest of the services were working fine. So I visit the Configuration Manager again and attempt to redo the password. For Schnitz and Giggles I decided to deliberately enter in the wrong password and surprisingly I received a nice error:

The specified network password is not correct.

Now I am puzzled. The password was right all along yet the previous error is saying that the login failed. WTF? To rule out if this is a Windows permissions or SQL Server permissions issue I added the service account to the local Administrators group and suddenly I was able to start the service. At the very least I now know that this is a Windows permission issue. Now I just need to figure out where this permissions issue resides.

I started to review the accounts and remembered during the installation that SQL Server creates Windows groups. So I hone in on the SQLServerMSSQLUser$XXXXXX$DEV08R2 group and find that the service account is not listed. So I add the service account to this group and kick off the Agent service and boom… it works! I tried changing the service account on a SQL Server 2005 instance and I noticed that the Windows group was updated so I am not sure why SQL Server 2008 R2 didn’t behave the same way.

The actual permissions are required within the instance directory. Here is where I found it.

And the required permissions are as illustrated.

Side Note

Once you switch the service account make sure you take a look at the owner for the databases and jobs. In my case I wanted all the databases and jobs to have the owner of the sqlservice so I ran the following query to address the database owner.

SELECT 
	name
	,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Then I copied the contents from the ‘cmd’ column and executed them to make the change.


Note: you cannot change the owner of the master, model, msdb and tempdb databases. If you attempt to change the owner of the system databases you will be greeted with an error.

To circumvent this you can add an additional condition to the WHERE clause as followed and this will skip the system databases.

SELECT 
	name
	,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)
  AND (database_id NOT IN (1,2,3,4))

The stored procedure sp_changedbowner will be removed in a future version of SQL Server. Which means you need to learn how to use ALTER AUTHORIZATION. Which I picked up from Jes’s article Changing a SQL Server Database. Here is the revised script you would use to generate the ALTER AUTHORIZATION statement.

SELECT 
	name
	,SUSER_SNAME(owner_sid) 'owner'
	,'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO [sa]' 'cmd'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Lastly I ran the following query to generate the ‘cmd’ statement which changed the job owner for all the jobs I had set up. Keep in mind that if you have any backup jobs that are backing up to a network share you will need to update the share permissions and add the service account to permit writing to that location.

SELECT
	name
	,'EXEC msdb..sp_update_job @job_name = ''' + name + ''', ''@owner_login_name = xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM msdb..sysjobs	
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Goodbye Stored Procedures… Hello Maintenance Plans

I like to write my our procedures for the sheer fact that I like the control. Which means I had sprocs that cleared out the history logs retaining only the records within the last 90 days. I also had sprocs that performed system and user database backups which would backup to a network share and append my timestamp (i.e. master_YYYYMMDD_HHMM.bak) which were all executed by agent jobs. Depending on the need I would setup Full, differential and log backups; furthermore, my process would also purge old back up files.

SELECT
'_' +
CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS VARCHAR)
+ '_' +
CAST(REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') AS VARCHAR)
+ '.bak'

I am here to say I have given up my ways and have been walking a different path. I moved away from my custom methods and adopted Maintenance Plans. I use them for backing up the system and user databases if the system is not being serviced by a backup system as well as running DBCC CHECKDB against each database. I also use maintenance plans to purge the history logs that falls outside of a 90 day threshold and perform reorgs or rebuilds of my indexes. For a complete task list visit: Maintenance Tasks.

I am kidding, it’s hard to break away from writing my own procedures. Maintenance Plans are good if you’re getting started with SQL Server and really don’t understand the fundamentals of writing your own queries to accomplish the same outcome. I don’t know if this makes me a control freak or not. Oh wait I said that in the beginning of this post. Anyhow if you want to setup maintenance plans here is what you need to do.

One thing to know before we jump in is that you need to grant the SQL Agent Service account write access to the backup share in my case the Agent service is “sqlservice”. Typically you would use a domain account (i.e. domain\sqlservice) which you will need to grant Change access on the directory where you will be storing the backups (i.e. \\server\backups\).

If you are using the default file path which was set during the initial install of SQL Server then the path would be like <drive letter>:\..\MSSQL10_50.<instance name>\MSSQL\Backup\ and will be using the SQLServerMSSQLUser$<computer name>$<instance name> which has enough permissions.

Also you need know Maintenance Plans and the SQL Agent are not features you will find in Express editions. Well technically the SQL Agent is there, but it cannot be used/started in Express edition, so hopefully you have at the very least developer edition.

Now that I touched on a few reasons on why to use Maintenance Plans let’s kick our heels up and dig right in. You’ll need to be a sysadmin in order to create a maint plan so if you’re not then the following steps will be more informational than anything. If you have worked with SQL Server Integration Services then the Maintenance Plans design surface will look a bit familiar.

For this post we will be using two tasks: Backup Database and Maintenance Cleanup to schedule routine backups of the system database: (master, model and msdb). Once you run through this process creating another Maintenance Plan to address the user databases is extremely similar with literally one item to change. You’ll see what I am talking about in a moment. Let’s being…

Creating a Maintenance Plan for daily backups of System Databases

Step 1: Open up Management Studio, connect to your instance and expand Management
Step 2: Right click on Maintenance Plans and select New Maintenance Plan…
Step 3: Enter a specific name: (i.e. Daily_System_DB_Full_Backup) & click OK

If you are going to schedule a FULL backup let’s say on Sunday then the name of the Maintenance Plan would be along the lines of (i.e. Weekly_System_DB_Full_Backup). Then if you were going to incorporate daily differentials you might consider calling the Maintenance Plan: (i.e. Daily_System_DB_Diff_Backup). Perhaps you want to perform Full backups of the DBs daily and then perform Transaction Log backups hourly or every four hours, etc… you would want to make the names meaningful to the types of backups the Maintenance Plan is performing. Enough harping on that… I am beginning to hear crickets at his point.

Once you have clicked OK you arrive at the design surface where you will build your workflow process. The default is always Subplan_1 which you can leave or you can change the name. If your Maintenance Plan only utilizes a single subplan then the naming really doesn’t matter, but if you add additional subplans then you might want to consider naming them accordingly to better identify their purpose or intent as opposed to having Subplan_2, 3, etc… you get my drift.

To rename the subplan just double-click on the Subplan name.

Rename the Subplan and specify a description and simply click OK

Since I am going to use only one subplan for this post I am going to leave that name as Subplan_1.

Step 4: Drag the Back Up Database Task to the designer surface area

Now in the Toolbox pane to the left select and drag Back Up Database Task from within the Maintenance Plans Tasks to the designer surface area and release.

Step 5: Edit the task

Right click on the Back Up Database Task and select Edit…

Step 6: Perform a quick hat trick (hockey reference)

A) Click the Database(s) select list
B) Select System databases
C) Click OK

Step 7: Specify the backup path in the Folder: field and click OK

If the Folder: is already populated using a local file system path then you can leave it as is. It will work as is. I am using a network share just as an example to illustrate that you would normally want to backup to a network share which should be routinely backed up via an enterprise backup system.

Step 8: Drag the Maintenance Cleanup Task to the designer surface area and release. The Maintenance Cleanup Task will purge all files with a specific file extension using a given date specification.

Step 9: Set the workflow

A) Click the Back Up Database Task
B) Drag the Green Arrow to the Maintenance Cleanup Task and release

This means when the backup task completes successfully to proceed and execute the Maintenance Cleanup Task.

Step 10: Edit the Maintenance Cleanup Task

This is similar to Step 5 except you are right clicking on Maintenance Cleanup Task instead and selcting Edit…

Step 11: Four point play…

A) Specify the backup path in the Folder: field
B) Specify the extension bak in the File extention field
C) Specify a value unit of time in numbers
D) Specify a value unit of time for the time frame then click OK

Step 12: Scheduling the Maintenance Plan

Click the Calendar Icon just above the Maintenance Plan name

Step 13: Secondary hat trick…

A) I like to remove the .Subplan_1 from the Maintenance Plan name (optional)
B) Click the occurs: select list and choose Daily
C) Set the field occurs once at: to when you want this to kick off and click OK

Step 14: Close & save the Maintenance Plan

Click Yes to confirm

Success!

Done! Well at least with creating the Maint Plan. We just need to test it.

Step 15: Start the newly created SQL Job

Right click on Daily_System_Database_Full_Backup.Subplan_1 and select Start Job at Step…

If the process fails make sure your service account has Change permissions on the network share. If you are running the SQL Agent Service under the Network Service account then set the permissions as illustrated. This also applies to what ever account you are running the Agent Service under like a domain account and such.

Otherwise if everything is configured correctly you will see GREEN!

And the databases will have been backed up as such…

Amended 2011-05-10


You should read Brad McGehee’s eBook: Brad’s Sure Guide to SQL Server Maintenance Plans to get a better understanding of some of the gotchas you need to be aware about in regards to Maintenance Plans.

Update to SQL Server 2008 R2 CU6

I got my work laptop environment finally squared away and today was the first attempt to update my instances of SQL Server 2008 R2 Developer and Express to CU3. Yes I know the post title say CU6, trust me I’ll get to that in a moment. Originally I was going to update to CU3 reason being because it addressed an issue I was experiencing with creating, adding or editing SQL Agent jobs in SSMS. Matter of fact here is the Microsoft article address: http://support.microsoft.com/kb/2315727

Here is the specific error I received when I attempted to edit an agent job.

Related Connect feedback

So I downloaded CU3 from Microsoft.

When I tried to apply CU3 I encountered an error which basically suggested that I had to uninstall SQL Native Client because it was not installed via sqlcnli.msi. Here is the url that the error pointed me to.

Source: setup.rll ID: 50000 (SQL Server 10.50.1600.1) – Events And Errors Message Center: Message Details

Needless to say I decided to reboot hoping that would some how, some way correct the issue auto-magically. WRONG! So I decided (executive decision) to push forward and apply CU6. See I told you I would eventually get to it! Well I downloaded CU6 and attempted to install and ran into another error.

Here is the full Summary.txt

Well again I tried a reboot and that did not work, so I surfed around and then stumbled on a post by Jonathan Kehayias (@SQLSarg | blog) titled: SQL Server 2008 failed installation blocks Service Pack 1 (SP1) installation. He essentially performed a repair which resolved his issue so I felt inclined to do the same and BOOM… CU6 applied oh so nicely. I cannot say if this will help you, but it helped me. Both my Dev and Express editions are at CU6 so I am a happy camper.

Think and review before diving in head first

On a recent project assignment I was tasked to comb through a client database and target a specific field that resided within a single table. This field was powered by a free form text field on a .NET web application. Instead of separating the required bits of data into their own fields respectively everything was lumped into one NVARCHAR field. Definitely not an ideal situation. Nonetheless the task needed to be done. I spoke with the our Business Analyst and obtained the requirements.

So I fired up SSMS 2008R2 and started to formulate the query. That was my first mistake! I regressed back to my rookie days without really thinking it through and instead of really researching in-depth I started to ask for assistance (twitter hash tag: #sqlhelp to the rescue). Don’t get me wrong… asking for help isn’t terrible, it’s just that thinking it trough first really makes a significant difference. To make a long story short let me tell you what steps I took to make this move along smoother.

Step 1: Always review the data first

If I would have carefully inspected the data first I would have noticed the different ways the end users were entering the data. Then I could have easily constructed special conditions to target those specific instances. For example here is a short list of what the data kind of looked like:

  • Select on plan/Spec 283
  • Side Menu/Spec 118 Sht 30
  • Side Menu/Spec 474 Sht C1
  • Select on plan/Spec 283 Sheet 17
  • Select on Plan/Spec 278 Sht 44 Dtl A
  • Select on Plan/Spec 389 Sht 4 Bypass Piping
  • Select on Plan/Spec 408.1 Sht M-2
  • Site Piping Menu/Spec 408.1 Sht C-1 Plan
  • Surge Tank Menu/Spec 408.1 Sht M-4 Dtl B
  • Select on plan/Sheet 8 Detail C
  • Select from Side Menu/Sheet 7 Detail C
  • Menu from Discharge Relief Valve/Sheet 8 Detail F
  • Menu for Valve/Sheet 8 Detail A
  • Select on plan/Sheet 12 Detail C
  • Menu for Pressure Transmitter Cabinet/Sheet 20 Detail B
  • Menu for Suction Surge Tank 2/Sheet 15 Detail V

My task was to find the instance of SPEC, SHEET and SHT and grab the following numbers or char. As you can see there is only one instance of each one so that made it a bit easier.

Step 2: Identify the patterns

I took each keyword and began analyzing the data carefully:

SPEC
For SPEC the following three were always numeric (e.g. Spec 283) and there were a small handful the had three numeric followed by a period and another numeric digit (e.g Spec 408.1) so my options are grab the following four char or six to the right.

SHEET
For SHEET it was pretty easy. Always trailing SHEET was a numeric digit but the only difference was either a single digit (e.g. Sheet 2) or double digits (e.g. Sheet 12). So that means I can grab the following three char to the right and trim the left and right to satisfy this requirement.

SHT
The SHT on the other hand was a bit more difficult since it varied the most. This either had a trailing single numeric digit (e.g. Sht 2), double numeric digit (e.g. Sht 13) or mixed with alpha-numeric chars like: (e.g. Sht M-1 or Sht P8 or Sht P20).

Now that I can clearly see what patterns to look for I can begin to construct my query. Nothing like a little Function action using CHARINDEX mixed with a little dose of PATINDEX to assist in situations likes this. Of course there are many ways to approach this, but this worked for me and satisfied the requirements.

Step 3: Approach each requirement one at a time

This permitted me to concentrate on a specific goal until I was able to correctly return the desired output. Naturally I felt that attacking the easiest ones first would be best, but after reading the book, “Eat that Frog!” I learned to eat the biggest and ugliest frog first as it provides the greatest sense of accomplishment. So I started swinging at SHT. Then once that was crystal clear I moved onto SPEC and SHEET. I must mention that a fellow SQL buff by the name of Aaron Bertrand [ Twitter | Blog ] actually provided a very cool script that tackled the SPEC and covered all the basis. So Cheers to him!

Step 4: Piece everything together and test again

Once everything is working accordingly piece everything together and test it again. I cannot emphasize this enough. Q/A is necessary and must be done until everything comes out the way we need it to. Data is critical to any organization and we as data professionals know it better than anyone else. So test, test and retest. Everything worked out as needed and the results are positive.

Off the subject
If I didn’t mention this before #sqlhelp is extremely useful and there are many people that are extremely talented that are willing to lend you a helping hand. It’s a community thing and I love it! If twitter ain’t your thing that’s fine no biggie; however, if you are looking into it… do it! You won’t be sorry.

Calculate Subscription Expiration Threshold

While I was piecing together a few queries for a report intended to deliver subscription information about subscribers that are nearing the expiration threshold for replication I found myself needing to calculate warning threshold levels. In one case I found not only do I need to calculate the threshold warning criteria but I also needed to round the outcome to the nearest whole integer.

As you probably know the default retention period for replication defaults to 14 days; however, in replication monitor you can set up warnings to indicate if a subscriber is nearing the threshold. Typically this is 80% so in short I am going to take the retention value and multiple it by .8 and round to the nearest whole integer. For this example I really only need to query data from one table [MSreplication_monitordata] which resides within the distribution database. If you have replication setup then you will have a distribution database, but if you don’t then the distribution database will not be available. Also note this example is based on a single publication so if you have multiple publications then you will see more than one entry per subscriber per publication.

Step 1: Simple Query

I just want the retention period…

SELECT retention 
FROM [Distribution]..[MSreplication_monitordata] 

Now we know what the retention period is set to and that is 14 days. Now I need to multiply the retention value by .8 and the result will be 11.2. So 80% of 14 = 11.2.

Step 2: Simple Calculation

SELECT retention
      ,(retention * .8) 
FROM [Distribution]..[MSreplication_monitordata]

Side Note:

Keep in mind if you want to get the threshold value of the metric being monitored from the database JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id and pull the t.[value] which will return the value of 80 which is the default. If it was changed then of course the value will differ. Just note that this is a sql_variant data type so you will need to CAST or CONVERT it to perform the calculation.

SELECT	m.retention 
		,t.[value]
		-- These steps are just for illustration
		,(CAST(t.[value] AS DECIMAL(10,2))/100) '1'
		,(m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) '2'
		,CAST((m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) AS INT) '3'
		-- The following is faster and easier
		,(m.retention * CAST(t.[value] AS INT)/100) '4'
FROM [Distribution]..[MSreplication_monitordata] m
JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id

What this means in terms of Replication Monitor is that any subscriber that has not synchronized for 11 days with the retention set to 14 days will have a subscription status of “Expiring Soon/Expired“. If the retention was set to 30 days which is also common and the threshold set to the default of 80% then the subscribers would show the same the status when they reach 24 days without synchronizing.

Step 3: Round the Results

11.2 is a good start, but I need a whole number so I will wrap line 2 from (Step 2) inside a ROUND function and supply 0 for the length or precision as it needs one argument.

SELECT retention
      ,ROUND((retention * .8) ,0)
FROM [Distribution]..[MSreplication_monitordata]

Step 4: Remove the Decimal

The above statement returns a value of 11.0 which is rounded, but now I need to get rid of the decimal place so that 11 is the only thing returned. Again I will take line 2 this time from (Step 3) and wrap it in a CAST function and CAST it as INT.

SELECT retention
      ,CAST(ROUND((retention * .8) ,0) AS INT)
FROM [Distribution]..[MSreplication_monitordata]

Or you could have saved yourself a few steps and done this…

 
SELECT retention 
      ,CAST((retention * .8) AS INT) 
FROM [Distribution]..[MSreplication_monitordata] 

Final Results

When I apply the rounded value to my query as part of the WHERE clause only the records that fall within the expiration threshold (retention * .8) will be returned. Meaning subscriptions that are close to expiration and subscriptions that are expired.

The results are exactly what I wanted. Now I just need to take it a bit further… think email delivery!

I have wrapped this into a stored procedure and added some logic that will check for expiring records and if there are records found it will then tie the results into a nice html formatted message delivered by database mail. Then I setup a sql job which runs the stored procedure daily including weekends and fires off an email only when records are found. Technically I could fire off a report subscription which can be formatted very nicely and just fire off the job using exec sp_start_job @JobName = ‘Report Subscription ID goes here’ when ever records are detected.

For more information about using ROUND please visit: http://msdn.microsoft.com/en-us/library/ms175003.aspx

Download the code

SELECT.GetExpiringSubscriptions_v0.01.sql

/*****************************************************************
**	Author	:	Samson J. Loo (justsamson.com | @just_samson)
**	Created : 9/26/2010
**	Intent	: Notification of subscriptions that are nearing the
**						the expiration threshold
**	Notes	: Requires db mail
**	Version	: 0.01
*****************************************************************/

IF OBJECT_ID('sp_RP_GetExpiringSubscribers') IS NOT NULL
DROP PROCEDURE sp_RP_GetExpiringSubscribers
GO
CREATE PROCEDURE [dbo].[sp_RP_GetExpiringSubscribers]
AS

DECLARE @cnt INT
SET @cnt = 0
SELECT	@cnt = COUNT(a.[subscriber_name])
FROM [Distribution]..[MSmerge_sessions] s
	JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
	JOIN [Distribution]..[MSreplication_monitordata] m ON s.agent_id = m.agent_id
	JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
WHERE s.end_time IN
(
	SELECT TOP 1 s1.end_time 
	FROM [Distribution]..[MSmerge_sessions] s1
	WHERE s.agent_id = s1.agent_id
	ORDER BY s1.end_time DESC
) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
AND t.[isenabled] = 1
GROUP BY s.[start_time]

IF @cnt > 0
	BEGIN
		
		DECLARE @tableHTML  NVARCHAR(MAX) ;

		SET @tableHTML =
			N'<H1><font color="#FF0000">Expiring Subscription Report</font></H1>' +
			N'<table border="0" cellspacing="2" cellpadding="2">' +
			N'<tr><th bgcolor="#BDBDBD">Subscriber</th>' +
						N'<th bgcolor="#BDBDBD">Status</th>
							<th bgcolor="#BDBDBD">Delivery Rate</th>
							<th bgcolor="#BDBDBD">Last Sync</th>' +
						N'<th bgcolor="#BDBDBD">Duration</th>
							<th bgcolor="#BDBDBD">Conn Type</th>
							<th bgcolor="#BDBDBD">Result</th>
							<th bgcolor="#BDBDBD">Days Behind</th>					
						  <th bgcolor="#BDBDBD">Subscriber Status</th></tr>' +
			CAST ( ( 

		SELECT
		td = CASE
					WHEN CHARINDEX('\',a.[subscriber_name]) > 0 THEN LEFT(a.[subscriber_name],CHARINDEX('\',a.[subscriber_name])-1)
					ELSE a.[subscriber_name]
				END	
			,''
			,td = CASE
				WHEN s.[runstatus] = 3 THEN 'Synchornizing'
				WHEN s.[runstatus] = 5 THEN 'Retrying failed command'
			ELSE 'Not Synchronizing'
			END 
			,''
			,td = CAST(s.[delivery_rate] AS VARCHAR) + ' rows/sec'
			,''
			,td = s.[start_time]
			,''
			,td = CAST((s.[duration]/86400) AS VARCHAR) 
			+ '.' + CAST(REPLACE(STR(((s.[duration]/3600) - ((s.[duration]/86400) * 24)),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 3600/60),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 60),2),SPACE(1),0) AS VARCHAR)	
			,''
			,td = CASE 
				WHEN s.[connection_type] = 1 THEN 'LAN'
				WHEN s.[connection_type] = 2 THEN 'Dialup'
				WHEN s.[connection_type] = 3 THEN 'Web Sync'
			END
			,''
			,td = CASE
				WHEN s.[runstatus] = 1 THEN 'Start'
				WHEN s.[runstatus] = 2 THEN 'Succeed'
				WHEN s.[runstatus] = 3 THEN 'In Progress'
				WHEN s.[runstatus] = 4 THEN 'Idle'
				WHEN s.[runstatus] = 5 THEN 'Retry'
				WHEN s.[runstatus] = 6 THEN 'Error'
			END
			,''
			,td = DATEDIFF(d,s.[start_time],getdate())
			,''
			,td = CASE 
				WHEN (DATEDIFF(d,s.[start_time],getdate()) < CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)) THEN 'Good'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) <= m.[retention]) THEN 'Expiring Soon'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) > m.[retention]) THEN 'Expired'
			END
			--,m.[retention]
		FROM [Distribution]..[MSmerge_sessions] s
			JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
			JOIN [Distribution]..[MSreplication_monitordata] m ON a.id = m.agent_id
			JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
		WHERE s.end_time IN
		(
			SELECT TOP 1 s1.end_time 
			FROM [Distribution]..[MSmerge_sessions] s1
			WHERE s.agent_id = s1.agent_id
			ORDER BY s1.end_time DESC
		) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
		AND t.[isenabled] = 1
		ORDER BY s.[start_time]    
		    
			FOR XML PATH('tr'), TYPE 
			) AS NVARCHAR(MAX) ) +
			N'</table>' ;

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'WorkingNotifier',
			@recipients='you@yourdomain.com',
			@copy_recipients='someone@somewhere.com',
			@subject = 'Expiring Subscription Report',
			@body = @tableHTML,
			@body_format = 'HTML' ;
		
		
	END
ELSE
	BEGIN
		PRINT 'No Records Found!'
	END

Update your Skill-Set and Stay Marketable in IT

It’s no secret that you need to stay on top of the latest technologies in order to stay marketable, but how do you do it on a tight budget? For one you cannot just go out and purchase the most beefy server(s) available because the hardware alone can cost at minimum two-three months rent and that’s a scaled down version. The server’s I often dream about are way out of my budgetary league. Then you have the software and licensing to add to the piling list of expenses. So where do you turn? How about virtualization? After all virtualization is booming so while you’re practicing Active Directory management or SQL Server Mirroring you can learn a few things about virtualization in the process. And that my friend is what I consider a true win-win scenario!

Virtualizing as I like to call it is essentially running or hosting a guest operating system on top of another system. Your current system should have at least 4GB of RAM and running at least an Intel Core2Duo processor or comparable AMD. I mean you could make do with less but performance will suffer. Don’t get me wrong virtualization can be costly but there are affordable versions available. I am bias to VMware and I love Workstation 7 but it comes with a price. However VMware Server is (FREE), as well as VirtualBox (FREE) by SUN and VirtualPC (FREE) by Microsoft to name a few. I have used all of the software listed and found that VMware has the fastest load times. I am talking about installing and loading the Operating System form an ISO CD image. Honestly in the end they all accomplished the same results so no matter what you choose you will still reap benefits of virtualization. Now you just need to get your hands on some operating systems and server software.

Good news… if you are a college student you have some options. Academic versions are often offered at significantly reduced prices. If that is not an option you can sign up for Dreamspark.com and get software for free, but you need an active college email address to validate your student status. At the very least you have the trials editions offered by Microsoft that are readily available but you will be limited to 120 days. You can always reseal the OS and extend your trial a bit further so keep that in mind. With either software option you should get enough experience and understanding with the OS to hold an intelligent conversation about the subject matter.

Some people tend to think that their company should invest in them and pay for technical training when in reality you really need to invest in yourself. It speaks volumes about a person who is able to tackle new functionality from new releases let alone get certified without the aided help of the employer. Honestly how else are you supposed to get ahead and advance your career without putting forth the effort to learn on your own? Trust me education through osmosis does not work so don’t just by a book hoping it will make you smarter. You have to open it and read the pages to get the most out of that investment. Not to mention picking up some reading material is always a good idea and it’s also a great way to work towards certification. If all else fails you can always turn to google or bing and they are both filled with tons of useful resources.

If at all you are into SQL Server you can download SQL Server 2008 R2 Express which will provide you with the tools necessary to perform the install, configurations including settings and security, restores, backups, business intelligence and well as diving into the Transact-SQL query language. However if you want to work with mirroring, replication, high availability then you can opt for Developer Edition which is typically $50. It provides all the functionality of Enterprise edition with limitations of course. So you can create database snapshots, setup principal and mirror servers, setup a publication, remote or local distributors and other good stuff. So what are you waiting for? It’s at your grasp… so grab a hold of it and run with it!