In response to your requests, Windows NT Magazine is
launching a monthly column devoted to SQL Server. Although veteran SQL Server
users might be dismayed to see SQL Server's ranks explode now that Microsoft is
bundling five-user developer versions of SQL Server with both Microsoft Visual
Basic (VB) 5.0 Enterprise Edition and Microsoft Visual Studio 97, the expansion
certainly is an indication of Microsoft's commitment to its enterprise
relational database engine.
We're modeling our column after Bob Chronister's popular Tricks & Traps
column, so we'll rely on you to keep us well stocked with questions. Email your
questions to us at sqlqa@winntmag.com. Not only will we do our best to answer
your questions, but we promise we won't make you wait for the paper copy of the
issue in which your answer appears--we'll email our answer to you.
We also plan to create a theme-based index to Windows NT Magazine
articles related to SQL Server to make it easier to track down references and
tips you remember reading about but can't put your finger on. This subindex will
save you time searching through the 100-plus hits for SQL Server already on the
magazine's Web site. With your help, we'll expand this indexed archive into the
best jumping-off point to other resources for information related to SQL Server.
Stay tuned for details.
Q:
How do I know which service pack SQL Server is using?
If you're like us, you've installed and reinstalled your server so many
times that you've forgotten which service pack (SP) your SQL Server is using. Or
maybe you've inherited a SQL Server at a client site where everyone swears up
and down that their administrator has applied the latest fixes.
Pop quiz: What's the fastest way to check the version of Windows NT
you're using? If you answered the Help About dialog box, give yourself a gold
star. Unfortunately, checking which SQL Server SP you're running is not as
easy--maybe in SQL 7.0.
In the meantime, however, just submit the query
SELECT @@version
You can use ISQL or ISQL/W, SQL Enterprise Manager, or any other tool that
lets you submit queries to the server. The result of this query includes a line
similar to
Microsoft SQL Server 6.50 - 6.50.240 (Intel X86)
Here's how to interpret the result:
6.50.201 = Original SQL Server 6.5 release
6.50.213 = SQL Server 6.5 with SP1
6.50.240 = SQL Server 6.5 with SP2
Q: Should I always upgrade to the latest service pack?
We were afraid you'd ask that question. When you're facing any upgrade, a
conservative part of you suggests, "If it ain't broke...." Even
Microsoft advises you not to apply a service pack (SP) unless it fixes a known
problem you're experiencing. To complicate things further, the NT SP2 fiasco
hasn't done much to improve people's confidence levels in SPs in
general--although SQL Server SPs have been problem free compared with NT's. Yet
despite Microsoft's caveats, Microsoft support engineers usually seem appalled
if you've been too busy doing real work to keep up with the SP du jour.
Nevertheless, SPs often include important pieces of new functionality. For
example, SQL Server SP1 included great bulk copy program (bcp) enhancements that
boosted performance up to 700 percent (see Brian's article, "Seven Tips for
Speeding Large Data Loads with Bulk Copy Program," February 1997).
Microsoft's Visual InterDev actually requires SQL Server SP2 to run properly.
OK, after waffling around the issue, our advice is to never apply an SP to a
stable production server, just for the sake of applying it. Make sure the SP
will fix an annoying bug or provide new functionality that you need. Test SPs on
your development servers for a few weeks before touching production boxes, and
always have a fresh backup in case something goes wrong.
Q: SQL Server won't install on my laptop or my home PC. Why not?
Yes, Virginia, there is a Santa Claus. And yes, some people run SQL Server
on a laptop. A laptop is a great configuration for people who support or perform
development for SQL Server at multiple locations. Lack of a NIC is the most
common installation problem for laptop and standalone PC users because the SQL
Server installation program looks for the default SQL Server Named Pipe
(SSNMPN60,\\.\pipe\sql\query) during installation. You can't create this pipe if
NT's networking services fail to start, and the network won't start unless
you've installed a NIC.
Fortunately, the problem has a solution. Even if you don't have a NIC, you
can fool NT into starting the network by installing the Loopback Adapter, from
the Control Panel, Network applet, as Screen 1 shows. The Loopback Adapter lets
network-aware applications access local resources as if the local resources were
on a network. Better yet, the adapter is as easy to install as a regular NIC.
Q: I just added a new RAID array to my server and want to move my
transaction log. What's the easiest way to do move this device?
Moving devices in SQL Server is simple, but you have to know the trick.
Generally, you don't learn how to move devices until you have to, and then only
after too many unrewarding hours of trying to find the answer. You can find the
answer in SQL Server Books Online, but as always, how fast you find the
answer depends on how well you pose the question.
The trick is to understand how SQL Server stores and uses device
information. A row in master..sysdevices represents each device; the row stores
the fully qualified name of the OS file where it stores the device. When NT
starts the MSSQL service, SQL Server reads the sysdevices.phyname column and
activates each device.
Moving a device to a new location on your server is as simple as changing
the fully qualified path stored in the phyname column. SQL Server will read the
new location at startup, and all will be well in your SQL Server universe.
You can modify the path information by hand or use the stored procedure
provided in Books Online, which you can find by searching for
sp_movedevice. Or you can follow these steps:
1. Back up your master database. Always perform this backup before monkeying
around with a system table.
2. Create sp_movedevice in your master database: Copy the procedure script
from Books Online, paste the script into a query window in ISQL/W or SQL
Enterprise Manager, and then run it.
3. Execute sp_movedevice, passing in the device name and new path location.
The following example moves the Really
BigDB device from c:\mssql\dataReally
BigDB.dat to d:\mssql\data\ ReallyBig
DB.dat:
sp_movedevice ReallyBigDB, to d:\mssql\data\ReallyBigDB.dat)
4. Stop and start the MSSQL service.
Caution: Your server won't start if you use this technique to move
the master device, and the procedure won't stop you ("Are you sure...?")
from doing something really dumb, such as specifying a drive location that
doesn't exist.