The next couple of weeks are going to me huge in my SQL Server career. Next week I will be attending the SQLskills.com Immersion Training for Internals & Performance. There are no words to express my excitement about this training as I don’t think I can truly comprehend how much I will be learning and moving my career into overdrive. As I move towards the MCITP, and quite possibly towards the MCM or Microsoft Certified Master, this training will move me along in my journey into the BIG TIME!
The following week, I will be standing up two new SQL Server 2008 R2 clusters for a huge project that will impact the future of SQL Server in my organization. This is a pivotal project and it is my first time standing up clusters on my own as I have maintained clusters but never built them on my own. For this reason, we have asked our Microsoft Premier Field Engineer to assist and I will be soaking up her knowledge as we build a solid production and acceptance cluster environments. If that project is successful, we will then look to move more of our Oracle systems to SQL Server and my plan for world domination will be set in motion.
And finally on the week after that we will be meeting with Microsoft again to discuss our plans to move to SQL Server 2012 and help management understand the new pricing structure. Hopefully we will be able to move forward and onto the next version of SQL Awesomeness. These next three weeks will be pivotal for my career, time to dominate! Enjoy!
Earlier this week, I chose a few SQL Server 2008 topics for our #SQLHelp MCITP study group to discuss here on the blog. Thus, today I will discuss logon triggers, one of the new features available in SQL Server 2008 (well actually if I remember correctly they were snuck in 2005 SP2…shhhh). Logon triggers are a special form of trigger that the database administrator can use in order to monitor logon events.
Logon triggers can be used for the following purposes:
- To audit and control server sessions (such as tracking logon activity)
- Limiting the number of sessions allowed for a particular logon
- Restricting logons to SQL Server (similar to AD snap-in that lets you set the hours that a user may logon)
Here is an example from books online showing how to set up a logon trigger that limits the number of concurrent sessions for a particular logon, logon_test in this case. This example, in particular, will limit the concurrent sessions to three. You must create this logon in order to test this code, or change the logon name to match your requirements.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘logon_test’
IF ORIGINAL_LOGIN()= ‘logon_test’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘logon_test’) > 3
Here is some sample code for restricting the user based on logon time (between 12Pm and 1pm in this example for all users except the SA\SQLAdministrator):
CREATE TRIGGER Restrict_Logon_HoursON ALL SERVERFOR LOGONASBEGINIF (DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13) AND(SUSER_SNAME() != ‘AD\SQLAdministrator’)BEGINROLLBACK;
Once you have created logon triggers, you can view them by a simple query to the sys.server_triggers metadata.
As you may already know from my blog, I have started down the path moving towards my MCITP for SQL Server 2008. Now I have some logistical quandaries and maybe some of my #SQLFamily can provide some insight or opinions.
The first item I was looking at, was which class to take. Obviously, there is a Global Knowledgeboot camp entitled: MCITP: Database Administrator, SQL Server 2008 Boot Camp. This class covers the 70-432 and 70-450 Microsoft exams needed to obtain the MCITP. I have alreadypassed the 70-432, but they are rolled into one course which is fine by me because you can never receive too much SQL knowledge. Other than that course, I have not really seen any other MCITP specific courses in my area. However, someone had mentioned to me that taking one or more of the SQLskills immersion courses would be great in this case. I asked this question a couple of times on twitter yesterday and did not receive any response. Any ideas?
Next up….test prep. I have never really been a big proponent of the test prep software. I have always bought books and taken classes and studied that material. Is that a good idea with the MCITP or does this particular exam need test preparation software?
Finally, are there any MCITP books that are better at preparing you for this exam? I have heard that the Microsoft SQL Server 2008 Bible and the Microsoft SQL Server 2008 Unleashed books are excellent sources for study information. Amazon has a book specifically for the MCITP, called Real World Skill for MCITP Certification and Beyond. Anyone used it?
This journey is starting to come together, I just need a few more tools. Enjoy!
Yesterday I attended a Brent Ozar PLF webinar with Kendra Little (blog : twitter). The webinar, Anatomy of the SQL Server Database: Settings and Secrets, was great, as per usual. What transpired at the end of the training was magical. As you know, if you have been reading my blog, I have been contemplating moving forward to pursuing my MCITP for SQL Server 2008. Kendra made an announcement that Brandon Leach (twitter) was forming an MCITP study group of like-minded professionals to get together online and help each other out. Hey that is exactly what I have been looking for! My #SQLFamily is really an amazing group of people, and I wish I had discovered them years ago while banging my head against the wall upgrading SQL 6.5 to 7 and then on to 2000. At the time, I was an accidental DBA. Those days have long passed, thanks to my MCTS. MCITP: Here I Come!
I have been contemplating my next move for SQL certification. Last spring, I earned my MCTS for SQL Server 2008 Implementaion and Maintenance passing the 70-432 exam after a couple of months of preparation and a TechSherpas course. The next logical step is the 70-450 which is Designing, Optimizing and Maintaining a Database Administrative Solution using Microsoft SQL Server 2008. The title alone is impressive. I think with this budget year I will have the opportunity to take a bootcamp for this certification.
Should I wait before pursuing this level? Is it recommended to have more experience before taking this exam? What do you think?