Microsoft SQL Server Resource Kit tools - 'Free' with MSDN and TechNet

Okay so these tools aren't totally free but I didn't really have anywhere else to categorise them. Anyway I'm sure I've seen advertisements that boast "you can get a ‘free’ toy with every packet of some breakfast cereal". Well these tools are ‘free’ with a subscription to MSDN SQL Server CD/DVD or TechNet Resource Kit CD/DVD and they’re very handy. Check them out below in this excerpt from the kit - maybe there's one you always wanted.

Some of you maybe aware that the SQL Server 2000 Resource kit used to be a separately purchasable item – however today I learnt from Allen, who pinged me an email, that this book is no longer in print. You can find text of the resource kit @https://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx - and this is free

Bulk Image Insert (BII)
One of the most frequently recurring questions in the support newsgroups is how to deal with image fields in SQL Server, and you'll find a comprehensive chapter on this topic. Particularly interesting is the section on "lessons learned" in implementing Microsoft's TerraServer project (https://www.terraserver.microsoft.com, the "world's largest online satellite imagery gazeteer"). Not only does the chapter explain in comprehensive detail different ways to access BLOB information, it also includes the BII utility that's part of TerraServer. BII lets you automatically import images into a database using a system that's reminiscent of bcp, but this utility imports the image files, or any binary file, defined in the import file, and not only the file's path.

Administration stored procedures
The kit includes a collection of audit-trail stored procedures to detect situations such as these:

· Clustered indexes defined in columns wider than a specific length (sp_rk_audit_clustered_wide)

· Suspicious settings at that server or database level (sp_rk_audit_configure and sp_rk_audit_dboptions)

· Use of SQL-89 syntax for outer joins (sp_rk_audit_old_outer_joins)

· Any database objects with owners other than the dbo (sp_rk_check_non_dbo_owned_objects)

· Any specific Table settings, such as pin table (sp_rk_tableoption)

To install these procedures, I recommend you read the installation process available in Chapter 34, because it's not as obvious as it might seem.

Analysis Services Thin Web Client Browser
There's also a useful example of a Thin Web Client showing an OLAP cube (see Figure 3), where you can get a good idea of how to use JavaScript to manipulate a cube from a Web interface. Be careful to provide the correct server name, database name, and cube name when you run this utility, though, because the default values probably won't match your installation.

Catalog Explorer
The Catalog Explorer is an example of how to write a VB application to show the meta data of OLAP objects. The application uses ADOMD and ADO, and the result is simple and efficient, as shown in Figure 4.

Data Mining Visual Toolkit
SQL Server 2000 Analysis Services includes data mining algorithms, and the Resource Kit provides a demo developed by Angoss Software Corporation (https://www.angoss.com), which shows how to use these algorithms in a commercial application. The information can show decision trees (as in Figure 5), cluster models, and liftchart models. [Angoss sells a Data Mining SDK for SQL Server 2000 and the KnowledgeSTUDIO SDK for developers, along with a variety of other related packages for end users and enterprises.—Ed.]

Digital Dashboard
With the new .NET initiative and the advent of Knowledge Management (KM) systems, designing Digital Dashboards is an increasingly popular way of developing information portals. Note that this new Digital Dashboard kit can't be installed in a domain controller. (It took me a while to realize why it didn't install until, in desperation, I decided that it was time to read the documentation. Sound familiar?)

Database Hammer
The Database Hammer is similar to the previous version's SQL Load Simulator, but Database Hammer is a VB app that you can customize. (The old utility was a multithreaded app that connected to SQL Server via ODBC, while this one uses OLEDB to connect to SQL Server from multiple instances of a VB component and inserts 10 million rows of random data into a test database.)

Database Generator
The Database Generator has been improved from the previous version. This utility allows you to create test data in a database, selecting the data distribution criteria for each column (see Table 1).

Table 1. Data distribution criteria.

Federated Toolkit Sample
The Federated Toolkit Sample is a very didactic example that uses an n-tier design based on SQL Server 2000 Distributed Partitioned Views (DPV) and two COM+ components: a data services component and a routing component. Using the demo Inventory application, you can see how a distributed application can benefit from using DPVs, and you can use this example as a template to develop more complex applications.

Meta Data Scripter
If you never learned how to use Decision Support Objects (DSO), the Meta Data Scripter sample provides an example of how to use this useful library. Using this sample application, you can fully script an OLAP server to a VBScript file—something that can come in handy when you want to re-create the same complete or partial structure in a different server.

Desktop Engine Administration
Another recurring question sent to the SQL Server support team and the public newsgroups is, "How can I administer MSDE? Why doesn't MSDE have an Enterprise Manager?" If you've ever asked these questions, the Resource Kit provides you with a sample application you can use to create an Access application to programmatically administer SQL Server. Figure 6 shows the Desktop SQL Server Configuration tool, giving you access to all configuration settings in SQL Server. I don't consider this tool a replacement for Enterprise Manager, but it does its job in situations where the only tools available are Access and MSDE, or the Desktop Engine. The SQL Jr. Admin tool, as you can see in Figure 7, provides a similar approach, but using SQL-DMO in this case. The technique used in SQL Jr. Admin can be very useful to create an administration tool that exposes only the features that a particular administrator needs to use.

Sample Resolver
Teaching the SQL Server Administration courses, I always have some students asking about how Merge Replication deals with conflicts, and I used to answer with the typical "You can write your own conflict resolvers using VB to cover your specific needs." Frankly, I'm glad I've always had a response such as a knowing chorus of "Ah!" because it's not that simple—at least it isn't something you'd want to explain (or try to absorb) on a Friday afternoon. The Sample Resolver application shows how to use the Conflict Resolver Library, included in SQL Server 2000, to create your own merge replication resolver using VB. This sample shows how to implement two resolvers: the Additive Resolver and the Minimum Resolver.

Simple Log Shipper
One of the main features of the previous Resource Kit was LogShipping. This is now a standard feature in SQL Server 2000, but in some scenarios, this tool can be a bit too complex to use. The Simple Log Shipper tool is a very simple solution, implemented entirely in T-SQL, and is suiTable for systems that require fault tolerance without very much administration overhead.

Stored Procedure Builder
Developers who are working in teams often work with some versioning control tool, such as Visual SourceSafe, but using this tool to control creation of stored procedures and versioning typically requires using Visual InterDev, which, frankly, isn't very natural for SQL Server programmers. [See David Brennan's May 2001 article, "Script Writing in Hollywood," for an alternative that doesn't require Visual InterDev.—Ed.] The Stored Procedure Builder utility works with the Visual SourceSafe repository.