Monthly Archives: January 2013
SQL Server Audit in SQL Server 2008 R2
Since I am out of the office today I though I would include this fun video from Microsoft to continue with our Auditing theme that we started earlier in the week. Enjoy!
SQL Server News Hour: SQL Server Audit in SQL Server 2008 R2 – YouTube.
SQL Server Auditing: A Learning Series Part Two
Continuing our series on Auditing from yesterday, I wanted to bring up a few additional points if you are planning on using the Windows Security log as the target for your auditing results.
- You must add the SQL Server service (the account that you are actually using to run SQL Server, go to the SQL Server Configuration manager and check out the Log On As column) to the Generate security audits policy. Go to your Local Security Policy then under Security Settings select Local Policies the User Rights Assignment. There you will find the policy so that you may add the account similar to what is shown Figure 1.
- Keep in mind if you are running in a clustered environment you need to do this on each node so that in a failover scenario the auditing continues to work as designed.
- Also in the Local Security Policy, you need to go to Local Policies then Audit Policy and select to audit success and failure for the Audit Object Access policy.
In addition, if you plan on using a file as a target instead of the windows logs you must keep the following in mind:
- The SQL Server service account must have the ability to read and write to the file.
- If you have a user account that is a member of the Audit Administrator role, they must also have the ability to read and write to the file.
- Finally, if you have users with the Audit Reader role, then they must have the ability to read the file.
Figure 1 – Generate Security Audits Policy
Enjoy and stay tuned as we continue this series!
SQL Server Auditing: A Learning Series Part One
Over the next few weeks I will be presenting here for your learning SQL Server Auditing, as I learn it. I have never been called upon to use the auditing features in any production SQL Server environments strangely enough. I have known about the capabilities but never really been in a position to advocate for or against them.
Let us start our journey with some limitations. Auditing uses SQL Server resources, albeit less than trace-based auditing. This may or may not be a big deal depending upon how busy the server is. Another limitation is the fact that auditing in limited at the instance level. There is no easy way to manage auditing on multiple servers from a central location. There is also no built-in reporting for auditing and the data is stored in a file or OS event logs. You can however, load that data into a database and create your own reports.
Enjoy!
Point In Time Restores
One of the many skills needed by DBAs today is the ability to restore a database from backup using point in time recovery. This option is only available if you are using the full or bulk-logged recovery model (see my post on recovery models). The default time shown is the ‘most recent possible’ which works in most scenarios.
Why would I need to restore to a point in time? There are many different reasons, one being that you noticed through your monitoring or logs that things went south at a certain point in time. Therefore, we want to restore a minute or two prior to that time. Another scenario that I have seen is that a change is made in an application in production at a certain time. They now want those changes demoted down into a testing environment. In this scenario we would want to make sure our restore includes that point.
How do I do it? In the restore database dialog, you select the ellipsis (…) button to the right of the point in time field below the database name and you will see a date and time option, as shown in Figure 1. Select the remaining restore options as you normally would. Not sure about the rest of the options? See the MSDN directions detailing point in time recovery. Enjoy!
Figure 1 – Point In Time Restore
70-457 and 70-458 Game Plan Part Uno
Being methodical and logical as I am, I have set forth a plan to achieve my Microsoft Certified Solutions Associate as noted earlier in my goals for 2013. Having not found much in the way of preparation material as noted in this post , I decided to rent the 70-462 Training Kit from Amazon and transpose some of the objectives there to fit the 70-457 and 70-458 since I am taking the upgrade exams the materials for the regular exams should suffice in preparation.
Now I have outlined a calendar below and the hard dates to hold me accountable to the task especially since the book has to be returned in June. Let the journey begin!
| 70-462 Training Kit – 70-457 Objectives | |||
| Objective | Chapter | Lesson | Date |
| Install and Configure | |||
| Plan Installation | 1 | 1 | 1/20/2013 |
| Install SQL Server & related services | 1 | 2 | 1/20/2013 |
| Implement a migration strategy | 4 | 1 | 1/27/2013 |
| Configure additional SQL server components | 3 | 1 | 1/27/2013 |
| Manage SQL Server agent | 11 | 1 | 2/3/2013 |
| Maintain Instances and Databases | |||
| Manage and configure databases | 3 | 3 | 2/10/2013 |
| Configure SQL Server Instances | 2 | 1 | 2/17/2013 |
| Implement a SQL Server clustered instance | 8 | 1 | 2/24/2013 |
| Manage SQL Server instances | 2 | 2 | 3/3/2013 |
| Optimize and Troubleshoot | |||
| Identify and resolve concurrency problems | 10 | 2 | 3/10/2013 |
| Collect and analyze troubleshooting data | 9 | 1-6 | 3/17/2013 |
| Audit SQL Server instances | 6 | 3 | 3/24/2013 |
| 70-462 Training Kit – 70-458 Objectives | |||
| Manage Data | |||
| Configure and maintain a backup strategy | 11 | 2 | 3/31/2013 |
|
Restore databases
|
11 | 3 | 4/7/2013 |
|
Implement and maintain indexes
|
10 | 1 | 4/14/2013 |
| Import and Export Data | 4 | 2 | 4/21/2013 |
| Implement Security | |||
| Manage logins and server roles | 5 | 1 | 4/28/2013 |
| Manage database permissions | 6 | 1 | 5/5/2013 |
| Manage users and database roles | 5 | 2 | 5/12/2013 |
| Troubleshoot security | 6 | 2 | 5/19/2013 |
| Implement High Availability | |||
| Implement AlwaysOn | 8 | 2 | 5/26/2013 |
| Implement database mirroring | 7 | 1 | 6/2/2013 |
| Implement replication | 7 | 2 | 6/9/2013 |
Paleo Lifestyle
At the start of the new year, I began a lifestyle change (not a diet) to follow the Paleo lifestyle from recommendations from NerdFitness.com. If you are unaware the paleo lifestyle is to basically eat like the cavemen. You essentially eat meat, nuts, fruits and vegetables that would have been available to the cavemen and remove dairy, grains and sugars from your diet. I removed cokes (cherry pepsi to be precise) from my daily routine a couple of years ago and drink flavored water. Before this move, I eat a good portion of this diet previously only with added grains and enough dairy and cheese to choke a Wisconsinite.
Nine days into this lifestyle change and I have never felt better in my life, however It did take a couple of days to feel better and get adjusted. Now, I am rarely hungry in the late morning, afternoons, and late nights when I would usually run to snacks which is where my additional weight generally came from. Late night bowls of cereal, afternoon sweets, raiding cubicle candy jars, and mid morning energy bars were all within my realm of daily consumption because I never felt full. I was always hungry an hour or two after good size meals. I noticed this week that those feelings are all but gone.
It is also a good idea to eat a good breakfast and a big lunch followed up with a light dinner to provide the necessary energy for the day. I also walk daily and try to do cardio two to three times a week but those are outside of the meal plan as I have done those in the past and did not see significant and permanent weight reduction.
A typical day’s menu for me:
Breakfast
2 eggs scrambled with mushrooms, banana peppers, and jalapeno
Dinner (Lunch for non-Southerners)
Chicken or red meat with a hearty serving of vegetables (baked chicken breast with broccoli and cauliflower)
Supper
Half the portion of what I had for dinner.
Dessert
Bowl of fruit or coconut milk ice cream (surprised that this tasted like regular ice cream)
T-SQL Tuesday #38: Speaking of Resolve
This month’s topic for T-SQL Tuesday #38 hosted by Jason Brimhall (b|t) is an aptly themed variation on the word resolution. I personally chose the word resolve.
In 2012, I made a resolution to begin presenting at the local level. That sounds like a normal enough resolution for most, however being an introvert standing in front of people is quite terrifying. After attending many user group meetings and SQL Saturdays, I decided that since I have met many of these speakers and they are not much different from me, I should be able to stand up and do that too!
In the past, when called upon to speak in public or in front of a group my fight or flight instinct would kick in and generally I would want to run with every fiber of my being. Sometimes, I did run shamefully. Interviews are no different in many cases therefore it is safe to assume that I do not interview well. In the past, I have probably missed out on some good job opportunities simply because they thought I was an idiot based solely upon my presentation skills. It is because of this that I resolved to improve upon the skill.
Sounds good, huh? Make a resolution and then you do it, right? Easy as pie.
Not so fast, my friend. I still wanted to run Forrest run! Karla Landrum (b|t), the SQL Community Evangelist for SQL PASS, realized what was going on and she gently nudged me along to speak at the Pensacola SQL Saturday last summer. This is not unusual as historically I have been talked into doing stuff by women (and I probably should not have put that out there, so now you know).
That day in Pensacola, my fight or flight instinct kicked in again all morning long, but now I was on the hook and I did not want to disappoint Karla. Plus many of my colleagues knew I was speaking. Quite honestly this held me accountable for I did not want to ruin my reputation in the SQL community before I really even had one. My resolve that day held firm and I spoke quite nervously. But I did not run. I climbed the proverbial mountain and planted my flag upon its peak.
Since that time I have spoken at SQL Saturday Orlando and a user group meeting. It is getting easier with each event, but I still need to resolve myself to get better each time. Maybe one day I can speak at the PASS Summit? I will resolve myself to accomplish that goal and now you can hold me accountable. Enjoy!
70-457 Course of Action
Today I thought I would set forth a course of action in order to accomplish my goal of passing the 70-457 (and eventually 70-458) in order to achieve the Microsoft Certified Solutions Architect (how hard is it not to say Systems Administrator). I know what you are thinking, experience is the best trainer, but I like to be well prepared before I spend money to take an exam.
Sadly, Microsoft’s page for this exam has no content as far as a book, training, or even practice tests available for either exam. I understand that I could use the limited materials for exams 70-461, 462 and 463, but those are all assuming that you do not have an MCTS. I would like to limit my preparation to only what I need to pass the exam. I was able to find one book on Amazon, but I am not sure of the series. I have never used one of these books before. I think I will rent the 70-462 book from Amazon and that will force me to finish it by June.
Now in order to accomplish this goal I need to set aside at least four hours a week in preparation with book learning and using my 2012 Developer license at home. That will be tough, but now I have told you so I am now accountable. Please let me know if you have any tips for studying for this exam, I would appreciate your input. Enjoy!
Red Gate Virtual Restore Scripting (Part 10)
If you have been following along since December, then you know that I have been doing some investigation with the Red Gate Backup and Restore bundle to implement in our system with our use of Ola Hallengren’s backup maintenance scripts. One of our last stumbling blocks has been automating and scripting of the Red Gate Virtual Restore product. We have moved methodically through the list and with the help of Red Gate’s excellent support department resolved each item including this one.
The problem we had been that when we tried to verify the backup through restoration and DBCC CHECKDB on a certain database, that was considerably larger than all of the other databases on this server, we would not have enough space on the drive using our restore script we created last week. That scripting solution is great as long as there is enough space on the drives to attach the database files from backup and then drop them.
The beauty of the Virtual Restore product, in case you did not already know, is that it can restore these files with a much smaller footprint than the actual database files take up in the exiting format. However, the problem is that it is strictly a wizard at this point that will grab a specific backup and run the restore manually and then drop it once it checks the integrity and consistency. This is a great product but this is feature should be included or else it does not help much especially when you have hundreds of databases.
We ran the wizard and then viewed the script:
RESTORE DATABASE [TestDB_Virtual] FROM
DISK=N’I:\TestDB\FULL\SQLPROD40_TestDB_FULL_20130101_213217.sqb’
WITH MOVE N’TestDB_Data’ TO N’F:\VirtualRestore\TestDB_Data_TestDB_Virtual.vmdf’,
MOVE N’TestDB_Index’ TO N’F:\VirtualRestore\TestDB_idx_TestDB_Virtual.vmdf’,
MOVE N’TestDB_Log’ TO N’F:\VirtualRestore\TestDB_Log_TestDB_Virtual.vldf’, NORECOVERY, STATS=1,REPLACE
GORESTORE DATABASE [TestDB_Virtual] WITH RECOVERY, RESTRICTED_USER
GODBCC CHECKDB ([TestDB_Virtual])
GODROP DATABASE [TestDB_Virtual]
GO
This script did not work when we ran it via T-SQL because of a lack of disk space which is the same problem we encountered using the Backup Pro restore script, however it did work with the Red Gate Virtual Restore Wizard. We contacted support to find out why there was a difference on the same box. Basically SQL Server does not know that the HyperBac service is running in the background. The wizard is smart enough to check your disk space and if you do not have enough it temporarily adds an entry into the HyperBac configuration that tricks SQL Server into thinking that you have the right amount of disk space in order to complete the task.
The parameter is “VirtualDiskSize=” where the value is an integer to represent the disk size in megabytes. You add the parameter to the bottom of the to the hyper.conf file found in the C:\Program Files (x86)\Red Gate\HyperBac\Bin folder. At this point then you restart the HyperBac service and the problem is solved! Next I will try to automate this script….stay tuned!
Additional Goals for 2013
Yesterday I posted my personal development plan for 2013. After reading a few other similar blogs by colleagues yesterday, one haunting thought stayed with me all night that I couldn’t quite put my finger on until this morning. My goals were lofty, but they were no more so than last year. That would not be stepping out of my comfort zone. There was really no challenge in the list when compared to 2012, where there were some serious steps on the ledge for me.
How do I resolve this? Not sure at this point, but I have some ideas so this may evolve over the next week. The first idea I had would be to upgrade my SQL Server 2008 MCTS to the new MCSA 2012 (Microsoft Certified Solutions Associate). This would consist of two exams, 70-457 and 70-458, both aptly titled ‘Transition Your MCTS on SQL Server 2008 to MCSA SQL Server 2012, Part 1 and 2.’ I waited for all of the dust to settle with the new certifications before I pursued moving forward in my personal growth as it does not appear that I am anywhere ready to tackle the MCM, thus I have made it a five-year goal. This would put me on that path and force me to commit to the preparation needed to pass these exams. Now that I have talked this out with you here I think I will add these to the list! Enjoy!
SQL Swampland 