Using the ADSI SQL dialect to query AD has a few limitations. For example, you can't use the SELECT * syntax; you must specify the columns in your SELECT list. Also, you can't use ADSI SQL to update AD; you must use ADSI instead. The latter restriction also applies to ADSI linked servers. For more information, see SQL Server 2000 BOL (Updated-SP3) under "OLE DB Provider for Microsoft Directory Services." Finally, because AD is hierarchical, using ADSI functions to return information about parent containers is easier than using strictly the ADSI SQL dialect.
The VBScript code in Listing 3 uses a combination of ADO, ADSI SQL, and ADSI functions to retrieve the name of the server on which the database is located. First, the code uses an ADSI SQL query to obtain the ADsPath for the mS-SQL-SQLDatabase container, which holds the Northwind database. After obtaining the ADsPath, the code sets ObjADSI to that path. The code then uses the ADSI Parent property to get the mS-SQL-SQLDatabase parent container MSSQLSERVER. After obtaining the ADsPath for mS-SQL-SQLServer, the code again uses the ADSI Parent property to obtain the server name or Common-Name, which is stored as the cn attribute.
Finding an Environment
Suppose you want to register three environments (production, development, and quality assurance—QA) of a particular database. For example, you might have a production version, a development version, and a QA version of the Northwind database with the same database name across all environments. How do you determine whether the database you're looking at is the production or development version? You could store this information in one of the mS-SQL-SQLDatabase attributes. Note that not all AD attributes are user-updateable; some attributes can be updated only by the Directory System Agent (the system process that provides access to AD). To determine whether a particular attribute is user-updateable, see the documentation about the AD class attribute.
The built-in attribute Flags is updateable, so you can use this attribute to store environment indicator information. For this example, I'll use an indicator code to represent different environments, where 1 is production, 2 is development, and 3 is QA. The VBScript code in Listing 4, page 18, illustrates how you can set the mS-SQL-SQLDatabase attribute Flags by using the ADSI properties Put and SetInfo. The code retrieves the new values from AD. You can then register multiple instances of the same database for each environment; you can differentiate among them by modifying the Flags attribute. For example, the flag attribute for my development environment would be 2, and the flag for my QA environment would be 3.
Locate and Connect to SQL Server
You can modify the code from Listing 3 to look for the correct database environment for the Northwind database and dynamically make a connection to a SQL Server based solely on a database name in the database environment. The code in Listing 5 uses an ADSI SQL query to obtain the ADsPath for the Northwind database that has the Flags attribute set to 1 (denoting the production environment). Then, the code uses the ADSI Parent property to get the mS-SQL-SQLDatabase parent container MSSQLSERVER. After obtaining the ADsPath for the mS-SQL-SQLServer object, the code retrieves the port number SQL Server is running on, then uses the ADSI Parent property to obtain the server name or dNSHostname. Having obtained the SQL Server name and port number, the code makes a trusted connection to the SQL Server. The download that accompanies this article includes a Visual Basic (VB) 6.0 COM DLL and VBScript code, which perform the four tasks I've demonstrated: get SQL Servers, get databases, get data source, and set flag attribute. Using a DLL overcomes some of the slow performance you'll notice when you use VBScript exclusively.
Publishing a service is one of AD's most powerful features. When you publish a SQL Server as a service in AD, you can design client applications to dynamically locate databases through Service Publication and lookup, thereby eliminating manual client-configuration changes. In turn, by simply updating the database location in AD, DBAs can more easily move databases to another server without any effect on applications that use AD to dynamically locate a database.
By publishing a SQL Server in AD and developing applications that use Service Publication, you can eliminate the need to store static configuration data on each client. And you have an increased flexibility to reconfigure database locations and reduce downtime when migrating databases between server environments.