Is it safe to dump a database from one server and load it on another?
Cross-server database dump-and-load operations are straightforward, as long as you avoid what we call the cases of mistaken identity and mismatched devices. Let's assume you're using a cross-server dump-and-load operation to move your customer database from your development server to the production server. The mistaken identity scenario occurs when user JohnSmith on the development server magically turns into JaneDoe on the production screen. How can mistaken identities even happen? The answer is that SQL Server stores login and database user information in two system tables: syslogins and sysusers (as you can see in Figure 1). The syslogins table is in the master database and controls who can access the server. The sysusers tables exist in every database and control who can access a particular database.
You add logins with sp_addlogin, which creates a new row in syslogins and assigns an internal system ID (suid) to the login. This suid is a foreign key in the sysusers table for each database the login has access to, so the suids must match properly. (Foreign keys are the mechanism SQL Server and other relational database management systems--RDBMSs--use to support the concept that each value in a set of columns must have a corresponding value in the primary or unique key of another value. SQL Server uses foreign keys during JOIN operations and as the basis for enforcing referential integrity.) Loading a user database from one server to another can easily cause these suids to get out of sync, effectively turning your database access security into Swiss cheese!
Fortunately, SQL Server 6.5 introduced a new stored procedure, sp_change_users_login, that simplifies fixing the problems. This procedure offers three options (Auto_Fix, Report, and Update_One) for re-establishing foreign key relationships from the syslogins to sysusers and sysalternates tables in cases where cross-server dump-and-load database activity has broken the relationships. Here's the syntax:
sp_change_users_login \{Auto_Fix | Report | Update_One\}
\[, '
Auto_Fix makes a best guess at what the relationships are by matching usernames with login names. Report tells you which suids are orphaned or appear to be out of sync. Update_One lets the systems administrator manually adjust individual login and user entries, even if the login names don't match.
The mismatched devices problem occurs if you load a database dump into a new database that had data and log space allocated in a different order from the original database. As you see in the example shown in Figure 2, when you load the first server's dump into the second server, SQL Server writes some data-file data into the second server's log file and some log file data into the second server's data file. To avoid the problem of mismatched devices, make sure that the new database has data and log space allocated in the same order as the original database before you load a database dump.
Replicating database structures. Another new SQL Server 6.5 procedure, Sp_help_revdatabase, analyzes an existing database and creates a Transact SQL (T-SQL) script that you can use to replicate the database structure on another server. Here's the syntax:
sp_help_revdatabase \[
We advise incorporating this procedure in every backup and recovery plan because it ensures that you can properly recover the database on a new server if necessary.
I'd like to load data with bulk copy program (bcp--SQL Server's oft-maligned bulk copy utility) directly from a T-SQL batch. What are my options?
Depending on your needs, one of two easy solutions can help you. The xp_cmdshell extended procedure is great for a quick and dirty solution, but the bulk copy distributed management object (DMO) is a much better choice if your task is repeatable or requires robust error-checking. The xp_cmdshell procedure lets you run commands as if you were typing at an ordinary DOS command prompt.
Running bcp from T-SQL with xp_cmdshell can be as simple as typing
xp_cmdshell "bcp pubs..authors out authors.txt Usa P c"
Readers familiar with bcp will recognize what's inside the quotes as a simple bcp command that dumps data from the authors table in pubs to a text file called authors.txt. This technique is simple to use, but it might not be appropriate for batch jobs that must accurately report success or failure. The problem is that SQL Server can think xp_cmdshell ran just fine, even if the underlying bcp command didn't work. For example, the command
xp_cmdshell "bcp pubs..authors JUNK JUNK JUNK JUNK!!!!"
returns successfully even though the bcp command obviously isn't going to do anything.
The SQL-DMO bulk copy object is a much better solution for regularly scheduled batch jobs because the object can properly detect accurate error codes. SQL-DMO exposes all the SQL Server administrative capabilities as an Object Linking and Embedding (OLE) Automation Server object accessible from any OLE Automation client, including these seven stored procedures that are new in SQL Server 6.5:
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
Listing 1 shows how T-SQL can call bcp functionality directly by calling SQL-DMO's bulk copy object. This technique is easy to use once you get the hang of it and gives you access to cool features of the bulk copy object, such as dropping and re-creating indexes on a target table so you can use fast bcp. For more information about exporting files with SQL-DMO's bulk copy object, see Michael Otey, "Managing SQL Server with VB," December 1996, and "Exporting Data from SQL Server," January 1997.
I've tried using SQL Enterprise Manager's Current Activity option, but nothing shows up. I know users are logged on to the system. What's going on?
The Current Activity option uses the SELECT INTO command to create and populate a temporary table in tempdb. This command works if you enable the Select Into/Bulk Copy op-tion for the database; if you don't enable the option, the command returns an error.
The Current Activity feature won't work if you turn this option off in tempdb, but SQL Server won't tell you what's wrong.
The command
sp_dboption "tempdb", "select into/bulk copy", true
re-enables the option in tempdb and solves this mysterious problem.
I started my server in default mode using the -f option, and now I can't connect at all. What happened?
You can start SQL Server in a default fail-safe mode by using the -f option at startup. The -f option is a handy feature when the systems administrator makes a configuration change that renders the server unstartable. SQL Server starts in single-user mode, which literally means that you can make only one connection to the server at one time. Nothing is wrong with this process, but the SQL Executive service will immediately grab the only connection that's running, preventing you from accessing the database. Anyone who's been in this situation knows that struggling to figure out why you can't connect is incredibly frustrating. By the way, SQL's Enterprise Manager won't work either, because it needs more than one connection. Fortunately, the solution is easy: When you run your server in fail-safe mode, shut down SQL Executive and access the server with isql/w.
Can I use SQL Mail to send Simple Mail Transfer Protocol (SMTP) Internet mail directly without using Microsoft Exchange?
Yes, if you're using Windows NT 4.0. Before NT 4.0, sending SMTP Internet mail without Exchange or a similar SMTP gateway was impossible because the standard Microsoft mail client that SQLMail used didn't provide direct support for Post Office Protocol (POP) 3/SMTP mailboxes. The new Windows Messaging client in NT 4.0 includes native support for POP3 mailboxes, so you can read and send Internet mail without going through Exchange. Because Messaging provides the full Messaging API (MAPI) 1.0 interface that SQLMail requires, SQL Server can send Internet mail directly right out of the box (see David Lafferty, "Integrating Windows Messaging with SQL Server," May 1997).
Help! I accidentally removed one of the xps that ships with SQL Server, and I can't figure out how to add it back in!
SQL Server registers all xps, system supplied or user created, through the SQL Enterprise Manager (SEM) interface or with the sp_addextendedproc command. That part is simple. The hard part is finding the correct DLL that contains the missing xp. But don't worry--you won't have trouble when you know that SQL stores all installation scripts in the \mssql\install directory. Obviously, the SETUP program must run sp_addextendedproc for all the system xps registered during Server's initial installation, so you can find the original call in one of the install directory .sql script files. You could munge through the files by hand if you were really bored, but we'd rather use NT's find command to locate the correct file(s):
Find "sp_addextendedproc 'xp_sendmail'" *.sql
The results of this command tell us that sqlmap60.dll contains the missing xp, so you can re-register it with SQL Server by running the following command:
"sp_addextendedproc" *.sql
Can SQL Server run on Windows 95?
No, but you can run utilities such as SQL Enterprise Manager, isql, and bcp. Microsoft has also announced plans to offer a Win95 version of Sphinx/SQL 7.
Report to Get Your Juices Flowing
IBM happily points databasers to a new report by Bloor Research, which
details the scalability "advantages" of DB2 for AIX and for Windows NT
over MS SQL Server for Windows NT. Read all about it at
http://www.bloor.co.uk/html/6_3_97.html.
Service Pack 3
In June, Microsoft released SQL Server Service Pack 3 (SP3). To download SP3
from Microsoft's Web site, go to http://www.microsoft.com/kb/articles/q166/7/01.htm. The June release was a second try;
Microsoft had released a buggy version temporarily in May. SP3 fixes many bugs,
so be sure to evaluate it if you're experiencing problems. Remember that service
packs are cumulative: If you haven't installed SP1 or SP2, SP3 will kill three
birds with one stone.
Questions?
Send your questions about SQL Server to Karen Watterson and Brian Moran at
http://www.sqlqa@winntmag.com.
