Then with your data adapter you just pass the stored procedure’s name and set the selectcommand.commandtype = commandtype.storedprocedure, then fill your DataSet and display the results.
Another way would be to fill the two DataSets, add the relation, create a DataView from the Customers DataSet, and then set DataView.RowFilter equal to customerid.
Q I have 4GB set aside for my SQL Server database and I have Address Windowing Extensions (AWE) enabled, as suggested last time in SQL Q&A. However, I still can’t take advantage of all the memory. What’s wrong?
Restoring a Database
Q I am trying to restore a SQL Server database from a device by specifying the location in the Backup Device Location dialog box. However, the option to specify a shared drive doesn’t exist in this dialog box. How can I add the drive path to this location?
A If the SQL Server service is running under a domain credential that has appropriate access to the remote share, you can back up to and restore from a file share via T-SQL BACKUP DATABASE and RESTORE DATABASE. Use the "DISK=" syntax and fully qualify the file name (like this: DISK=‘\\server\share\folder\database_backup.bak’).
Moving a SQL Server Instance
Q I have an active/active cluster pair running two instances of SQL Server 2000, SQL2, and SQL2\i2 on two nodes, NODE1 and NODE2. I’ve been asked to remove one of the cluster SQL Server instances (SQL2\i2), but the server owner still wants to be able to failover drive resources on the second remaining node (for example, SQL2 between NODE1 and NODE2).
If I remove the SQL Server instance, will I still be able to failover by resource? Am I missing something here as this is the production server and I still want to have failover for the remaining instance?
A What used to be called active/active is now called multiple instances. So if you have two virtual SQL Server instances, you have two instances. You could have up to 16 of them. They are completely independent (except for the tools—there is only one copy of them). Removing one instance should not have any effect on the other.
Each instance can be configured to allow one or more of the nodes to be possible owners. So if the remaining instance is configured to allow either Node1 or Node2 to be the owner, then you can fail back and forth. Whether another instance is removed has no effect on the possible owners for the remaining instance.
To summarize, if the virtual instance SQL2 can currently fail back and forth between Node1 and Node2, removing the virtual instance named SQL2\i2 will not change that.
Security Auditing Tools
Q What tools can I use if I want to enhance a security audit of SQL Server?
A You can use the
Microsoft Baseline Security Analyzer
. It will generate a report of detected security vulnerabilities in SQL Server, Windows, and IIS. You can also use the SQL Profiler tool, which is included with SQL Server, to monitor and capture security-related events and identify any potential gaps in permissions. You might also take a look at the paper "Threat Profiling Microsoft SQL Server" at www.ngssoftware.com for good information on security audits.
Updating SQL Server
Q I need information about SQL Server 7.0 Service Pack scripts to update system catalogs. I have recently upgraded my operating system from Windows NT® 4.0 to Windows® 2000. After the upgrade, I installed SQL Server 7.0 with SP4. Finally, I stopped the SQL Server services and replaced system databases (master, msdb, tempdb) with a file copy, then restarted the services.
I think the server is running with an old system catalog for the master database. If I fix this problem, can I simply rerun the post-SP4 installation scripts instead of reapplying SP4 and restarting?
A If your server is running with SP4 installed and you still have pre-SP4 system databases, you should reapply SP4 to this server. Otherwise this would be considered an unstable environment because the SQL Server executable is expecting options that may not be available. In a best case scenario, the system will act in an unexpected manner.
A service pack would put the server in single-user mode and run these scripts in order to ensure that updating is done correctly. It is unlikely that you could connect with Query Analyzer during production time and run these scripts without them failing or getting some sort of unexpected behavior during or after the scripts run unless SQL Server is restarted.
The service pack puts the server in single-user mode and on shared memory only, runs the scripts in order, and restarts SQL Server normally. To get a stable environment, you should do the same. What you could have done to avoid this situation in the first place is check the SQL Server version before you did any of this and reapplied the exact same version. Now that you have upgraded to SP4, you really will need to schedule downtime to perform this update.
Thanks to the following IT professionals for their technical expertise: Rand Boyd, Bill Emmert, Dave Greene, Cindy Gross, Rob Hawthorne, Scott Heim, Tom Keane, Michael McCracken, Alejandro Mihanovich, Hemant Patel, Ryan Perlman, Rob Schoedel, Ananth Shenoy,Tim Sullivan, and Brad Wilkens.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.