Friday, February 22, 2013

How to test a very large database (SQL Server)

submit to reddit
This is not a very complicated article, but it might be useful for some of you that occasionally have to test a very large database (more than 2 TB) in SQL Server. As usual, all this tutorial will be focused on Virtual Machines (VM) with VMware Workstation 9.


You have to test an application that monitors a database. For some reason, like validating a reported bug, you have to test the application on a large database of more than 2 TB. Of course, it is difficult to find a 2 TB database, and you don't even have the hardware or that. It doesn't matter if the database is populated or not.


  1. A Windows Server VM. I used a Windows Server 2003 machine. You can use VMware Workstation 9.
  2. SQL Server installed. I used SQL Server 2005 with SQL Server Management Studio.

1. Create space for your database

A detail: In VMware Workstation, virtual disks do not have the size they say they have. For example, a virtual disk of 2 TB really has only about 100 MB (depending of the use of the disk). These virtual disks grown only when they have stored data. So, it is possible to have 2 TB virtual disks on, lets say, 50 GB physical disks. Taking this into account, we can go on:

1.1 The first thing to do is to create a disk of 3 TB or more.  To do this, click on VM>Settings>Add...
1.2 Select "Hard Disk" in Hardware and click Next.
1.3 Select "Create a new virtual disk" and click Next.
1.4 Select SCSI and click Next.
1.5 The maximum size that VMware Workstation admits for a Virtual Disk is 2 TB (2040 GB). So, enter 2040 in "Maximum disk size (GB)".
1.6 Select "Store virtual disk as a single file" and press Next.
1.7 Press Finish. Now we have 1 disk of 2TB, but we have more. Repeat steps from 1.1 to 1.6.

2. Merge 2 disks in a single volume

2.1 Go to the VM
2.3 Right click on My Computer>Manage
2.4 Click on Disk Management
2.5 In the "Initialize and Convert Disk Wizard", press Next twice.
2.6 Select Disk 1 and Disk 2 to convert them into dynamic disks and press Next.
2.7 Press Finish
2.8 Now, you can format the disks to become a single unit. But with 4 TB to format, this can be to long for a simple test. Here's a trick to save some time:
a) Go to Computer Management. Right click on Disk 1>New Volume...
b) Press Next
c) Select "Simple" and press Next until you reach the final dialog. Then press Finish.

d) Now the disk will be formatting. Here's the trick: Right click on the disk while it is formatting>Cancel Format.
e) Right click on the disk for which you canceled formatting>Extend Volume
Press Next>Select Disk 2 and press Add
Pres Next>Finish

Now you have a dynamic disk:
f) Go to My Computer>Right click on the dynamic disk you just created>Format...
g) Check Quick Format>Press Start>Press Ok.  Wait a couple of minutes.
h) Now you can use your 4 TB disk. If you use normal format it could take hours.

3. Create a 3 TB database

The same rule of the virtual disks apply to the virtual databases. Their size corresponds only to the effective data they contain.

3.1 Open SQL Server Management Studio, or some tool to create databases. In this case I will use Management Studio

3.2 Expand Server>Right click on Databases>New Database...
3.3 Set Initial size to it maximum (2097152 MB)
3.4 Click on "Add"
3.5 In the added row, enter the name of the new file (in this case "Test_1") and set it to 500000 MB in "Initial Size" in order to get a database larger than 2 TB (this is not exact, obviously).  The configuration dialog should be similar to this:

3.4 Now, you have to change the path for the database files to the 4 TB disk you created. To do this, go to right in the central pane and look for the "Path" column.
3.5 Now change the path for all the files. I recommend to create separate folders for the database files and the logs. This is what I did:
Now press Ok and wait for some minutes. This could take long.

3.6 Now you can check the properties of the database. It has a size of more than 2 TB!!!

0 comentarios:

Post a Comment