What should be done for a Transaction log drive (array) to make sure the log records are ON the disk, even if the power fails?
This is a great question that I got a while back during a web cast that I did during the 24 Hours of PASS. Most storage arrays, at least the better quality ones, have a battery within them which will keep a portion of the array online in the event of a power outage. While the array won’t be available for use, this will give the array enough time to safely take its write cache from memory and write it down to the disk, then gracefully shutdown.
In the case of the transaction log, every write into the log is put down to disk immediately, and then written into the database file. SQL tells Windows to do the write, and Windows tells the disk to do the write. When the write is written to the write cache the disk tells Windows that the write has been completed, and Windows passes that information to SQL which tells SQL that the write to the log is done, and that it can now write to the database files as well. If the power fails in the middle of this, after the transaction has been committed to the log, but before it has been committed to the database the data still has been written to the disk. When the array sees that the power has gone out it’ll write the write cache to the disk, then power down.
When the power comes back up the array will load up the write cache back into memory, and flush the write cache down to where it needs to be written (in the similar way that SQL does a roll forward when it restarts). Once the writes have been completed the array will allow the hosts (the servers) to see the LUNs and the SQL Server can then fire up and do its normal roll back and roll forward of the data within the transaction log.
Hopefully this helps fix some confusion.
P.S. Yes I am well aware that I have greatly over simplified the process of how SQL writes to the log and the database, but that isn’t the point of the article. I’m sure that Paul has some great articles on the internals of how this works over on his blog.
Given that LiveMotion, etc give you high availability in the event of a server failure, why would you put a SQL cluster on VMs?
This question comes up every once and a while when designing a virtualization solution for someone. When building a virtualization solution (either Hyper-V or VMware) you get some great free HA solutions included when you build a cluster so why would you want to go through the pain of creating a Windows cluster under an already clustered solution?
Creating a Windows cluster under the hypervisor gives you some additional protection. If you only have the host HA solution while you can live migration or vMotion from one host to another easily you aren’t protected from a guest OS failure or from a host failure (sort of). If the host fails, yes the platform will restart the VM on another host pretty quickly, but pretty quickly may not be good enough for you. You may need it to restart within seconds of a host failure.
What happens on patch Tuesday when all the security patches are rolled out? The Windows OS within the guest still needs to be rebooted after the patching and the HA solutions of the host won’t do anything for you here.
However if you need to ensure that very high up time of a SQL Server database (or any other cluster-able service) then building a Windows Cluster under the hypervisor of choice is a viable solution for you to use. Just make sure that you use the rules engine of the host to ensure that both nodes of the cluster are never running on the same host server, because if they are then the whole point of the cluster will be lost in the event of a host failure.
P.S. This post and the next bunch of posts are all questions and answers which I have received from online sessions at either SQL PASS Virtual Chapters or the 24 Hours of PASS virtual event.
Every once and a while I run across someone who thinks that the way to correctly restart the SQL Server service is to use the SQL Server Configuration Manager. That’s just not the case. You can restart the services from SSMS (if your account has rights), or from the service applet in Administrative Tools, or Computer Management, or the NET STOP command shell command, or the sc command shell command, or power shell.
These all have the same effect, to tell the Windows API to issue a stop command to the service which the service takes and uses to shutdown. None of them are the correct way, as they all do the same thing.
I am happy to report that on Monday I’ll be starting my new job with Phreesia who are based in New York, NY. I’ll be working from home pretty much full time with occasional trips to the office. When I get a chance I’ll post more info about my new job and company.
This week was the 2010 SQL PASS summit in Seattle. I would have to say that this was the best PASS summit that I have attended (this was my fourth). There were more exhibitors that ever before, there were more attendees than we’ve had in a very long time, and I think that lots more people had a great time.
For the first time I was able to deliver a pre-con session on the day before the first regular conference day. For those that haven’t gone to a pre-con they are typically full day sessions (8:30-4:30) and come with an extra cost to the attendee, $400 per person in this case. My pre-con was a great success as far as I could tell. There were lots of questions (which means that people were listening and wanted deeper information than I had in the slide deck) and we had some great discussions during the session. Another great thing was that the room was pretty full with 82 attendees being the final count. Continued »
Today’s keynote started with Rick giving us some important news. He first announced that the SQL Rally website has gone online for the first SQL Rally event as well as the first SQL Rally pre-con speakers. The date and location of the next SQL PASS summit has also been announced as October 10th-14th.
After Rick gave a lucky winner an XBOX Kinect we were treated to a presentation by David Dewitt on how the optimizer works within the SQL Server database engine. Now there isn’t really anything that I can post about David’s presentation other than it was awesome to get this sort of insight into the query optimizer other than the fact that optimizer’s are very hard to write. One really cool thing that David did was to accept questions from the audience both online and in real life via email which were culled by people from PASS HQ and were passed to him after the power point was completed.
After listening to David’s keynote my head hurts. Tonight there will be more singing and drinking at Bush Garden to make the pain go away.
Today was the second day of the SQL PASS summit. The first item of business was to award The passion award to Wendy Pastrick (blog | twitter) . After the awesome award was given out by Bill Graziano, Bill talked about the budget stuff. Bill also promised to wear a kilt on Wednesday of next year, something which I’ve done for the first time this year supporting Women In Technology (wit).
Today was also the first time that I used my iPad to blog for the day. I went totally laptop free for the day to see how well it works for me.
Quentin Clark was our next keynote speaker for today. Quentin started by talking about the high level features around the new Denali product (also called SQL 11). SQL Server Danali will be bringing in an “Always On” concept which will allow ever database to have the ultra high availability requirements that many database applications have. This new “Always On” solution doesn’t require any sort of shared storage to work. It will replicate data to multiple servers with the ability of having some servers available for automatic failover ads well as having some servers available for reading the data as well as for backup so you don’t have to take the backups from the active server (not in the current CTP). The awesome part of the setup for this feature doesn’t require that you copy backups around. It will handle the moving of those backups automatically. I have a lot of questions about how this happens and how it effects the system cache.
On the T-SQL side we now have paging, sequences, and much better error handling. Paging allows you to specify the size of each page to display and which page to display with the database engine doing the hard work. Sequences and some more flexibility into the engine by allowing the developer to use the same sequence numbers for multiple tables.
The new filetable feature is a feature which I really don’t see the use for. This feature allows you to create a table which is exposed as a network share so that applications can write files into the database without any application changes. Under the hood it uses filestream feature to handle the IO. We have spent years keeping this blob data out of the database because of the performance issues of having TB of data within the database. Some questions which need answering about the filestream is how does this affect the buffer pool when pulling files in and out of the database engine? Is there a point at which this feature can’t handle a file load, in other words how many files can I shove into a single filetable table before it’ll slow down performance?
(Sorry this was so late coming out, I’m such a slacker.)
Ted Kummert was our keynote speaker for this mornings keynotes. Before Ted come onstage there was a very interesting video which gave us some history of the SQL Server product. The thing that I found the most astonishing was that SQL Server 6.0 and 6.5 was a $100M a year business for Microsoft. Today the SQL Server product is a $3B a year business. That is a massive amount of growth over just 12-13 years or so.
Parallel Data Warehouse
The first announcement was that the Parallel Data Warehouse (PDW) has been released, and the first appliances will be shipped from Dell and HP starting in December of 2011. When you create a database without specifying where the database is stored. When you create a table you specify the column which is used to distribute the data across the servers, as well as how the table should be partitioned. The admin console for the PDW is a web based system which allows you to view how all the cores are running and really see what is happening with the system, this is in addition to the SQL Server Management Studio of course.
The next thing which we saw was Yahoo telling us about their SSAS system which they have. It processes 3.5 billion rows a day which is 1.2 TB of new data per day being processed into a 12 TB SSAS cube. Truly an amazing scale.
Microsoft has announced the new project “Atlanta” which is a new self service configuration assessment service for Microsoft SQL Server which will hopefully reduce the number of support calls. Atlanta is a Azure based system which analyzes your system looking for problems with the SQL Server configuration. The Atlanta system will scan the system configuration configuration and upload the configuration to the Atlanta application nightly. Atlanta will then process the configuration and show the knowledge base articles which show how to resolve the potential issues. When systems settings are changed Atlanta will track those changes to allow the DBA to more quickly find changes which have been made to the SQL Servers.
SSRS is now available as an Azure product where you can simply upload your SSRS reports to the Azure SSRS website and it’ll get its information from your SQL Azure databases. These reports are edited and published using the same BIDS application which we already use today.
The new Azure Marketplace allows you do purchase a variety of known good data from a variety of sources such as local weather. The data is available to be imported into SSRS reports via a data source using an ODATA URL from SQL Server Reporting Services, either in the cloud or a local SSRS report. This published data can then be used side by side with the business data.
Denali (aka SQL 11)
The new Denali CTP (the first public one) has been released to the public. It will be shown during tomorrows keynote, so today was just the announcement. You can download the package today from the Microsoft website. I’m sure there will be much more information tomorrow about the product tomorrow. Be sure to watch Twitter (specifically the #sqlpass hash tag) to watch the information in real time.
Check back tomorrow for more keynote information.
Yesterday was my first day at the SQL PASS summit. Normally I don’t have anything official to do on that day, however this year was different. This year I was allow to present a pre-con at the summit. The pre-con was a heck of a lot of work to put together, but it was a heck of a lot of fun to present for the entire day. There were about 80 people in the room for the day and it seems that everyone had a great time, and pretty much everyone I talked to enjoyed the session and learned something.
There were a ton of questions being asked, and I was even able to almost all of them. Hopefully I’m able to do another one next year at the summit.
After the pre-con day was done I was suckered into being a part Quiz Bowl game at the welcome reception party. We did very poorly as far as the questions go, but we all had a great time with the game. Hopefully they make the mistake of asking me to come do the game again.
I’ll do my best to post something on my blog daily (the morning after probably).
If you aren’t at the conference, and you are a SQL Server professional, and you aren’t at the summit, then you are missing the biggest, best SQL Server conference available.
This week was my first dev connections conference. The conference was a bit bigger than I thought it would be, and I had a blast. The couple of sessions that I went to where excellent, but I wouldn’t expect much else from Paul Randal (Blog | Twitter), Kimberly Tripp (Blog | Twitter), and Buck Woody (Blog | Twitter). The people that attended by sessions were great, although they were a little quiet and didn’t ask many questions (I love questions during my sessions).
I made some new friends, some from Microsoft and some that were attendees. Many of which I’ll hopefully be seeing at future events.
It was a great conference, but I’m exhausted, and the PASS conference is coming up next week. I’ve got about 2 days to relax before the insanity of the SQL PASS conference starts up. If you are going, I’ll see you there. If not you should.