Q: I'm using the Microsoft Access upsizing wizard to move a large database
to SQL Server, and the process is taking forever. What can I do to speed
things up?
Three versions of Access databases (Access 2.0, Access 95, and Access
97--each with different file formats) let you deal with the three versions of
Microsoft's upsizing wizard. You can download the Access 97 and Access 95
versions from http://www.microsoft.com/AccessDev/ProdInfo/AUT97dat.htm.
But for Access 2.0 files, you'll need to migrate from Access 2.0 to Access
95 first, purchase the Access 2.0 Developer Kit, or consider commercial options
such as Aditi's UpDown (http://www.aditi.com).
In general, each version is faster and more powerful than the previous
version, but automation has its limits. You can't, for example, use the upsizing
wizards to create SQL Server databases that span more than one device; that is,
you must have the data and log files on the same device.
All the upsizing wizards can, however, create a SQL Server schema and
populate your tables with data from the Access database. Although that
capability makes the migration as hands-off as possible, upsizing large datasets
is tedious because the wizards use INSERT statements to populate the SQL Server
database one row at a time. You have a table with 500,000 rows? Any idea
how long Access will take to issue 500,000 INSERTs across the wire?
SQL Server's bcp provides a much faster load, especially when you implement
bcp in fast (no logging) mode (see Brian Moran, "Seven Tips for Speeding
Large Data Loads with Bulk Copy Program," February 1997, for tips about
tuning bcp). A good compromise is to let the wizard create the database schema
for you, move the log file to a second device, and then use bcp to manually load
the data. By the way, each upsizing wizard has bugs and anomalies, so be sure to
check the Knowledge Base before using any of them.
Note that nothing forces you to use the upsizing wizards. You can always use
SQL Enterprise Manager--or your favorite third-party database administrator
utility--to create the new database. Then you can export the Access data as
ASCII files and use bcp to get them into SQL Server. For a comprehensive
discussion of options, download Microsoft's new 80-page paper "Upsizing
Microsoft Access Applications to Microsoft SQL Server" from http://www.microsoft.com/sql/reskit.htm.
Q: My table has a primary key (PK) on a column defined with the identity
property, but I've started getting error messages saying I'm trying to insert
duplicate keys. What gives?
You might have duplicate keys without knowing it. In theory, SQL Server
always picks a unique number when it assigns identity values, but it can lose
track and try to assign a value that's already in use. This duplication can
happen, for example, if you shut down the server improperly or if you truncate
the table. To troubleshoot the situation, first make sure to use the PK or
UNIQUE constraints to forbid duplicate entries, and then use DBCC CHECKIDENT if
your values ever get out of synch and cause inserts to fail with duplicate key
errors. The command
DBCC CHECKIDENT (table_name)
compares the current identity value with the maximum value in the identity
column. If the current identity value is invalid, SQL Server will reset it using
the maximum value in the identity column.
Q: What's the purpose of the logwrite sleep parameter?
Logwrite sleep is one of the least understood SQL Server configuration
parameters, but it can affect performance dramatically in certain applications.
(It's also one of the advanced parameters that are visible only when the Show
advanced option is set to 1.) By default, SQL Server performs a physical
write to the transaction log as soon as a user transaction commits. This process
provides a reliable way to recover the system if someone accidentally kicks out
the power cord. Logwrite sleep tells SQL Server to wait x milliseconds
before forcing the physical write to the transaction log. Logwrite sleep's
values (in milliseconds) range from 1 to 500; the default is 0.
Let's say you are building a stock system that needs to handle a high volume
of trade requests from hundreds of users. The transaction mix for this
application is 5 percent SELECT, 5 percent UPDATE, and 90 percent INSERT, and
all transactions are brief. Assume that SQL Server must perform a physical disk
I/O to write a log record as each INSERT transaction commits. This requirement
means that one transaction occurs per log write. A good SCSI/RAID disk subsystem
might let you do sustained sequential I/O of about 150 writes per second, and
typical IDE drives might handle about 60 per second. In this scenario, you could
do a maximum of 150 inserts per second because each insert requires a physical
I/O to write the log record. (SQL Server could include more than one transaction
in one log write, but let's keep the example simple.)
Register
