So you use LiteSpeed and expect that at some point you will be using object level recovery. Without any changes you can do this no problem, LiteSpeed will simply need to create what is called the OLR Map first. This is simply a map of where OLR can find each object within the backup file. This is done by adding the @OLRMap=1 parameter to the backup job. Currently there’s no way in the backup wizard to do this (something which will hopefully be changed soon).
Now if your database is smaller, then creating this map is no problem. It just takes a few seconds, or a few minutes. However if your database is say 1 TB in size like mine is, then you’ll want to allow the SQL Server to create the map while the data is being backed up.
Now creating the OLR Map is a pretty quick operation when done as part of the database backup, somewhere about 10-20 seconds if you believe Quest (I didn’t really see any change in database backup time). When done after the fact this can take a very long time. In my case creating the map afterwords takes over 12 hours. Now the good news is that if you do it on object level restore you only need to do it the first time you restore an object from the file, as the object map is written to the backup. Now the downside to this is that you have to write to the backup file. In my case I found of this the hard way, I had the volume with the backup mounted in read only mode on the server that was running the OLR process. Because of this the OLR process had to be stopped and the volume remounted, then the OLR process restarted.
So in short.
- Add the @OLRMap=1 parameter to your backups.
If you can’t, or need to restore from an existing backup.
- Make sure the volumes are read/write
Or do a full restore, then use SSIS to move the data to the production server.
Over the weekend I went to SQL Saturday 28 over in Baton Rouge, LA. I gave two sessions this weekend. The first was “Getting SQL Service Broker Up and Running” and the second was “Deciding if Virtualization is a good choice for your SQL Server“. I hope everyone had a great time at the event, personally I had a blast.
If you want to catch more about virtualization (and storage) options for your databases be sure to check out my pre-con at the PASS summit.
Thanks for coming to my sessions.
That’s right, Mr. LaRock himself (Blog | Twitter) is giving me the chance to present during the 24 Hours of PASS (aka 24 HoP) virtual conference this time around. I’ll be doing my “Storage for the DBA” session to give you a taste of what you’ll be able to get during my pre-con at this years summit, and best of all the virtual conference is totally and completely free. Continued »
In case you missed the dozen blog posts and Twitter messages about it over the last couple of weeks yesterday was a dry run of the Storage and Virtualization session that I’ll be presenting at the SQL PASS summit in Seattle this November. We had a pretty good turn out, and I’d like to thank everyone that came out to watch me talk for 8 hours (turned out it was closed to 6 hours, so that’s the first thing to fix). I got great feedback from everyone.
A big thank you to the sponsors who the event couldn’t have been done without. Those sponsors for yesterday were Microsoft, Emulex, Quest Software and Red Gate.
Several people asked for the slide deck, so here it is in its current form. The deck for the pre-con at PASS will be quite a bit different, as more material will be added, and some of this material will be moved around to make it flow better as I move from topic to topic.
Congrats to the drawing winners. For the folks that won the Windows 7 licenses, as soon as we can track down the Windows 7 licenses I’ll get them mailed out to you.
Thanks again to everyone for coming. Hopefully I’ll see most of you at PASS or Connections.
Latency on a fiber channel network isn’t normally something you worry about. But something that you need to remember is that every meter of fiber optic cable that your data has to travel through takes time. Every fiber channel switch that you have to go through adds more latency.
When you are setting up something like synchronous replication between two storage arrays this latency starts to become more important as every millisecond that you spend waiting for the storage to respond is time that your application isn’t responding to your clients requests.
So, the basic math is that every meter of fiber optic that your data travels takes 5 nanoseconds. So if you have your server connected to your storage array via a one meter cable there will be 10 nanoseconds of delay. 5 nanoseconds for the data to get to the array, and 5 nanoseconds for the response to get back to the server from the array.
So using this math, for each 100 meters of fiber optic cable there is 1 microsecond of latency. For every kilometer of cable there is 10 microseconds. For every 100 kilometers of cable there is a 1 millisecond delay, and for every 1000 kilometers of cable there is 10 milliseconds of delay. So if you are replicating data from LA to New York that’s about 2778 miles, or 4470 kilometers which gives us a delay of about 44 milliseconds for each command which is being sent.
Now there is something else which needs to be taken into account when figuring out the storage latency time. And that is the fiber channel switches. If the ports on the fiber switch are on the same ASIC then there is no measurable latency through the switch, however if the two ports on the fiber switch are on different ASICs then there is an additional latency of 2 microseconds in each direction. While this isn’t much, if you keep in mind that between LA and New York there are probably hundreds of switches, those 2 microseconds are going to really start to add up.
Because of these numbers when using synchronous replication 30 miles is about as far as you want to replicate data. And farther than that and you’ll start to see network latency problems with your application. These problems will be amplified if you use something like SQL Server as with SQL Server and other databases, every nanosecond counts.
Hopefully this math will help you make more informed storage design decisions.
OK, so the odds are getting an XBox and a Windows 7 license are basically zero, but you could win one of them. How can you win some of this fantastic stuff? Well that’s the easy part, all you have to do is come and attend my free day of training on “Storage and Virtualization for the DBA“. Submit a survey, and you’ll get a ticket for the drawing.
Thanks to some great sponsors the lunch plans have changed a little for this event. Instead of everyone heading out for lunch a group of vendors has gotten together and sponsored lunch for everyone. The Lunch sponsors who I can’t thank enough are Microsoft, Emulex, Quest Software and Red Gate.
So head on over to the Event Bright page and get signed up. Seating is limited, as are the number of lunches, so be sure to get signed up quickly.
Tomorrow I’ll be speaking at the Orange County SQL Server Users Group. I’ll be presenting two sessions at the meeting. One will be “Exploring the DAC and everyone’s favorite feature the DACPAC“, and the other will be “Reading the SQL Server Execution Plan“.
The meeting starts at 6:30pm, and I believe that there will be pizza provided and who doesn’t like pizza and a DACPAC discussion.
The user group meets at the New Horizons Computer Learning Center in Anaheim.
1900 S. State College Blvd.
Anaheim, CA 92806
It’s right behind Angel’s stadium (or what ever its called this month), you can’t miss it.
I’ll have some SWAG with me, but not a whole lot (my supplies are starting to run low).
See you there.
Time is running out to register for a free full day of Storage and Virtulization training. If you’ll be in the Irvine, CA area on August 9th and you’ve got a free day be sure to come check out this free training event. This will be a very similar session to what I present at the SQL PASS summit in Seattle in November, where the session will cost $499 to see.
On top of that all attendees that turn in an evaluation form will be entered to walk about with one of the great prizes including a brand new XBox 360.
Seating is limited, and registrations need to be completed ahead of time. If the session does sell out, there is a waiting list, but being on the waiting list doesn’t guarantee that you’ll get a seat so act now.
Later this week (Thursday to be specific) I’ll be speaking at the Orange County SQL Server Users Group. I’ll be speaking on Thursday the 5th, starting at 6:30pm. I’ll be giving two sessions at the user group. The first will be Reading the Execution Plan, and in the second we’ll be looking at the Data Tier Applications, UCP, and DACPACs as well as some other new features in SQL Server 2008 R2.
The OC SQL Users Group meets at New Horizons in Anaheim (behind Angel’s stadium).
1900 S. State College Blvd, Suite 100
Anaheim, CA 92806
Hopefully I’ll see you there.
While I’m there I’ll be pimping my day long session in Irvine which is next week, and hopefully I’ll be giving away from SWAG during the meeting.
So you’ve got a bunch of machines that you want to run a quick query against. SQL Server 2008’s Management Studio gives you a quick and easy. Open up the Registered Servers in Management Studio and select a group of servers. Then right click on the group and click on the “New Query” option.
This will open a new query window where you can run a query against all the servers that are online in that group. In my sample query shown below you’ll see that I ran SELECT @@VERSION against all the servers. When I ran this 3 of the 6 servers in the group were online so three servers were able to return data.
Now if you look at the messages tab (look down) you’ll see which servers the query ran against, and which servers it failed against. It also tells you what accounts the query was run using (based on the connection info for each server).
Now you can’t merge data together in a temp table as everything in the query window will be run against each server. It simply displays the information together. You can pull a single value like I showed above, or you can query a table.
Now when querying from a tables on multiple servers you’ll need to make sure that the schema for those tables are identical otherwise it will only return the data for the first table that it queries. An error will be shown on the messages tab telling that you that the schemas don’t match if this is the problem.