Portillo’s beef sandwiches are best when the entire sandwich is dipped in gravy so the bread becomes gelatinous. (Used with permission)
SQL Saturday #211: Chicago 2013 is approaching quickly! I’ve gotten a few emails inquiring about what food will be served, and I’m very happy to announce that lunch for this year’s SQL Saturday Chicago will once again be catered by Portillo’s! The menu will consist of their famous Italian Beef sandwiches, salad, and Mostaccioli. We will have grilled veggie sandwiches available for those of you who requested a vegetarian option. Please PLEASE be sure to check the vegetarian meal box on your registration form if you need one. We will have very few extra veggie sandwiches (if any), so we need an accurate count to be able to plan ahead.
In other news, the schedule has now been posted. We have an amazing lineup of speakers this year, many of whom have traveled great distances to share their knowledge with you for free. Please help make sure their sessions are absolutely packed! Registration is still open, so sign up today if you haven’t already.
We’re looking forward to another great time! Hope to see you there!
Last week while at work I received a phone call from a recruiter. It was pretty standard until they decided to turn into a jerk. I sent the following letter to the recruiter’s agency. I doubt I’ll get a reply, but if I do I will be sure to share it here. Identifying info has been removed to protect the not-so-innocent.
I received a phone call at work last week from <redacted>, one of your recruiters. Getting phone calls from recruiters during the work day is a fairly common occurrence, but I’m sure you will agree that the rest of this call was very uncommon.
<Redacted> said their records showed I’ve been working for Northwestern University for nearly two years, and they were curious if I was interested in pursuing any new opportunities. I told them I was not interested at this time, but if they would be willing to send me a job description with a salary range I would be happy to forward it to others I know. I am fortunate to have lots of contacts in the SQL Server world, and I know a few who are currently seeking out new positions.
Most recruiters are happy to take me up on offers like this, and I have generated some interviews through this manner in the past. <Redacted> was clearly not as enthusiastic about this, as they snapped back with a “Well why aren’t you interested? How do you even know you’re not interested if I haven’t even told you about the position yet?” They proceeded to explain the position – a production DBA for a respected local company that’s been in the <X> industry for over <Y> years. I was happy to hear this was at least a position relevant to my experience – I’ve received calls in the past for things like “an exciting position staffing the overnight help desk at a company in [some other state]. And if you play your cards right, you’ll have the chance to move into a junior developer role after a few years!”
Still I told them I wasn’t interested, and this is where things got really strange. (I will paraphrase as best I can.) “Let me give you a piece of advice”, they said. “I’ve been an IT recruiter for <Z> years, and as long as you work for a University, I know for a fact that your career is going nowhere. I’m sure it’s comfortable there, but you’re not doing anything interesting, you’re not growing your skills, and you will find yourself with a whole lot less respect the next time you are looking to change employers.”
Wow. I told them I’m very sorry they feel that way, and that in fact I’m doing some incredibly interesting projects right now. “So what is it that you’re doing? I want to hear this” they said, their voice dripping with sarcasm. “Well you already told me what I’m doing is not interesting, so why should I waste my time explaining it?” I said. I thanked them for taking the time to call me, and said I needed to get back to not growing my skills. <Redacted> hung up before I had the opportunity to end our conversation. How professional of them.
Is this really the way you do business? I am no expert in the field of recruiting, but I’m pretty sure that insulting your product (yes, I do realize that I am the product, not the client) is not going to result in any placements or productive relationships in the future. If <redacted> has managed to stay in the business for <Z> years while acting like that, that’s pretty pathetic.
Despite what <redacted> told me, I happen to be furthering my career right now in ways that weren’t even possible in my previous positions. While I enjoy my current role and have no plans to leave it in the short term, I don’t plan to stay there forever either. There will come a time where I’ll find myself in need of new challenges. I sincerely hope that my next career move is to the type of position that’s not posted anywhere and hence I won’t be needing the services of a recruiter. But if that doesn’t happen, I am absolutely sure that I will never, ever, do business with <redacted> or your agency again. Please remove me from all of your directories and lists immediately.
SQL Server DBA with a career that’s “going nowhere”
Moral of the Story
First of all, the vast majority of recruiters are not like this. It is by far in a recruiter’s best interest to build a positive relationship with you, because even if the job they’re contacting you about isn’t something you’re interested in, chances are good that in the future they’ll find something that does interest you.
Second, there are lots of recruiters and job placement agencies out there. If you get a cold call from someone who’s being a jerk, I wouldn’t hesitate to tell them to remove you from their records and take your business elsewhere. Others will be more than happy to assist you in your job search.
It’s once again the second Tuesday of the month, meaning it’s T-SQL Tuesday! This month’s topic comes to us from Jen McCown (blog | @JenniferMcCown). Jen is asking us to share our knowledge about filegroups, and it just so happens that I have a story about migrating to new storage that involved a large number of file and filegroup operations.
At work we have been very fortunate to get a new SAN, and I was charged with moving data from the old SAN to the new one. This data falls under 2 main groups: large numbers of small files which can be moved at any time without consequence, and data files from the production SQL Server database. I covered how I moved the non-database files in a previous post on the Microsoft FCIV. This time I’ll cover the larger task of moving the databases. To make things much more difficult interesting, I did not have the luxury of being able to take any downtime to accomplish this move. Making use of pre-scheduled maintenance windows was fine, but those are typically 2 hours every 2-3 months, not nearly enough time to move the 22TB of data that was in need of migration.
Just to be clear, the instance of SQL Server was not moving or changing at all. The server was connected to both the old and new SANs, and the objective was to move a particular database from old to new. Yes, there are other databases on the server, but they are located on other storage and did not need to be moved. We use SQL Server 2008 R2 Enterprise Edition, so at least I had the full arsenal of functionality available to me.
I began listing out all the ways to move data that I could think of, and started disqualifying from there:
Copy the files. This would be easy and relatively fast, but requires taking the database offline to move the files. Since uptime is paramount, this is a no-go.
Online index rebuilds. Using enterprise edition means it’s possible to rebuild tables and indexes online without interrupting users. It is also possible to move objects to a new filegroup online as well. This is very useful, but comes with some limitations. Prior to SQL Server 2012, indexes containing LOB (large object) information such as columns of type image, ntext, text, varchar(max), nvarchar(max), varbinary(max) and xml can’t be rebuilt online. SQL Server 2012 has improved this feature so this limitation only applies to image, ntext, and text columns, which are deprecated anyway. But even though SQL Server 2012 lets you perform online rebuilds of tables with LOB data, it still won’t let you move the LOB data to a different filegroup. This solution gives us the uptime we require, but unfortunately can’t move everything.
Mirroring. Much like a rolling upgrade, mirroring can be used to migrate a database between instances. You can set up a mirroring session with the mirror server on the instance you want to migrate to, manually fail over, and you’re now on the new instance. Unfortunately it won’t work in this case because the primary and mirror databases can’t reside on the same instance.
Backup and restore onto the new SAN. This is a very strong candidate, especially since I can backup the database in about 3 hours and restore it almost as quickly. Restoring a backup on the new storage (with a different database name) can take place while the current database remains online. Cutting over would involve backing up the tail of the transaction log on the old database, restoring it on the new one, and then swapping names. The cutover would be quick enough to fit in a maintenance window, making this a very attractive option. I ended up choosing against it because the new storage would be partitioned differently than the old storage and adjusting the files and filegroups and then rebuilding objects across them would make the process more complicated than the solution I ended up choosing.
Add and remove files from the filegroup. This approach can be very useful, but rarely gets mentioned. SQL Server allows you to add and remove files from filegroups at almost any time, and this can be leveraged to move data between different storage systems. What makes it awesome is that the operation is completely online with no interruptions to the user, all types of data (including LOB data) is moved, and it works in any edition of SQL Server. As always, there are trade-offs. Shrinking data files is generally frowned upon as it causes high levels of fragmentation. It’s also very slow, and LOB data makes it even slower. Still, fragmentation can be solved using REBUILD or REORGANIZE, and if speed is not important, it could be the way to go.
The add/remove file method is best for preserving uptime, but due to its slow speed I decided to use a hybrid solution with 4 parts:
1. All tables & indexes that can be rebuilt online on a new filegroup are moved to a temporary filegroup on the new storage.
2. The remaining tables & indexes are moved via adding & removing files to the original filegroup.
3. The contents of the temporary filegroup are moved back into the original filegroup via online rebuilds.
4. The transaction log and primary filegroup (which cannot be moved by the add/remove method) are moved during a scheduled downtime.
The image below shows our starting state: the new SAN is empty, while the old SAN has a (non-primary) filegroup [FG1] with files A, B, and C in it, each 1GB in size. Most systems will contain more filegroups, but for simplicity I only show one here.
The first step was online index rebuilds. For each filegroup on the old SAN I created a temporary one on the new SAN. For example, in this case of having [FG1] on the old storage, I’d create filegroup [FG1_Temp] on the new storage. Then I did online rebuilds onto the temp filegroup for each table and index that would allow it. This can be an arduous task for filegroups with large numbers of objects, so I created a powershell script to automate this which I’m happy to share. Creating the new filegroup and files can be done via the following T-SQL:
The powershell script will move objects online from one filegroup to another. Objects which cannot be moved online will be ignored. When you open the script file you will see variables that need to be filled in for the server name, database name, and source and destination filegroups, and a folder to log to. Optionally you can filter by schema name, object name, and size.
I am happy to share this with the community, but please be advised of the following: This script needs to run in sqlps, the SQL Server powershell extension. It was written for and tested on SQL Server 2008 R2. I have not tested it on SQL Server 2012. I do not consider this to be perfect or in any sort of a finished state. And as always it is your responsibility to read and understand this code before running it on your own systems. Always test in a development environment, no warranty is expressed or implied, etc. etc., use at your own risk!
After the online filegroup migration, the storage layout will appear as follows, with a temporary filegroup on the New SAN:
Once the migration script has completed, the old storage will contain the original filegroup with all the objects which could not be moved online. At this point all those objects still need to be moved onto the new storage, and the best (and perhaps the only) way to do this in an online fashion is by adding and removing files from the filegroup.
Here’s how it works: SQL Server allows the addition and removal of files from filegroups at almost any time via the ALTER DATABASE command. (You can’t add or remove files while a backup is running.) A filegroup can be extended onto new storage by adding files to it which are located on the new storage. The script below shows how to do this by adding files D, E, and F, located on SAN 2.
After executing that, filegroup [FG1] is now spread across the old and new storage with 3 files on each SAN, as well as the 3 files containing the temporary filegroup [FG1_temp] on the new SAN:
At this point, files D, E, and F contain very little data, if any at all. Merely creating a file doesn’t mean it will be populated with existing data, though SQL Server will start to make use of it for operations that allocate space on disk from here on out. The objective now is to remove files A, B, and C, so the filegroup has no presence on the Old SAN. This is done using the DBCC SHRINKFILE command with the EMPTYFILE argument, which will remove all of the data from the file by distributing it across the other files in the filegroup. Depending on the size of the file and the amount of data it contains, this operation can take a long time. Fortunately if you have to stop it, the work that has already been performed will not be lost. Once a file has been emptied in this manner it can be removed by using ALTER DATABASE. The code to accomplish this is as follows:
DBCC SHRINKFILE (A, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE A;
DBCC SHRINKFILE (B, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE B;
DBCC SHRINKFILE (C, EMPTYFILE);
ALTER DATABASE DB_NAME REMOVE FILE C;
When this is complete, filegroup [FG1] will only exist on the new SAN, however there is now 2 filegroups and we started with 1. Filegroup [FG1_Temp] can be removed once all objects in it have been moved back into filegroup [FG1], again using the online migration script. I realize this method involves rebuilding some objects more than once, however in my testing it was still faster than moving everything by adding/removing files from the filegroup. I would imagine your mileage will vary depending on your environment and the number of tables containing LOB data, but this is what worked best for me. On my system about 80% of objects could be moved online, leaving only 20% that needed to be moved via the slower method.
Once the online rebuild script completes, things will be nearly complete. All data will be in its original filegroup on the new SAN. Filegroup [FG1_Temp] and its files can now be removed via the following script:
ALTER DATABASE DB_Name REMOVE FILE X;
ALTER DATABASE DB_Name REMOVE FILE Y;
ALTER DATABASE DB_Name REMOVE FILE Z;
ALTER DATABASE DB_Name REMOVE FILEGROUP FG1_Temp;
Once this is repeated for each filegroup in the database, the job will be almost complete. The primary filegroup and transaction log cannot be moved using either method just discussed, so a downtime will be required to move them to the new storage. I was able to make use of existing maintenance windows to detach the database, copy the files to the new SAN,and reattach it. Everything came back up without incident.
At this point the only issue left to tackle is fragmentation. The data that was moved via online rebuilds is not affected, but tables and indexes moved via the add/remove files method will be very highly fragmented. The quickest way to resolve high levels of fragmentation is to rebuild an index, but these are the indexes which cannot be rebuilt online! If an offline rebuild is not possible (and since uptime was very important in my case it wasn’t possible) the next best method is to use the REORGANIZE argument of the ALTER INDEX statement. REORGANIZE is typically slower than REBUILD, but it will allow the index to remain online for users while it runs.
I don’t consider this method to be perfect by any means, but it worked rather well for me given the uptime requirements I had to meet. Thank you very much Jen for the excellent topic, and thank you to all of you who kept reading to the end! I hope this proves helpful!
This time around I’ll be giving a new presentation on tuning backups and restores. Despite what your SAN administrator might tell you about snapshots, you really do need to run backups on your databases, so it’s to your advantage to make sure your backup and restore processes complete as quickly as possible. I’ll be covering tips and tricks that can help dramatically speed them up so you can save the day that much sooner!
The team up in Wisconsin has put together a great schedule that you won’t want to miss. And as of right now, you won’t have to! Registration is still open, so sign up today!