Pass Summit 2011… A First Timers Experience
Words really cannot describe the experience. The SQL Server community is an amazing community to be a part of. The camaraderie among peers is undoubtedly extraordinary. From what I recall there were over 500 first timers this year which just proves the PASS community is growing at a great rate. I have only been involved with PASS for a few years now and have become the V.P. for the Arizona Pass Chapter, a SQLSaturday Phoenix Organizer and now a volunteer/presenter scheduler for the Performance Pass Virtual Chapter. So if you have any questions about Pass feel free to drop me a line, I would love to chat with you about it.
Back to the summit…
The Pass Summit is technically a three-day fun-filled event from Wed – Fri but there were pre-conference seminars (precons) that occurred on Monday and Tuesday which I did not attend. From what I hear the (precons) were amazing. Hopefully next year I can be fortunate enough to attend those as well. I only attended the Wed – Fri sessions. My main focus was on performance but there were many other tracks to choose from. The ever so popular business intelligence to administration to development to professional development. Definitely something for everyone.
The training is just one part of the conference but the relationships you build from networking is priceless. I finally met so many people whom I have literally known for several years for the first time. I know that sounds odd, but the power of social networking just brings people from all walks of life together. The best part is that you feel like you’ve been friends forever even though this is the first time meeting one another. That’s the energy of the SQL Community and I am proud to be a part of it. I learned a lot and have so many thoughts and scenarios running through my mind that I need to organize them into actionable items and prepare to blog about them. There’s so much more I can say about the benefits of attending the Pass Summit, but take it from me (a first timer) that it’s well worth it and you’ll never forget it. Hope to see and/or talk with you soon at a local, national or international event!
Just in case you wanted to see some the photos taken at this years and past Pass Summit events… Pass Summit 2011 and Pass Summit Past Events.
Fun times at SSAS Workshop
I had the extreme pleasure of attending the SSAS Workshop by PragmaticWorks this week, which was a two-day session with a bonus third day entirely focused on Denali (Expedition Denali). Brian Knight (blog | @brianknight), Dustin Ryan (blog | @SQLDusty) and Lonnie Mejia (LinkedIn) were on site at the Microsoft Southwest District office in Tempe, AZ which has a beautiful view of Tempe Town Lake.
I have only had a little exposure to SQL Server Analysis Services before this and from what I have learned I do know that our own data warehouse group could significantly benefit from this workshop. I am not mocking them whatsoever, but I am saying some processes could be handled differently. For example cube updates. Instead of providing me the entire visual studio solution they can easily provide me a XMLA script which I can use in SSMS to deploy the dimension update. Things like this I never knew, so this was a real eye opener for me and gives me the needed ammo to fight with our developers. Kidding! It does however allow me to extend my freshly acquired knowledge to that group in a non-confrontational way of course (fingers crossed behind back).
Business intelligence has a warm place in my heart and the time I did spend developing reports was exciting. To be honest I would love nothing more than to be able to go from zero to data warehouse to SSAS slice and dice to full publish on reporting services, sharepoint, etc… in a week or so. I believe as a DBA that would be a valuable skill-set to have under my belt. This course is my step towards that direction.
There is no doubt that this workshop packs in a lot of information. The two days are literally bursting at the seams with information but this is definitely a MUST for those looking to get into the SSAS world. The PragmaticWorks staff really demystified SSAS. Their lectures and labs are delivered in such a manner that it is really easy to keep up with the pace. Throughout the course you are walked through the process of setting up an SSAS project all the way through creating cubes, dimensions, mining structures, roles and everything in between. The price of the course is a bargain given everything you walk away with.
I think the most action came towards the ending of day two. The room was divided down the middle and the groups were paired against each other to build an SSAS project from start to finish following a set of requirements. Then you needed to create a report in either reporting services or excel based on the cube we published. Everyone participated either by being the designated drivers (at the computer), yelling out the requirements, providing assistance and so on. It was intense! I must mention that “Team Dustin” my group WON the challenge against “Team Brian”. Better luck next time Brian! We literally beat them by 1-2 seconds at best. Nonetheless a fantastic method to illustrate not only what we had learned but more importantly what we had retained. If you get the opportunity to attend this workshop I would highly recommend it. You will not be sorry!
Expedition Denali (Day 3) was very exceptional. I have not touched Denali at all but from what Roger Doherty (blog | @Doherty100) and Brian Knight were covering and demoing I cannot wait till RTM. I would totally spill the beans because there are so many very cool and sexy things coming… but their “body-guard/new sales guy” Lonnie is a pretty big guy so I will refrain. Here he is working through the demo.
Random pics
Get IP Address From Windows Command Line
I know many can and will say I can simply use ipconfig or ping the local computer name and to an extent that’s true. In my case I really only want the IP Address and nothing else, just the plain IP Address. I don’t want the extra verbiage that goes along with it.
To get started let’s run through a simple statement, but before we do know that this is geared towards a command prompt and not a batch. The syntax is slightly different.
Step 1: Get only one reply
ping %computername% -4 -n 1 | find /i "reply"
Step 2: Get all left of the colon
FOR /f "tokens=1 delims=:" %d IN ('ping %computername% -4 -n 1 ^| find /i "reply"') DO ECHO %d
Step 3: Get the IP Address
FOR /f "tokens=1 delims=:" %d IN ('ping %computername% -4 -n 1 ^| find /i "reply"') DO FOR /F "tokens=3 delims= " %g IN ("%d") DO echo %g
Step 4: Get the first octet
You might question why you would only want the first octet and the answer is simple. Based on that single value I can determine what the backup share is. So if I were to return only the first octet into a stored procedure then it can dynamically perform backups accordingly to the appropriate share.
FOR /F "tokens=1 delims=:" %d IN ('ping %computername% -4 -n 1 ^| find /i "reply"') DO FOR /F "tokens=3 delims= " %g IN ("%d") DO FOR /F "tokens=1 delims=." %h IN ("%g") DO ECHO %h
At this point you might be asking yourself what the syntax means. Well here is the scoop using (Step 2) as a reference point. Well consider tokens as segments of a single item that is separated by a specific value.
Let’s examine the following string:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Looks pretty straightforward for the most part but if you think about what the separating value that you’ll want to use then the string begins to appear differently. For example I want to set the delims otherwise known as the deliminator character to a colon. Well there is only one colon therefore making two tokens. All characters left and all character right of the colon.
So by me running (Step 2) I am essentially requesting all characters to the left of the colon, because I am only asking for token 1. If I specified token 2 then I would get all characters to the right of the colon including the leading space.
FOR /f "tokens=2 delims=:" %d IN ('ping %computername% -4 -n 1 ^| find /i "reply"') DO ECHO %d
Now moving onto (Step 3) I am essentially breaking apart the string into three tokens because I am setting the delims to a space which is represented by delims= “. There is a space between the = and the “.
Let’s examine the string:
Reply from 127.0.0.1
Hopefully at this point you are able to see the three tokens in the above string. So in order to return only the IP Address I only request token 3.
POP QUIZ
Q1. What would be the delims value for 127.0.0.1?
Q2. How many tokens will be as a result?
Q3. What token will I need to request to get the first octet?
A Different Day
5/25 started out different. I woke up about an hour earlier than my alarm despite the fact I ended up crashing out around 1:30 AM. I woke up completely awake, perfectly content and I felt very refreshed with only 4.5 hrs of rest. I had no need for coffee, just a tuck and roll to the home office with a quick stop at the local Bistro (my kitchen) for a power breakfast a la cart. You have to love light traffic.
I logged into work and I flew through the critical SQL SCOM alerts and moved onto the SQL Health & Backup report. Then I finished up right on time to join SQL Sentry’s “Learn How to Tune Queries” webinar. Unfortunately I am not well versed with 3rd party monitoring tools outside of the minimal SSMS tools, which is why I tend to participate as much as I can in demos, forums and similar sessions.
I learned a lot about SQL Sentry’s Plan Explorer and I can honestly say it will definitely be extremely beneficial as I venture into the realm of performance tuning. This literally could not have come at a better time. At my current place of employment there are plans in the works to include me in several tuning aspects which is why I have immersed myself with profiler, database engine tuning advisor and thanks to the SQL community I am learning the many available DMVs.
A huge surprise came at the end of the session. They held a raffle and they happened to call my name. Unfortunately I couldn’t hear the audio portion when they explained what the item was, but I did hear my name called, so I responded. I had figured I won a license for one of their awesome monitoring software packages like the SQL Sentry: Power Suite or their Performance Advisor for SQL Server. To be honest it really didn’t matter to me because either would have been a sweet prize!
Anxious as I was I turned to twitter and pinged Brent Ozar (@BrentO | Blog) and asked what I had won since he was associated with the session. Then shortly after Aaron Bertrand (@AaronBertrand | Blog) responded and the convo went something like this:
As you can tell I was (still am) excited. Everything just worked out perfectly. So I wanted to take this opportunity to say thank you to SQL Sentry, Aaron Bertran, Greg Gonzalez (@SQLsensei | Blog), Peter Shire (@Peter_Shire) and the rest of the SQL Sentry staff for the awesome presentation and the gift. I also want to the thank the organizers of SQLCruise for hosting the event. To bad I am not going on the cruise, but I’ll be there in spirit!
Thank you SQL Sentry!!!
Did I mention the iPad 2 arrives… on… FRIDAY!!!
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.
- 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.
Flickr Photos
|
