If you are creating a new database, the Upsizing Wizard will ask you to select devices for your database and optionally, a log. It will also ask you to set the size of the database itself. In order to answer these questions, you should estimate how much space your new database will require.
When SQL Server creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all this space is necessarily used by the database. Database size is just the upper limit on how large a database can grow before it runs out of space.
By looking at the size of your Microsoft Access database and estimating the rate at which your new SQL Server database will grow, you can arrive at a rough estimate of the space needed for your database.
If you have ample disk space on your server, simply multiply the size of your Microsoft Access database by two. This will ensure that the Upsizing Wizard has enough space to upsize your database and leave it some room to grow as well. If you expect a lot of data to be added to the database, you should make the multiple larger.
The Upsizing Wizard works best when there is plenty of disk space available. In situations where disk space scarce, it is possible to fit an Microsoft Access database onto a SQL Server database less than twice its size. In general, every megabyte of Microsoft Access data will require 1.3 to 1.5 megabytes on SQL Server. Remember that the size of a Microsoft Access .MDB file includes all Microsoft Access objects, not just data.
All SQL Server databases and logs are placed on devices. At one level, a device is merely a logical location to put databases and logs. At a lower level, a device is a physical file. When a device is created, SQL Server creates a file, thus reserving a set amount of disk space for its own use. A device can be thought of as a fixed portion of disk space that SQL Server sets aside for its own use. At a lower level, a device created by SQL Server.
If no devices have enough free space, you may create a new device using the Upsizing Wizard. New devices should be at least as big as your estimated database size. It is recommended that you make the device still larger if possible. This will let you expand your new database later or place other databases or logs on the same device.
The size of a device cannot be changed. Make sure you create devices that are sufficiently large.
Once you've arrived at an estimate of how much space your new database will require, you will know how much free space the device you choose must have. The Upsizing Wizard will show how much free space is available on SQL Server devices and you can choose from among those.
In most cases, the Upsizing Wizard provides more than enough control over SQL Server devices. There are two cases where you may wish to create devices before running the Upsizing Wizard.
If your server has more than one physical hard disk, you may want to place your database on one disk and the log for the database on a different disk. In the event of a disk failure, the likelihood of recovering will be much greater.
The Upsizing Wizard allows you to create new devices but only on one physical disk¾the same disk as the Master database device. To place a database and log on separate disks, make sure you have devices that are big enough on both disks, creating new devices if necessary. Then run the Upsizing Wizard.
SQL Server allows databases and logs to span several devices. However, the Upsizing Wizard allows you to place databases and logs only on a single device or the "Default" device.
If you want to specify multiple devices for a database or log, make those devices default devices. Make sure only those devices are set as default. Then run the Upsizing Wizard and choose "Default" for the database or log device.
Note that if the size you specify in the Upsizing Wizard for the new SQL Server database, or the size of the log doesn't require using all the devices set as default, SQL Server will use only the devices necessary to accommodate the database or log.