I recently stumbled upon a problem where there were duplicate commands within the sub menu of Data in Business Intelligence Development Studio 2008. To give you a birds eye view below is what we were facing.
With a little research and some musical chairs with keywords I stumbled upon the following post: Visual Studio team System 2008 Database Edition FAQ which pinpointed the solution. You’ll need to scroll down a bit till you see “I have installed the GDR, but now I see duplicate commands on the menus in the IDE.”
1) Be sure to exit out of all the visual studio sessions
2) Open a command prompt
3) Within the command prompt navigate to %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\
Execute the following:
You will not see a confirmation message after running the above statement. To verify launch BIDS and click on Data. It should now look like the following.
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.
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)
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.
- Cumulative Update package 1 for SQL Server 2008 R2
- Cumulative Update package 2 for SQL Server 2008 R2
- Cumulative Update package 3 for SQL Server 2008 R2
- Cumulative Update package 4 for SQL Server 2008 R2
- Cumulative Update package 5 for SQL Server 2008 R2
- Cumulative Update package 6 for SQL Server 2008 R2
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.
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.
I was reviewing some of my older scripts and I came across one in particular that stood out. I wrote it quite a while ago and looking back does indicate where I came from in terms of logic. To give a little background my script was written to grab the row counts from all tables within the given database. The problem… it created a temp table then loaded the temp table from querying the INFORMATION_SCHEMA.TABLES. Then it looped through each of the table names loading them it into a variable and then passed them to an update statement which updated the temp table. As you can see this was a very inefficient way to go about it.
I was reading up on catalog views and it seemed that I could query sys.sysobjects and join sys.sysindexes. So I figured I would get this verified. I posed the question and following are the responses I received.
Yep. sys.sysindexes is depreciated in Denali. Information here: msdn.microsoft.com
So here is my script. I originally left out line 15 and when I did I saw a lot of duplication in the results set. So I surfed around and found a post titled, “Display Row count for all tables” by Shyam Skj that used the indid field from sysindexes to filter the records. The closes thing I found was the index_id from sys.partitions and lo and behold using similar syntax the results returned nicely.
/* Check if object already exists */ IF OBJECT_ID('usp_GetTableRowCounts') IS NOT NULL /* If available drop object */ BEGIN DROP PROCEDURE usp_GetTableRowCounts END GO /* Create procedure */ CREATE PROCEDURE usp_GetTableRowCounts AS SELECT o.name 'TableName', p.rows 'Rows' FROM sys.objects o JOIN sys.partitions p ON o.object_id = p.object_id WHERE (o.type = 'U') AND (p.index_id IN (0,1)) ORDER BY p.rows DESC
Usually I can simply run an ALTER statement against the database and SET SINGLE_USER WITH NO_WAIT like so:
ALTER DATABASE MyDBName SET SINGLE_USER WITH NO_WAIT
but I ran into a problem with active processes that prevented me from switching to SINGLE_USER mode. So I executed the sp_Who2 stored procedure and identified the spids responsible and killed them!
I essentially ran the kill command for every spid that was associated to the specific database. So you can see it was very inefficient and a great waste of time.
KILL 54 KILL 58
Now imagine if I had to do this will 200 subscribers… not a fun time!
Then I re-ran the stored procedure sp_Who2 again to make sure they were dead.
This worked for me at the time, but later in the future I don’t want to deal with the hassles of running through all the steps involved. I turned to BOL and then I remembered I could easily query the sysprocesses and mock up a script but I didn’t really have the time to run through the whole Q/A process. So I searched the interwebs and stumbled upon the blog post [Script to drop all connections to a Database] by Tijo. It was exactly what I was looking for. So I borrowed it and merged it with some additional items that are specific to our clean up steps. In a nutshell here is the final outcome.
-- KILL ACTIVE PROCESSES DECLARE @db NVARCHAR(50) DECLARE @spid int SET @db = 'myDBName' SELECT @spid = MIN(spid) FROM sys.sysprocesses WHERE dbid = db_id(@db) WHILE @spid IS NOT NULL BEGIN SELECT @spid EXEC ('KILL ' + @spid) SELECT @spid = MIN(spid) FROM sys.sysprocesses WHERE dbid = db_id(@db) END -- DROP DATABASE DECLARE @cmd1 NVARCHAR(75) DECLARE @cmd2 NVARCHAR(50) SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT' SET @cmd2 = 'DROP DATABASE ' + @db EXEC(@cmd1) EXEC(@cmd2) -- DROP USER ACCOUNT DECLARE @cmd3 NVARCHAR(65) IF EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent') SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]' EXEC(@cmd3) GO
Also thinking about the future I combined this sql script with a batch file using SQLCMD and provided it to the onsite support staff so that they can handle the reinitialization for subscriptions that fall outside of their threshold.
@echo off color 17 Title Device Reinit Clean Up . . . :: ========================= SET srv=(local)\Instance :: ========================= SQLCMD -S %srv% -E -i "Device.ReinitCleanUp.sql" PAUSE
Both files need to reside in the same directory in order to work; however, if you decide to call the sql script from a network share be sure to make the necessary adjustments. Reason being is that we don’t deploy management studio on the subscribers and some of the onsite technical support staff are unfamiliar with SQL Server. So I would rather not take any chances which is why I am going to add an additional condition.
IF HOST_NAME() = 'servername' BEGIN PRINT '**** WARNING ****' PRINT 'You attempted to apply the cleanup script against the Production Server!' PRINT 'Terminating process!' END ELSE BEGIN -- KILL ACTIVE PROCESSES DECLARE @db NVARCHAR(50) DECLARE @spid int SET @db = 'myDBName' SELECT @spid = MIN(spid) FROM sys.sysprocesses WHERE dbid = db_id(@db) WHILE @spid IS NOT NULL BEGIN SELECT @spid EXEC ('KILL ' + @spid) SELECT @spid = MIN(spid) FROM sys.sysprocesses WHERE dbid = db_id(@db) END -- DROP DATABASE DECLARE @cmd1 NVARCHAR(75) DECLARE @cmd2 NVARCHAR(50) SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT' SET @cmd2 = 'DROP DATABASE ' + @db EXEC(@cmd1) EXEC(@cmd2) -- DROP USER ACCOUNT DECLARE @cmd3 NVARCHAR(65) IF EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent') SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]' EXEC(@cmd3) END
This video steps you through the process of deploying the SQL Server Analysis Services sample database.
Music by Kevin MacLeod
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]
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]
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
/***************************************************************** ** 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', @email@example.com', @firstname.lastname@example.org', @subject = 'Expiring Subscription Report', @body = @tableHTML, @body_format = 'HTML' ; END ELSE BEGIN PRINT 'No Records Found!' END
I merely ready about policy based management (PBM) through short snippets here and there, but never really got that in-depth with it as I normally do with other subjects. I think the major factor was due to the name Policy. Mistakenly I automatically associated it with Active Directory. PBM was introduced with SQL Server 2008 and offers great benefits with administering SQL Server. I was conducting some research on the subject and found a post by Ashish Kumar Mehta titled: “Identify Databases Not in Full Recovery Model Using Policy Based Management” which steps you through the process of creating a simple policy and runs you through the evaluation process. Ashish covers the steps rather well so be sure to visit the post for more details. If you want to get deeper into the terminology, scheduling and such then visit a post by Ray Barley titled: “Using Policy-Based Management in SQL Server 2008“
After walking through Ashish’s post I wanted to make one similar but I wanted to return all Windows Accounts and such, though his use for the Recovery Model is more practical. So fire up your SSMS 2008 and let’s get started. This is just a quick run down of how to create one. This does not get into the deep details that others like have. More of a pre-primer primer if you will.
Open SQL Server Management Studio and double-click or expand Management.
Right click on Policies
Select New Policy…
Give your policy a name
Click the Check Condition and Select New Condition…
Give your Condition a name… I know it requires a lot of naming
Select the Facet drop down and Select Login
In the Expression section click on the Field column and select @LoginType
Select the Operator (=)
Select WindowsUser for the Value and hit Enter
Click Ok to create the Condition and the Policy
Policies should be expanded now and you should see your newly created policy. Expand Condition and you should see the condition you just finalized.
Now let’s test the Policy. Right click on the newly created Policy and select Evaluate.
And this brings us to the end results
The green marks are the Windows Accounts and the Red marked rows are not.
In the event you are working on a server that may or may not have policies in place Ken Simmons provided me with the following select statement that will return information about all the existing policies.
SELECT * FROM msdb.dbo.syspolicy_policies
In the event you incorrectly spelled a policy here is a method that will allow you to rename it. Again thanks to Ken Simmons with the #sqlhelp tweet.
SELECT * FROM msdb.dbo.sp_syspolicy_rename_policy @name = 'OldName', @new_name = 'NewName
Ken Simmons (website | @KenSimmons) is also a published author on this very subject so check out his book titled: (Pro SQL Server 2008 Policy-Based Management) with was co-authored by Jorge Segarra (website | @SQLChicken) and Colin Stasiuk (website | @benchmarkIT)
I am a little late in the certification arena but I have a good excuse… my dog ate my ambition! Actually I have been plugging away at my Computer Information Systems (CIS) degree with a concentration of Database Management and it boils down to a few remaining classes. Seems like I have been working towards this degree for a lifetime. I would have been finished long ago, but I had to decide if I would: 1) either run full steam ahead with my education while working full-time in a travelling required position and let my grades suffer or 2) attend part-time and maintain a 3.5 or higher GPA. I choose the latter. Even though some have told me that a (D) will still get you a degree, that is just not my character. I believe if you are going to commit, then you need to commit 100%. Especially when the investment comes with a hefty price tag. Not to mention “half-ass-ism” can be habit-forming and that quite frankly is something I am not looking to catch.
During these next few classes I have decided to pursue DBA specific certifications. I mean I am certified but the certifications that I currently hold are desktop support centric and since my role has changed it is only natural that I change my focus. To be honest when I first started in the IT field I really wanted nothing to do with databases. I was set on being a Server or Network Admin and thought databases were boring. Looking back I realize I was foolish because now I am consumed in it and wish to do nothing more but DBA work. I literally started working with SQL Server 2000 by an initiative that was put into action by my previous employer. The overall goal was to eliminate the use of paperwork for field staff. So the consultants were brought in and shortly after careful observation a project was born. Instead of equipping the field staff with paperwork to shuffle they were issued laptops with a custom developed application and a local copy of sql server (MSDE) back then. The fact we implemented Merge Replication somewhat enticed me to refer to the field staff as subscribers rather than field staff because I was trying to immerse myself with SQL Server terminology as much as I could. Not to mention I felt cool just speaking it out loud during meetings since this was a new concept to our organization.
I have heard and read about the pros and cons about certifications, but I see it as another item to prove that I can take my own initiatives along with my willingness to continue and further my knowledge and skill-set. Another available avenue for continual education. I have also heard certifications combined with a degree tends to hold more weight over those with just a certification but that is debatable. In the end I suppose it is all up to you and how you feel about it. Some say it boosts personal self-confidence and I am all for that. Why not?
The first exam I might tackle is (70-431: Microsoft SQL Server 2005 Implementation and Maintenance). I know it is a SQL Server 2005 exam, but I am more familiar with 2005 at the present time over 2008. I picked up the Microsoft Press Self-Paced book and will use that as my primary study material. I have heard and read many good things about this title. I also thought about (70-445: Business Intelligence Development and Maintenance). I cannot decide actually because I have a love for both. I see a need for Administration and Business Intelligence development. Either way I figured I would set study time to one hour a day at minimum with weekends being optional. If I can or cannot cover multiple chapters in one hour then so be it. I need to be strict with a schedule in order to stick with it. Luckily I have a license for VMware Workstation 7 which allows me to build a VM study system that I can literally destroy and revert when needed. This will provide me an adequate study platform during my progress. One thing I need to do is schedule myself for the actual test as it will make the process more official since I will be actually setting a realistic and attainable goal. Not to mention it will keep me focused since the added penalty of wasting money will add fire to my priorities and will align my studies.
I am interested in knowing what you would pursue first? SQL Server Administration or Business Intelligence?