SQL Server with Mr. Denny | 3/15/2010 11:19:49 PM
I read this on the net tonight and thought it was an awesome idea. A friend and colleague at NetApp let me know they were doing a drive for St. Baldricks – a campaign where people shave their heads to help raise funds for fighting cancer in children. So, in the spirit of [...]
SQLServerPedia | 3/15/2010 9:59:20 PM
Today I got home and found a package on the steps…in the rain. The box was addressed to me, had yellow police tape across the top, and a green sticker that said ‘AUTHORS COPIES’. I brought the box into the house and eventually opened it. You can get more of an idea how I felt by [...]
Glenn Berry's SQL Server Performance | 3/15/2010 9:34:26 PM
After creating a Utility Control Point (UCP) you will want to enroll some managed instances (up to 25 with SQL Server 2008 R2 Enterprise Edition) into your Utility Control Point. Remember, any managed instances must also be SQL Server 2008 R2 (as of the November 2009 CTP of SQL Server 2008 R2). You also have to have TCP/IP enabled and you must have SQL Agent running with the startup type set to Automatic for each managed instance. In SQL Server Management Studio (SSMS), go to the View menu, and select “Utility Explorer”. In the left hand tree-view, right-click on Managed Instances, and select “Enroll Instance”. You will see the Introduction screen for the Enroll Instance wizard. Click “Next”, and you will see the “Specify the Instance of SQL Server” screen. Enter the name of the instance you want to enroll, and the click on Connect to enter the credentials needed to connect to that instance. Once this is complete, click “Next”, and you will see the “Utility Collection Set Account” screen. Enter a Windows domain account and password that will be used as the SQL Server Agent proxy account for the utility collection set, and collect “Next”. You will see the the “SQL Server Instance Validation” start, which will take 20-30 seconds to complete. If the instance passes all of the validation tests with no errors (warnings are ok, but you should investigate those), you should click on “Next”. You will see the “Summary of Instance Enrollment” screen. Click on “Next”, and you will see the “Enrollment of SQL Server Instance” screen. It may take a few seconds for this to complete. Once the enrollment process has completed, click on “Finish”. You will see a summary of all of your managed instances (including the UCP itself). You will not see any data from the newly managed instance for at least 10-15 minutes. You can continue to add additional managed instances until you run into the limit of 25 managed instances per UCP. Microsoft estimates that each managed instance will generate roughly 2GB of data per year in the management data warehouse database on the UCP instance.
After creating a Utility Control Point (UCP) you will want to enroll some managed instances (up to 25 with SQL Server 2008 R2 Enterprise Edition) into your Utility Control Point. Remember, any managed instances must also be SQL Server 2008 R2 (as of the November 2009 CTP of SQL Server 2008 R2). You also have to have TCP/IP enabled and you must have SQL Agent running with the startup type set to Automatic for each managed instance.
In SQL Server Management Studio (SSMS), go to the View menu, and select “Utility Explorer”. In the left hand tree-view, right-click on Managed Instances, and select “Enroll Instance”. You will see the Introduction screen for the Enroll Instance wizard.
Click “Next”, and you will see the “Specify the Instance of SQL Server” screen.
Enter the name of the instance you want to enroll, and the click on Connect to enter the credentials needed to connect to that instance. Once this is complete, click “Next”, and you will see the “Utility Collection Set Account” screen.
Enter a Windows domain account and password that will be used as the SQL Server Agent proxy account for the utility collection set, and collect “Next”. You will see the the “SQL Server Instance Validation” start, which will take 20-30 seconds to complete.
If the instance passes all of the validation tests with no errors (warnings are ok, but you should investigate those), you should click on “Next”. You will see the “Summary of Instance Enrollment” screen.
Click on “Next”, and you will see the “Enrollment of SQL Server Instance” screen. It may take a few seconds for this to complete.
Once the enrollment process has completed, click on “Finish”. You will see a summary of all of your managed instances (including the UCP itself). You will not see any data from the newly managed instance for at least 10-15 minutes.
You can continue to add additional managed instances until you run into the limit of 25 managed instances per UCP. Microsoft estimates that each managed instance will generate roughly 2GB of data per year in the management data warehouse database on the UCP instance.
SQLServerCentral.com | 3/15/2010 9:20:47 PM
Join us this Thursday, March 18th for our monthly NTSSUG meeting. SQL Server MVP Sean McCown will be continuing his series on Ground Zero SSIS at 6:00pm. Our guest speaker will be Drew Minkin, who will be presenting a session on SQL Server data mining: Data Mining for SQL Developers : This session will introduce SQL Developers to how SQL Server Analysis Services (SSAS)'s Data Mining algorithms can enhance your applications. Topics covered will include basics of data mining theories...(read more)
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 8:55:00 PM
Tonight I spoke at the SNESSUG user group meeting in Warwick, RI. You can download the slide deck here (this is a 3.5 MB PDF with presenter notes): http://sqlblog.com/files/folders/23423/download.aspx If you attended the talk, please feel free to provide feedback at speakerrate.com: http://speakerrate.com/talks/2849-management-studio-tips-tricks Today also happened to be a birthday celebration for Grant Fritchey ( blog | twitter ). He blogged about the meeting and also took a picture of the cake...(read more)
Journey to SQL Authority with Pinal Dave | 3/15/2010 8:30:21 PM
Just a day ago, I was invited at Round Table meeting at prestigious organization. They were planning to implement High Availability solution using Database Mirroring. During the meeting, I have made few notes of what was being discussed there. I just thought it would be interested for all of you know about it.Database Mirroring works [...]
SQLServerPedia | 3/15/2010 6:17:00 PM
Aaron Bertrand showed up to teach us tips and tricks for SQL Server Management Studio. We had to move our meeting night because of a conflict at our wonderful host, New England Tech. But we still had 12 people show up. For SNESSUG, that was a good turnout. I gave away some swag that I [...]
SQLServerPedia | 3/15/2010 5:41:00 PM
Microsoft provides an easy to use graphical user interface for most every server application, but such amenities are not immediately available in SharePoint 2007. The necessity to restore and recover a SharePoint farm in case of a disaster is a requirement of most, if not all, organizations. I was surprised to find that the ability to create and schedule such a task from a user interface was not included in SharePoint. After a week or so of manually backing up our SharePoint implementation and cleaning up the outdated backups I decided that it was time find a way to automate this task. The documentation for performing such a task is well outlined on-line using the stsadm executable. I was able to quickly and easily find documentation that met my needs of creating a scheduled backup of our SharePoint farm, but as the back-ups grew and space became an issue, I realized that I needed a more encompassing solution. I again found AWESOME documentation on how to maintain the backup files to only 2 days, but realized that this did not clear the history that SharePoint maintained and I was forced to deal with the spbrtoc.xml file, this is the xml file used to record the backup history of SharePoint. Some may find this a rather minor hurdle, but remember I deal mainly with SQL Server, relational, and working with xml, hieratical, can prove a bit frustrating. I would love nothing more than to provide my solution as I implemented it as MINE, but I must admit that a GREAT majority of the work was already done for me. I was able to find the online documentation “Clean up backup files (Windows SharePoint Services 3.0) “on TechNet which almost solved my entire automation conundrum:http://technet.microsoft.com/en-us/library/cc967308.aspx This vb script covered all of my requirements except:1. Required user input2. Did not include a backup command for SharePoint As I stated previously the above referenced TechNet link should be more than enough for most that run upon this post so I would highly recommend studying this. The below vb script will remove the folder(s) containing all backups that are more than two days old and uses the entries in the spbrtroc.xml to retrieve these directories. After the directories are removed the associated node(s) in the spbrtoc.xml file is removed thereby assuring that the entry will not be shown as an option to restore from the SharePoint central administration site. The final step is to call a command object that will run the stsadm executable to do a full farm backup:Set fso = CreateObject("Scripting.FileSystemObject")Set f = fso.GetFolder("\\server\Moss Backup")OlderThanDate = DateAdd("d", -2, Date)Set sf = f.SubFoldersFor Each f In sf If InStr(f, "spbr") Then If f.DateCreated < OlderThanDate Then f.Delete End If End IfNextDim dtDeleteDatedtDeleteDate = DateAdd("d",-2,Now)Set objXML = CreateObject("Microsoft.XMLDOM")Set objFS = CreateObject("Scripting.FileSystemObject")Set objLog = objFS.OpenTextFile("BackupCleanUp.log",8,True) ' Load SharePoint Backup and Restore TOC FileobjXML.Async = FalseobjXML.Load("\\server\Moss Backup\spbrtoc.xml")If objXML.ParseError.ErrorCode <> 0 Then objLog.WriteLine(Now() &vbTab& "Error: Could not load the SharePoint Backup / Restore History." & vbCrLf&_ Now() &vbTab& "Reason: " & objXML.ParseError.Reason& ".") WScript.QuitEnd If' Delete Backup Nodes Older Than Deletion DateFor Each objNode In objXML.DocumentElement.ChildNodes If CDate(objNode.SelectSingleNode("SPFinishTime").Text) < dtDeleteDate Then If objNode.SelectSingleNode("SPIsBackup").Text = "True" Then Dim f f = Left(objNode.SelectSingleNode("SPBackupDirectory").Text, Len(objNode.SelectSingleNode("SPBackupDirectory").Text), -1) objFS.DeleteFolder(f) objLog.WriteLine(Now() &vbTab& "Deleted: " & objNode.SelectSingleNode("SPBackupDirectory").Text & ".") objXML.DocumentElement.RemoveChild(objNode) End If End IfNext' Save XML File With Old Nodes RemovedobjXML.Save("\\server\Moss Backup\spbrtoc.xml")objLog.WriteLine(Now() &vbTab& "Finish: Completed backup clean up.")Set fso = CreateObject("Scripting.FileSystemObject")Set f = fso.GetFolder("\\server\MOSS Backup")OlderThanDate = DateAdd("d", -2, Date)Dim sfSet sf = f.SubFoldersFor Each f In sf If InStr(f, "spbr") Then If f.DateCreated < OlderThanDate Then f.Delete End If End IfNextSet objShell = CreateObject("WScript.Shell")objShell.Run "STSADM.exe -o backup -directory ""\\server\Moss Backup"" -backupmethod full" After completing the script and testing I placed the .vbs file in the SharePoint bin folder, where the stsadm is located, and scheduled it using Scheduled tasks. Further documentation on backing up SharePoint can be found here:http://msdn.microsoft.com/en-us/library/cc264321(office.14).aspx I am sure that this will come to no surprise to many, but my follow up post will be doing the same actions using an SSIS package, and YES I will include C# code.
Set fso = CreateObject("Scripting.FileSystemObject")Set f = fso.GetFolder("\\server\Moss Backup")OlderThanDate = DateAdd("d", -2, Date)Set sf = f.SubFoldersFor Each f In sf If InStr(f, "spbr") Then If f.DateCreated < OlderThanDate Then f.Delete End If End IfNextDim dtDeleteDatedtDeleteDate = DateAdd("d",-2,Now)Set objXML = CreateObject("Microsoft.XMLDOM")Set objFS = CreateObject("Scripting.FileSystemObject")Set objLog = objFS.OpenTextFile("BackupCleanUp.log",8,True) ' Load SharePoint Backup and Restore TOC FileobjXML.Async = FalseobjXML.Load("\\server\Moss Backup\spbrtoc.xml")If objXML.ParseError.ErrorCode <> 0 Then objLog.WriteLine(Now() &vbTab& "Error: Could not load the SharePoint Backup / Restore History." & vbCrLf&_ Now() &vbTab& "Reason: " & objXML.ParseError.Reason& ".") WScript.QuitEnd If' Delete Backup Nodes Older Than Deletion DateFor Each objNode In objXML.DocumentElement.ChildNodes If CDate(objNode.SelectSingleNode("SPFinishTime").Text) < dtDeleteDate Then If objNode.SelectSingleNode("SPIsBackup").Text = "True" Then Dim f f = Left(objNode.SelectSingleNode("SPBackupDirectory").Text, Len(objNode.SelectSingleNode("SPBackupDirectory").Text), -1) objFS.DeleteFolder(f) objLog.WriteLine(Now() &vbTab& "Deleted: " & objNode.SelectSingleNode("SPBackupDirectory").Text & ".") objXML.DocumentElement.RemoveChild(objNode) End If End IfNext' Save XML File With Old Nodes RemovedobjXML.Save("\\server\Moss Backup\spbrtoc.xml")objLog.WriteLine(Now() &vbTab& "Finish: Completed backup clean up.")Set fso = CreateObject("Scripting.FileSystemObject")Set f = fso.GetFolder("\\server\MOSS Backup")OlderThanDate = DateAdd("d", -2, Date)Dim sfSet sf = f.SubFoldersFor Each f In sf If InStr(f, "spbr") Then If f.DateCreated < OlderThanDate Then f.Delete End If End IfNextSet objShell = CreateObject("WScript.Shell")objShell.Run "STSADM.exe -o backup -directory ""\\server\Moss Backup"" -backupmethod full"
SQL Server Community Blogs | 3/15/2010 5:23:00 PM
At the VBUG meeting in Manchester on 3rd March, I was scheduled to talk about Table Valued Parameters, but when I got there the guys wanted something more general so I talked about some of the new features of SQL 2008. The presentation is here and the TVP demo project is here . My blog postings on TVPs are listed at http://sqlblogcasts.com/blogs/martinbell/archive/tags/TVP/default.aspx . Information about the new date and time data types http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/15...(read more)
SQLServerCentral.com | 3/15/2010 5:16:13 PM
On Saturday the 27th of March, I’m going to be presenting two sessions at SQLSaturday #29 in Birmingham, Alabama. 1:30 PM: Tips and Tricks for Writing Better Queries. Transact-SQL is not a very difficult language to learn. As long as the syntax is correct, it can be quite forgiving. However to truly get the best performance from your SQL Server, careful consideration should be given to the structure and logic of the queries. In this session, we’ll discuss some Transact-SQL tips and tricks that can be employed to help you write better queries, allowing your server to perform better. 2:45 PM: SQL Server Locking & Blocking Made Simple. A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times. If you’re not familiar with SQLSaturdays, you can learn more about them here. I hope to see you there!
On Saturday the 27th of March, I’m going to be presenting two sessions at SQLSaturday #29 in Birmingham, Alabama.
If you’re not familiar with SQLSaturdays, you can learn more about them here.
I hope to see you there!
SQLServerCentral.com | 3/15/2010 4:51:56 PM
The Denver SQL Server User’s Group will be having their March meeting on Thursday, March 18. Bulent Gucuk will discuss SQL Server Replication basics. The second speaker will be Dean Richards of Confio Software, giving a presentation on using Wait Time Analysis. The meeting will be held at Ameriteach, at the address below: 7800 East Dorado Place, Suite 150Greenwood Village, CO 80111 Map/Directions
The Denver SQL Server User’s Group will be having their March meeting on Thursday, March 18. Bulent Gucuk will discuss SQL Server Replication basics. The second speaker will be Dean Richards of Confio Software, giving a presentation on using Wait Time Analysis.
The meeting will be held at Ameriteach, at the address below:
7800 East Dorado Place, Suite 150Greenwood Village, CO 80111 Map/Directions
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 3:18:00 PM
Earlier today, MSSQLTips posted a backup compression tip by Thomas LaRock ( blog | twitter ). In that article, Tom states: "If you are already compressing data then you will not see much benefit from backup compression." I don't want to argue with a rock star, and I will concede that he may be right in some scenarios. Nonetheless, I tweeted that "it depends;" Thomas then asked for "an example where you have data comp and you also see a large benefit from backup comp?" My initial reaction came about...(read more)
SQLServerCentral.com | 3/15/2010 1:28:00 PM
If you are a regular to the sql server community sites, forums and blogs, then you know about the awesome stuff brought to you by SQL MVP Muthusamy Anantha Kumar(a.k.a. MAK ). Well, if you're in the NY/NJ area, be sure not to miss tomorrow's evening's presentation at the NJ User Group HQ. (Sorry for the short notice!)Tuesday, March 16, 2010 - 6PM This session will demonstrate the various legal compliance requirements / controls such as Sarbanes Oxley, SAS 70, etc and how to make use of SQL Server 2008 Auditing features to achieve it. This includes different types Auditing - Server level, Database Level and Audit level. This demo also includes auditing steps like auditing a domain group, Auditing job changes etc. Details & Register: http://tinyurl.com/yzlfmnf Bio: Muthusamy Anantha Kumar(a.k.a. MAK - http://www.mssqlengineering.com)MAK is a Microsoft Certified IT Professional - Database Administrator ( MCITP SQL Server 2008, MCITP SQL Server 2005) . He has more than 11 years of experience in Information Technology including Database Engineering, Database Administration, System Analysis, Design, Development and Support of MS SQL Server 2008/2005/2000/7.0/6.5/6.0/4.X for production/development/testing. He is experienced in MS SQL Server Engineering, installation, configuration, performance tuning, client/server connectivity, query optimization, back-up/recovery. He has handled VLDBs and been involved in SDLC of various data warehouse projects. Currently, MAK is Senior Database Engineer responsible for Engineering and standardizing Microsoft SQL Servers in one of the big finance companies on Wall Street. Previously, he was a Senior Database Administrator/Data Architect, responsible for development, test, DR and production servers for many finance , .com and B2B companies. He plays an active role in forums as a SQL Expert and moderator. He also teaches Database Administration at University of West Florida. He also teaches SQL Server part time in New Jersey. He published many online articles and he is a contributing columnist in DatabaseJournal.com. He is also publishing online articles in SQL-Server-Performance.com and SQLServercentral.com.
If you are a regular to the sql server community sites, forums and blogs, then you know about the awesome stuff brought to you by SQL MVP Muthusamy Anantha Kumar(a.k.a. MAK ). Well, if you're in the NY/NJ area, be sure not to miss tomorrow's evening's presentation at the NJ User Group HQ. (Sorry for the short notice!)
Tuesday, March 16, 2010 - 6PM This session will demonstrate the various legal compliance requirements / controls such as Sarbanes Oxley, SAS 70, etc and how to make use of SQL Server 2008 Auditing features to achieve it. This includes different types Auditing - Server level, Database Level and Audit level. This demo also includes auditing steps like auditing a domain group, Auditing job changes etc. Details & Register: http://tinyurl.com/yzlfmnf
Bio: Muthusamy Anantha Kumar(a.k.a. MAK - http://www.mssqlengineering.com)MAK is a Microsoft Certified IT Professional - Database Administrator ( MCITP SQL Server 2008, MCITP SQL Server 2005) . He has more than 11 years of experience in Information Technology including Database Engineering, Database Administration, System Analysis, Design, Development and Support of MS SQL Server 2008/2005/2000/7.0/6.5/6.0/4.X for production/development/testing. He is experienced in MS SQL Server Engineering, installation, configuration, performance tuning, client/server connectivity, query optimization, back-up/recovery. He has handled VLDBs and been involved in SDLC of various data warehouse projects. Currently, MAK is Senior Database Engineer responsible for Engineering and standardizing Microsoft SQL Servers in one of the big finance companies on Wall Street. Previously, he was a Senior Database Administrator/Data Architect, responsible for development, test, DR and production servers for many finance , .com and B2B companies. He plays an active role in forums as a SQL Expert and moderator. He also teaches Database Administration at University of West Florida. He also teaches SQL Server part time in New Jersey. He published many online articles and he is a contributing columnist in DatabaseJournal.com. He is also publishing online articles in SQL-Server-Performance.com and SQLServercentral.com.
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 12:05:19 PM
Oracle hasn’t yet released versions of its client software for Windows Server 2008 R2. If you need to create an Oracle linked server, that’s a problem. You’ll see this installation block when attempting to install the Oracle client software for Windows Server 2008: It’s very simple to fix. Check the first checkbox to make the installer ignore the version check. Click Next and ignore the warning you’ll see. The installation should complete successfully. Windows does offer various strategies for mitigating...(read more)
SQLServerPedia | 3/15/2010 12:00:00 PM
A while ago I had read about the hidden threat of forwarded records. These silent killers are like storm troopers in the bush waiting to get you when you least expect it. And then the other day I saw a T-SQL Tuesday post by Sankar Reddy on (blog – twitter) on determining if your SQL Server [...]
A while ago I had read about the hidden threat of forwarded records. These silent killers are like storm troopers in the bush waiting to get you when you least expect it.
And then the other day I saw a T-SQL Tuesday post by Sankar Reddy on (blog – twitter) on determining if your SQL Server [...]
SQLServerPedia | 3/15/2010 11:16:13 AM
On Saturday the 27th of March, I’m going to be presenting two sessions at SQLSaturday #29 in Birmingham, Alabama.1:30 PM: Tips and Tricks for Writing Better Queries. Transact-SQL is not a very difficult language to learn. As long as the syntax is correct, it can be quite forgiving. However to truly get the best performance [...]
Glenn Berry's SQL Server Performance | 3/15/2010 10:51:56 AM
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 10:40:00 AM
We have just two weeks to go before Paul Randal and Kimberly Tripp touch down in the Boston area to deliver their famous SQL Server Immersions course . This is going to be a truly fantastic SQL Server learning experience and we're hoping a few more people will join in the fun. This is where you come in: we have a few vacant seats remaining and we need your help spreading the word. Simply tell your friends and colleagues about the course and e-mail me (adam [at] bostonsqltraining [dot] com) the names...(read more)
Carpe Datum | 3/15/2010 10:09:00 AM
There's already a post on this topic - sort of. I read this entry, where the author did a good job on a few steps, but I found that a few other tips might be useful, so if you want to check that one out and then this post, you might be able to put together your own plan for when you leave your job. I once took over the system administrator (of which the Oracle and SQL Server servers were a part) at a mid-sized firm. The outgoing administrator had about a two- week-long scheduled overlap with me, but was angry at the company and told me "hey, I know this is going to be hard on you, but I want them to know how important I was. I'm not telling you where anything is or what the passwords are. Good luck!" He then quit that day.It took me about three days to find all of the servers and crack the passwords. Yes, the company tried to take legal action against the guy and all that, but he moved back to his home country and so largely got away with it.Obviously, this isn't the way to leave a job. Many of us have changed jobs in the past, and most of us try to be very professional about the transition to a new team, regardless of the feelings about a particular company. I've been treated badly at a firm, but that is no reason to leave a mess for someone else. So here's what you should put into place at a minimum before you go. Most of this is common sense - which of course isn't very common these days - and another good rule is just to ask yourself "what would I want to know"?The article I referenced at the top of this post focuses on a lot of documentation of the systems. I think that's fine, but in actuality, I really don't need that. Even with this kind of documentation, I still perform a full audit on the systems, so in the end I create my own system documentation. There are actually only four big items I need to know to get started with the systems:1. Where is everything/everybody?The first thing I need to know is where all of the systems are. I mean not only the street address, but the closet or room, the rack number, the IU number in the rack, the SAN luns, all that. A picture here is worth a thousand words, which is why I really like Visio. It combines nice graphics, full text and all that. But use whatever you have to tell someone the physical locations of the boxes. Also, tell them the physical location of the folks in charge of those boxes (in case you aren't) or who share that responsibility. And by "where" in this case, I mean names and phones. 2. What do they do?For both the servers and the people, tell them what they do. If it's a database server, detail what each database does and what application goes to that, and who "owns" that application. In my mind, this is one of hte most important things a Data Professional needs to know. In the case of the other administrtors or co-owners, document each person's responsibilities. 3. What are the credentials?Logging on/in and gaining access to the buildings are things that the new Data Professional will need to do to successfully complete their job. This means service accounts, certificates, all of that. The first thing they should do, of course, is change the passwords on all that, but the first thing they need is the ability to do that! 4. What is out of the ordinary?This is the most tricky, and perhaps the next most important thing to know. Did you have to use a "special" driver for that video card on server X? Is the person that co-owns an application with you mentally unstable (like me) or have special needs, like "don't talk to Buck before he's had coffee. Nothing will make any sense"? Do you have service pack requirements for a specific setup? Write all that down. Anything that took you a day or longer to make work is probably a candidate here.This is my short list - anything you care to add?
There's already a post on this topic - sort of. I read this entry, where the author did a good job on a few steps, but I found that a few other tips might be useful, so if you want to check that one out and then this post, you might be able to put together your own plan for when you leave your job.
I once took over the system administrator (of which the Oracle and SQL Server servers were a part) at a mid-sized firm. The outgoing administrator had about a two- week-long scheduled overlap with me, but was angry at the company and told me "hey, I know this is going to be hard on you, but I want them to know how important I was. I'm not telling you where anything is or what the passwords are. Good luck!" He then quit that day.
It took me about three days to find all of the servers and crack the passwords. Yes, the company tried to take legal action against the guy and all that, but he moved back to his home country and so largely got away with it.
Obviously, this isn't the way to leave a job. Many of us have changed jobs in the past, and most of us try to be very professional about the transition to a new team, regardless of the feelings about a particular company. I've been treated badly at a firm, but that is no reason to leave a mess for someone else. So here's what you should put into place at a minimum before you go. Most of this is common sense - which of course isn't very common these days - and another good rule is just to ask yourself "what would I want to know"?
The article I referenced at the top of this post focuses on a lot of documentation of the systems. I think that's fine, but in actuality, I really don't need that. Even with this kind of documentation, I still perform a full audit on the systems, so in the end I create my own system documentation. There are actually only four big items I need to know to get started with the systems:
1. Where is everything/everybody?The first thing I need to know is where all of the systems are. I mean not only the street address, but the closet or room, the rack number, the IU number in the rack, the SAN luns, all that. A picture here is worth a thousand words, which is why I really like Visio. It combines nice graphics, full text and all that. But use whatever you have to tell someone the physical locations of the boxes. Also, tell them the physical location of the folks in charge of those boxes (in case you aren't) or who share that responsibility. And by "where" in this case, I mean names and phones.
2. What do they do?For both the servers and the people, tell them what they do. If it's a database server, detail what each database does and what application goes to that, and who "owns" that application. In my mind, this is one of hte most important things a Data Professional needs to know. In the case of the other administrtors or co-owners, document each person's responsibilities.
3. What are the credentials?Logging on/in and gaining access to the buildings are things that the new Data Professional will need to do to successfully complete their job. This means service accounts, certificates, all of that. The first thing they should do, of course, is change the passwords on all that, but the first thing they need is the ability to do that!
4. What is out of the ordinary?This is the most tricky, and perhaps the next most important thing to know. Did you have to use a "special" driver for that video card on server X? Is the person that co-owns an application with you mentally unstable (like me) or have special needs, like "don't talk to Buck before he's had coffee. Nothing will make any sense"? Do you have service pack requirements for a specific setup? Write all that down. Anything that took you a day or longer to make work is probably a candidate here.
This is my short list - anything you care to add?
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 10:00:00 AM
In my most recent posts I have looked at a few of the new features offered in T-SQL in SQL Server 2008. In this post, I want to take a closer look at some of the smaller additions, but additions that are likely to pack a big punch in terms of efficiency. First let’s talk a little about compound operators. This is a concept that has been around in programming languages for a long time, but has just now found its way into T-SQL. For example, the += operator will add the values to the current variable...(read more)
SQLServerCentral.com | 3/15/2010 10:00:00 AM
This is probably part 1 of a series, but no guarantees.Why Certify?I've been working with SQL Server for 10 years and for most of that time I've thought certification was unnecessary. When I first started out my boss was not a fan of MS certifications, so I wasn't interested either. Once I had some experience, I thought that my experience was enough. So what changed my mind? I moved and got to know people who, in my opinion, know a lot more about SQL Server than I do, so I was challenged to "up my game" and one way to do that is through certification. The certification requires that I study about areas of SQL Server that I have not had the opportunity to work with or needed to know about, areas like clustering, log-shipping, online restores, to name just a few. Another reason is that I enjoy presenting (teaching) and I think I'd like to continue to do that and maybe make some money doing it. So, I decided that one way to do that is to become and MCT (Microsoft Certified Trainer) and in order to do that you need to be an MCITP. Finally, after having already decided to go down the certification path, I read this article in ComputerWorld, Opinion: Certifications are no longer Optional. I don't know how accurate that might be, but I also think that certifications can't hurt me. So What Have I Done?I recently took (and passed) 70-432: TS: Microsoft SQL Server 2008 Implementation and Maintenance. PreparationI prepared for the test by going through Buck Woody's (@BuckWoody) blog series on his preparation for this test. I also purchased the MCTS Self-Pace Training Kit (Exam 70-432): Microsoft SQL Server 2008-Implementation and Maintenance. I purchased the book because it comes with practice test software which I thought would be valuable. The training kit was helpful, but a little shallow, I really expected it to be delve deeper into the subjects. The case study presented at the end of each chapter was the most helpful part of the book as it really caused me to think about what I thought was the best way to solve the problem(s) presented. I actually had a harder time doing the practice test(s) than I did with the real test. I don't think I passed the real test because of this book, but it did help augment my experience and the other resources.The TestHaving never taken a certification test before and this being about 15 years after the last test I took that actually meant anything, I was a bit concerned about being able to pass. This concern was based on the fact that I had NOT passed a practice test. I'll share that I have always been a good test taker, especially when the test is multiple choice, which the certification test is. I got to the test center about 30 minutes before the test was scheduled (as recommended) and was registered and at the test station about 20 minutes later. Now, the recommendation is to allow 2 hours and 45 minutes for the test so I expected a long test. Well, I was leaving the test center 40 minutes after I sat down at the test station. During that time I did the practice test, as it was my first time, did the pre-test MS survey, took the test, reviewed every question on the test, took the post-test MS survey, and the post-test Prometric survey. You can decide how hard the test was. Test ValueThe test definitely covered all the areas it said it would cover, but I thought it should have been twice as long as there was not enough depth to the questions. I am pretty sure I could have passed without studying. I definitely could have passed without studying as much as I did. Would I expect someone who passed this test to be able to sit down and set up a cluster or replication? No. I would expect to be able to give them a scenario and have them give me an basic solution that would be meet business requirements around availability and recovery. So it has some value, but could be better.What's Next?I've scheduled 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008. I'll have another post about this test when I get there.
This is probably part 1 of a series, but no guarantees.
I've been working with SQL Server for 10 years and for most of that time I've thought certification was unnecessary. When I first started out my boss was not a fan of MS certifications, so I wasn't interested either. Once I had some experience, I thought that my experience was enough. So what changed my mind? I moved and got to know people who, in my opinion, know a lot more about SQL Server than I do, so I was challenged to "up my game" and one way to do that is through certification. The certification requires that I study about areas of SQL Server that I have not had the opportunity to work with or needed to know about, areas like clustering, log-shipping, online restores, to name just a few.
Another reason is that I enjoy presenting (teaching) and I think I'd like to continue to do that and maybe make some money doing it. So, I decided that one way to do that is to become and MCT (Microsoft Certified Trainer) and in order to do that you need to be an MCITP.
Finally, after having already decided to go down the certification path, I read this article in ComputerWorld, Opinion: Certifications are no longer Optional. I don't know how accurate that might be, but I also think that certifications can't hurt me.
I recently took (and passed) 70-432: TS: Microsoft SQL Server 2008 Implementation and Maintenance.
Preparation
I prepared for the test by going through Buck Woody's (@BuckWoody) blog series on his preparation for this test. I also purchased the MCTS Self-Pace Training Kit (Exam 70-432): Microsoft SQL Server 2008-Implementation and Maintenance. I purchased the book because it comes with practice test software which I thought would be valuable. The training kit was helpful, but a little shallow, I really expected it to be delve deeper into the subjects. The case study presented at the end of each chapter was the most helpful part of the book as it really caused me to think about what I thought was the best way to solve the problem(s) presented. I actually had a harder time doing the practice test(s) than I did with the real test. I don't think I passed the real test because of this book, but it did help augment my experience and the other resources.
The Test
Having never taken a certification test before and this being about 15 years after the last test I took that actually meant anything, I was a bit concerned about being able to pass. This concern was based on the fact that I had NOT passed a practice test. I'll share that I have always been a good test taker, especially when the test is multiple choice, which the certification test is. I got to the test center about 30 minutes before the test was scheduled (as recommended) and was registered and at the test station about 20 minutes later. Now, the recommendation is to allow 2 hours and 45 minutes for the test so I expected a long test. Well, I was leaving the test center 40 minutes after I sat down at the test station. During that time I did the practice test, as it was my first time, did the pre-test MS survey, took the test, reviewed every question on the test, took the post-test MS survey, and the post-test Prometric survey. You can decide how hard the test was.
Test Value
The test definitely covered all the areas it said it would cover, but I thought it should have been twice as long as there was not enough depth to the questions. I am pretty sure I could have passed without studying. I definitely could have passed without studying as much as I did. Would I expect someone who passed this test to be able to sit down and set up a cluster or replication? No. I would expect to be able to give them a scenario and have them give me an basic solution that would be meet business requirements around availability and recovery. So it has some value, but could be better.
I've scheduled 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008. I'll have another post about this test when I get there.
SQLServerCentral.com | 3/15/2010 9:37:00 AM
Each time a query is executed, the SQL Server engine, by default, will detect the best degree of parallelism (DOP), that is, it will determine the ideal number of processors required. The DOP is based on the estimated elapsed time or cost of the different tasks the engine will have to process. In most configurations the DOP is not controlled by the database administrator. For high-end configurations, it is informative to force the number of processors to be used for two reasons:• To determine the ideal DOP for a given query, the engine needs to calculate the cost of the query.This process could generate an extra cost, especially with an OLTP workload where typically only short T-SQL statements (inserts, updates, and deletes) are found.• To control the resources assigned to a workload, for example, if you do not want complex queries to use all the CPU resources of your SQL Server.The DOP is usually defined at the instance level. It is obviously not the best option because not all the queries will need the same numbers of CPUs to be processed. Another option is to define the DOP in the query itself using query hints:select count(*) from dbo.telco_fact32 option (MAXDOP 1)This could be challenging because you must have control of the application. The last option is to use plan guides to force the DOP for previously identified queries.
SQLServerCentral.com | 3/15/2010 9:31:00 AM
Many companies face the decisions to 1) consolidate their SQL Servers, due to sql sprawl; and 2) whether to virtualize their SQL environment by placing muliple SQL Servers on one powerful physical box. Here is an interesting whitepaper, sponsored by Dell & AMD, that discusses how to Consolidate SQL Server 2008 on Dell 4 Socket - Multi-Core Servers - Using Hyper-V IT pros have the difficult job of consolidating multiple SQL Server 2008 instances onto a single physical system and the problems compound in a virtual environment. This white paper discusses how to effectively virtualize SQL Server 2008 on the Hyper-V platform. Explore the concepts and procedures essential for migration of your SQL Server databases to a virtualized environment. Discover an overview of basic general virtualization and consolidation processes, and gain insight into how to set up and configure a virtual server.
Many companies face the decisions to 1) consolidate their SQL Servers, due to sql sprawl; and 2) whether to virtualize their SQL environment by placing muliple SQL Servers on one powerful physical box. Here is an interesting whitepaper, sponsored by Dell & AMD, that discusses how to Consolidate SQL Server 2008 on Dell 4 Socket - Multi-Core Servers - Using Hyper-V
Find out how to consolidate SQL Server 2008 in a virtual environment: http://tinyurl.com/ye2slrw with this great whitepaper. I hope this information is helpful!
SQLblog.com - The SQL Server blog spot on the web | 3/15/2010 9:09:00 AM
There's already a post on this topic - sort of. I read this entry, where the author did a good job on a few steps, but I found that a few other tips might be useful, so if you want to check that one out and then this post, you might be able to put together your own plan for when you leave your job. I once took over the system administrator (of which the Oracle and SQL Server servers were a part) at a mid-sized firm. The outgoing administrator had about a two- week-long scheduled overlap with me, but was angry at the company and told me "hey, I know this is going to be hard on you, but I want them to know how important I was. I'm not telling you where anything is or what the passwords are. Good luck!" He then quit that day.It took me about three days to find all of the servers and crack the passwords. Yes, the company tried to take legal action against the guy and all that, but he moved back to his home country and so largely got away with it.Obviously, this isn't the way to leave a job. Many of us have changed jobs in the past, and most of us try to be very professional about the transition to a new team, regardless of the feelings about a particular company. I've been treated badly at a firm, but that is no reason to leave a mess for someone else. So here's what you should put into place at a minimum before you go. Most of this is common sense - which of course isn't very common these days - and another good rule is just to ask yourself "what would I want to know"?The article I referenced at the top of this post focuses on a lot of documentation of the systems. I think that's fine, but in actuality, I really don't need that. Even with this kind of documentation, I still perform a full audit on the systems, so in the end I create my own system documentation. There are actually only four big items I need to know to get started with the systems:1. Where is everything/everybody?The first thing I need to know is where all of the systems are. I mean not only the street address, but the closet or room, the rack number, the IU number in the rack, the SAN luns, all that. A picture here is worth a thousand words, which is why I really like Visio. It combines nice graphics, full text and all that. But use whatever you have to tell someone the physical locations of the boxes. Also, tell them the physical location of the folks in charge of those boxes (in case you aren't) or who share that responsibility. And by "where" in this case, I mean names and phones. 2. What do they do?For both the servers and the people, tell them what they do. If it's a database server, detail what each database does and what application goes to that, and who "owns" that application. In my mind, this is one of hte most important things a Data Professional needs to know. In the case of the other administrtors or co-owners, document each person's responsibilities. 3. What are the credentials?Logging on/in and gaining access to the buildings are things that the new Data Professional will need to do to successfully complete their job. This means service accounts, certificates, all of that. The first thing they should do, of course, is change the passwords on all that, but the first thing they need is the ability to do that! 4. What is out of the ordinary?This is the most tricky, and perhaps the next most important thing to know. Did you have to use a "special" driver for that video card on server X? Is the person that co-owns an application with you mentally unstable (like me) or have special needs, like "don't talk to Buck before he's had coffee. Nothing will make any sense"? Do you have service pack requirements for a specific setup? Write all that down. Anything that took you a day or longer to make work is probably a candidate here.This is my short list - anything you care to add? Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
SQLServerCentral.com | 3/15/2010 9:05:00 AM
By default, SQL Server can potentially use all of the server’s memory and CPU resources. You can edit the instance properties and check the box for the “Automatically set processor affinity mask for all processors” option to allow the CPU’s resources to be shared when running multiple instances on one server. This could, however, impact the response time of one of the database engines because some of the resources could be used by another instance. The quality of service is then very difficult to guarantee. Instead of allowing the access to all the CPUs for all the SQL Server instances, you can limit the number of CPUs assigned. With this feature, you can dedicate CPUs for each instance. A benefit of CPU affinity is the ability to change the number of CPUs assigned to an instance dynamically without having to restart the instance. If you are not satisfied with the average response time of an instance, you can improve performance by removing CPUs from another instance and adding them to the slower instance. This configuration change can be achieved with SQL Server Management Studio by editing the instance properties or the following querysp_configure 'show advanced options', 1 reconfigure go sp_configure 'affinity mask', XXreconfigure go Need to run the perfmon and indentify the CPU utilization of a server running SQL Server under load where only the XX CPUs assigned with CPU affinity. are in use. Note that if SQL Server is licensed per processor (see Processor Licensing Model), the addition of CPUs with the CPU affinity feature may impact your licensing agreement. The figure shows the effect of the number of CPUs assigned to the SQL Server instance on the response time of a given query. Obviously the response time decreases while we add CPUs. This statement is true only if the SQL Server engine considers that the ideal number of CPUs to process the query is equal to the number of CPUs available on the server (degree of parallelism equals 0 (default) or equals the number of CPUs). For an OLTP workload that is executing one single thread, the CPU affinity will have no influence on the response time. In that case, we need to consider executing multiple threads concurrently so that the overall response time will be improved.Ideally, the CPU affinity should take into account the hardware layout of the server. The Non-Uniform Memory Access (NUMA) architecture is used on some of servers; therefore, it does not make sense to set the affinity for a SQL Server instance to two processors that do not belong to the same NUMA node. Note that when hyperthreading technology (HT) is enabled on some Intel processors, the processor enumeration performed by Windows Server is in a logical order. This fact should also be considered when you are deciding whether to use CPU affinity. Windows Server 2008 will enumerate a server with two Itanium II dual-core and HT-enabled processorsIn a cluster, you will have to adjust the CPU affinity settings to accommodate failover. The remaining nodes will have more workload to manage, especially if you have an active/active configuration. You may need to dispatch the remaining resources to fulfill your SLA after the failover event