DMZ and SQL Server
Authentication
My next enhancement (i.e., support for SQL Server
authentication for non-Windows domain accounts
in DMZ environments in which Windows credentials
might not pass through) was a rather simple addition.
I added two data sources that would use SQL Server
authentication to connect to the DMZ and duplicate
the existing Control Flow tasks.
When the updated package runs, the first phase
truncates the local repository tables (as in the original
solution). Next, the Windows-authenticated objects
execute and complete. Then, the package populates
the same variable that was used for the Windows-authenticated
server list, but now only the servers in the
DMZ require SQL Server authentication. The following
code contains the query that populates the DMZ
servers:
SELECT LTRIM(RTRIM(Server)) AS Servername,
FROM ServerList_SSIS
WHERE 1(Connect = 1) AND (DMZ = 1)
Notice the DMZ flag is set to 1 for true.
After you load the DBA_Server_Load SSIS project
that populates the DBA_Rep database, you must
configure the appropriate DMZ SQL Server-authenticated
credentials for this enhancement to work in
your environment. Because the Connection Manager Server name property is dynamically configured
at runtime, you can overwrite the two Connection
Manger sources (i.e., MultiServer_DMZ and Multi-
Server9_DMZ) with valid SQL Server account names
at design time simply to save the SQL Server user account password. In fact, doing so is actually necessary
the first time you run the SSIS package.
You need to enter a valid server name and SQL Server
authenticated user name and password to initiate the
first connection. I chose ‘sa’ because it’s a known, usable
SQL Server administrative account. Your account might
be a different SQL Server authenticated administrative
account. After you save this account information, it will
be used at runtime. However, the server is dynamically
set based on the variables you use for the Server name
connection property. Web Figure 1 (www.sqlmag.com,
InstantDoc ID 97840) shows the entire DBA_Rep SSIS
package with annotations for the package flow.
Location
I’ve been hoping for a long time that future versions
of SQL Server would include an area for descriptive
data about the installation—information that can’t
easily be gleaned elsewhere, such as a server’s geographical
location. DBAs typically work around the
unknown location problem by adhering to a naming
standard that includes OS version, application, and
location. For example, a Windows Server 2003 SQL
Server machine located in Denver might be called
W2K3SQLDEN1. Some quick string parsing would
reveal DEN in the server name and could be used as
a location trigger.
The problem with this solution is that even though
you might have 90 percent compliance with the nomenclature,
you’ll always have a few stragglers that
lack a location-defining name. So until SQL Server
includes this description or categorical storage area,
you must manually track servers’ locations. Thus, I
added a location field to the updated DBA_Rep SSIS
package and repository database so that you can use
locations in reports or queries.
The Server_Location table consists only of a LocationID
and Location (string) column, which refers
to the city in which the server resides. The LocationID
values are stored in the ServerList_SSIS table—and although this table is initially populated manually, the
package will update the SQL_Servers table after the
package completes execution with the values from the
ServerList_SSIS table. The following code contains
the query that runs as the last step of the package to
update the SQL_Servers table from the LocationID
value stored in the ServerList_SSIS table:
UPDATE dbo.SQL_Servers
SET dbo.SQL_Servers.LocationID = dbo
.serverlist_ssis.LocationID
FROM dbo.serverlist_ssis
INNER JOIN dbo.SQL_Servers ON dbo
.serverlist_ssis.Server = dbo.SQL_
Servers.Server
New Data to Play With
In addition to the schema changes necessary to provide
SQL Server 2005 and SQL Server 2000 support
and DMZ connectivity, I added several new tables to
the DBA_Rep repository database. These tables categorically
store two of the most frequently requested
pieces of information: users’ permissions and disk
space utilization.
In any large organization, especially one that has
a lot of turnover, quickly ascertaining database or
server access rights at the user or group level is essential
to safeguarding data. Large companies with many
users typically have numerous SQL Server machines
throughout the network. And even when preventive
measures are implemented, unmitigated database and
log file growth can occur because of poor planning, unexpected large data load volumes, or failed maintenance
tasks to reduce file sizes.
To address both security and disk space management,
I added several key reports to the repository.
But before I discuss these reports, let’s look at how
one of the tables, SQL_Drives, is populated within
the SSIS package. The same technical methods are
used to populate the new SQL Server user tables.
As you can see in Figure 3, two tasks are encapsulated
in a ForEach Loop container. This container
rolls through the tasks for each server derived from the Server name variables, and in this case initiates a SQL
Server execution task called Fixed Drives to populate
a temporary table, then runs a Data Flow task called
Fixed Drives Load to pull the data from the temporary
table to the DBA_Rep table called SQL_Drives.
The reason I had to use a temporary table was because
of the need to execute an extended stored procedure,
xp_fixeddrives, for which there wasn’t a valid result set
without a temporary table.
It wasn’t possible to execute the xp_fixeddrives
stored procedure directly in the Data Flow task
and provide all the data I wanted—specifically, the
Server, Disk_Drive, and MBFree columns. The xp_
fixeddrives stored procedure returns only the latter
two pieces of information. But because I had no way
to tie a server name to the repository, this information
would be meaningless. My solution, which Web
Listing 1 shows, was to create a temporary table in
an Execute SQL task and populate it with the required
data. Next, I fed the results of this query as
input columns to the Data Flow task called Fixed
Drives Load, which populates the SQL_Drives table
in the repository. I included the code that executes
the xp_fixeddrives stored procedure into the temporary
table, RESULT_STRING_FD, then uses
SERVERPROPERTY(‘Servername’) to update the
server information. The results of the query in Web
Listing 1 are fed as input to the OLE DB destination,
which is the SQL_Drives table in the DBA_
Rep database, via the Data Flow task in the
DBA_Server_Load SSIS package.
After the package executes, you can use the
following code to query the results from the SQL_Drives table:
select SD.Server,
Drive_Letter,
MbFree
from sql_drives SD
shows the results of this query. With the
data already gathered for database information
(which was part of the original solution), you
can now tie the database sizes to the space remaining
and create a threshold value that you
can monitor.
Putting the Data to Use with
Reporting Services
I needed an at-a-glance report that would tell me (1)
which of the drives on each server was potentially getting
low on free space and (2) which server was hitting
a threshold of data and log file sizes as it pertained to
the free space. The driving query for this report, which
Web Listing 2 shows, returns only a few columns,
mostly calculations of the free space and database file
sizes per drive per server.
Note that the query includes the Size field from the
Database_Info table. This size is shown in 8KB pages
that require division by 128 so that the size matches the
megabyte value of the MBFree column. The percentage
calculation is derived by dividing the sum of the
database size by the total of MBFree, plus the sum of
the database size again, times 100. Note, also, that the
outer join between the two tables, Database_Info and
SQL_Drives, is accomplished by linking the Drive_
Letter field from SQL_Drives with the first character
of the FileName field in Database_Info. The first letter,
derived with the LEFT function, is the drive letter
where the database or log file is located.
Continue to page 3
Prev. page
1
[2]
3
next page 
