Category: "Reliability"

SQL Server Storage Engine under the hood: How SQL Server performs I/O

I will present this session on September 6th 2011 in Dublin Ireland at 6:30pm (None Irish audience is welcome too, I promise to finnish in time for a pint of Guinnes :-) How does SQL Server really access the data and log files on the disk? What's done asynchronous and what's synchronous and what impact does that have on our queries and DML statements? How do you specify IO requirement and what are they to begin with? What I/O characteristics do different access methods, workloads and maintenance task have? What impact do features like database mirroring and replication have? What changes have occurred to the SQL Server startup? Which I/O Subsystems (including SSD) are best for which type of data and what's the impact if you choose a different one? Which files can be placed on the same volume and which should not? Thomas H. Grohser is a SQL Server MVP (SQL Server Performance Engineering). In the last 17 of his more than 24 years as an IT Professional, he spent the majority of the time exploring the deeper inner workings of SQL Server and its features while working for entertainment , pharmaceutical and other industry leading companies. The main focus of his work is to architect, plan and provide a reliable, highly available, secure and scalable infrastructure for and with the features of SQL Server. This covers all angles from hardware, network, storage systems, Operating systems, SQL Server configuration and the Data Access Layer of applications; Currently, he works at one of the largest SQL Server installations in the world processing hundreds of millions of transactions daily while moving peta bytes of data in a 24x7 environment ; Now its time to share all the findings and the gathered know how with the SQL Server community therefore he started speaking regularly at international conferences in North America and Europe and at local events and work closely with SQLCAT to make sure everyone interested can access the information. Location : Ground Floor, Microsoft Atrium Building Block B, Carmenhall Road, Sandyford Industrial Estate, Dublin 18 (close to the Sandyford Luas stop). Limited underground parking is available at the back of the building Registration : The event is free to all. However, registration is mandatory for safety and security reasons. When: Tuesday, Sept 6th at 18:30 Where: Auditorium, Microsoft Atrium How: http://www.mtug.ie/Home/tabid/38/ctl/Details/Mid/369/ItemID/91/Default.aspx?ContainerSrc=[G]Containers/_default/No+Container

A little bit more information on multi location backups

On of the advantages of backing up to multiple locations is that the data is read on the SQL Server just once, this is an advantage because the IO on the database files is kept to a minimum (in comparison on doing two backups one after the other). Then the data is sent to all destenations in parallel this results in the fact that the backup will take as long as is takes to write to the slowest target location.

You can backup full, differential and log backups to multtiple locations the MIRROR TO option is build in into all of them

I do not backup full backups to two locations (you can alsways take the backup before the last to start your restore if you lose the last one) but I backup my logs to two locations to reduce the risk of a broken log chain by a missing or corrupt file.

If you are really paranoid you should write the data trough two different network cards from different manufactuers with different drivers to avoid data corruption by a faulty driver and then have a constant verify by restore on all destinations.

Backup to multiple locations at the same time (multiple copies of the backup files)

Sometimes it can be usefull to have two ore more copies of the same backup (e.g. you have two locations/disks and want a copy in/on both)

 

So lets explain how to backup to two locations at once. First the bad news this is an Enterprise (SQL Server 2005, 2008 and 2008R2) and Datacenter Edition (SQL Server 2008R2) only feature.

 

If the backup to one location looks like this

BACKUP DATABASE MyDatabase
     
TO DISK = 'D:\BACKUP\MyDatabase.bak'

 

Backing up to two locations  looks like this

 

BACKUP DATABASE tg
      TO DISK = 'D:\BACKUP\MyDatabase.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup2.bak'
      WITH FORMAT

The WITH FORMAT is required even if the backup files do not exist

 

You can go wild and backup to up to 8 locations at the same time.

BACKUP DATABASE tg
      TO DISK = 'D:\BACKUP\MyDatabase.bak
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup2.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup3.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup4.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup5.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup6.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup7.bak'
      MIRROR
     
TO DISK = 'E:\BACKUP\MyDBBackup8.bak'
      WITH FORMAT

 

I would not recommend to backup to 8 locations thats because the feature does not work as you might expect. It works like any other transaction in the database all or nothing. This means if one of your locations is unavailable the backup does not start and if one location fails during the backup the whole backup operation fails.

Hello World

Hello fellow SQL Server users.

I have spent half my life working with SQL Server and accumulated a lot of knowlege in this past 20 years. I started presenting on several conferences over the last few years and more and more people asked me to start blogging. This is what I am going to do here.

My focus is on building reliable, avalable and scaleable infrastructures for SQL Server and this is also the main focus of this blog but you will see the ocasional general tip entry as well.

If someone has specific questions feel free to ask them I will do my best to answer them

=tg=