SQL Server - Data Integrity Part 1

I will be doing a number of posts on data privacy and how we can protect ourselves from the worst thing that can happen to a DBA besides lagging developers - corrupt/inconsistent data.

I will be doing a number of posts on data privacy and how we can protect ourselves from the worst thing that can happen to a DBA besides lagging developers - corrupt/inconsistent data. When things have gone wrong, plain and simple.
Before installing a new instance we should examine the disk we will be using, for performance testing I recommend SQLIo (which I have written about before here) and to check that the disk is whole and clean we use SQLIOSim which is included in the Binn folder in SQL2008 and later versions, it can also be downloaded separately. The program has no dependency or connection to SQL Server other than it tries to simulate SQL load randomly on your poor disks. Before running SQLIOSim, you should talk to your SAN administrator as it is possible that the load will increase quite a bit and this may interfere with other systems connected to the same SAN. You should also ask about the cache size of the connected disks. If you are using local disks, you do not need to ask anyone for permission.

Run SQLIOSim.exe which you will find in your bin folder, run with Administrator rights. The program will scan your disks and suggest to put two test files on each disk (data and tlog file), however quite small files so start by increasing these, at least/at most 3 times as much as the cache memory of the SAN/your RAID controller or as big files as you have space for on each disk. For each file you can also set if it is a log, shrinkable or save file.

You should ideally run for 24 hours, so set the cycle duration / test cycles to the appropriate values. I usually set 600 cycle duration and 0 = infinite and break the test myself. Set Max Memory in the same way as you intended to set the equivalent in the SQL instance(s).

Please change the Error Log XML filename so that you use unique filenames for each test run, e.g. add date/sequence number and if you are testing with a specific configuration, e.g. 140331_NoCacheMem_sqliosim.log.xml. Then you avoid getting a big log file that builds up and becomes very difficult to read. Check "Delete files on shutdown" and the test files will be cleaned up when you are done. Click OK and then click on the Simulator menu and Start to get started. A log window appears where you can follow all the test steps, errors are shown in red and warnings in yellow. A typical warning is that the disk system does not keep up and that there is a waiting time, i.e. you have loaded it more than it can handle. When the test is finished, the number of errors and warnings is displayed and each error is saved in a separate dump file in the same folder as the program.

Example of what it might look like when errors are found on the disk. If everything looks ok, we can go ahead and install SQL Server. In the next part we will look at appropriate maintenance jobs and backup procedures.
/Fredrik