Unified Database Toolkit – Connection definition.

This is the second article relating to my Unified Database Toolkit. We have seen in the previous article, Unified Database Toolkit - Scheduling, a simple way to create jobs in Windows scheduler from within Microsoft database engine. But before we take a look at how this database tool will be generating script files and associated batch files and then schedule them, we need something else. We need to introduce a connection standard to use to open connections and run scripts on the remote or local database engine.

The first thing to know is that the included stored procedure is using a standard way to connect and run scripts to different RDBMS products. You will notice that I have furnished basics in the procedure to connect to Oracle, SQL Server, and MySQL. (Our bug tracker system is mounted on MySQL, my luck!!!) Other systems can also easily be defined by inserting the proprietary tool command line particularities to the right columns into DBT_COMMAND table. The dbtsp_validateConnection procedure will be use as an example to explain the generic method.

General reminder

The puzzle pieces necessary to form the Database Toolkit automation concept will come into form with future articles. Just remember that the core concepts explained in those related articles can be used to put in place an automated “close to zero impact”¹ database administration system of your own. Running administration commands to remote systems but also collecting statistics and system parameter settings in a dedicated MSDE or MSSQL database. In time, with minimal efforts you should be able to implement and personalize those concepts to your needs in terms of statistic and administration automation as well as intelligent notifications. At one point you will get the most common SQL Server administrative and maintenance tasks independently scripted and designed to be executed by the Database tool kit core components. Other database engine scripts will not be included.

Security reminder

When you are working to automate connections to remote machines you should keep in mind that it implies dealing closely with security issues. This means that when the system is defined to use a database login, it must be using a password too. This way of connecting to the database system cannot be secure in this context because it implies storing this password in the tool database but also in clear text either in the batch file, or in the scheduled task. Some RDBMS are not compatible to Windows integrated security. In those situations you must restrict your system to limit the possibility of others being able to see the content of the files and the tool database. On the other hand when integrated security is used, the Windows scheduler service must be started with a domain account that will have administrative privileges to the remote database server you intend to have the script run from. If other users can log to this machine, I recommend that you restrict them from creating or running tasks with the appropriate Windows policies.

Procedure explained

As usual, for simplicity and the purpose of this article, I incorporated in the procedure the dependant object creation and initialization needed to be able to run the procedure successfully.

In summary what the procedure does is build the string based on the definition of the command line tool to use, which is stored in the DBT_COMMAND table. Then it checks if all the given parameters are good by opening a connection and exiting right away. That’s easy. This procedure is in fact used internally to validate primary connection parameters prior to running other procedures responsible for initializing guarded object configuration tables at server declaration.

You might also find interesting the way xp_cmdshell system extended procedure is used. xp_cmdshell returns 0 in case of success or 1 for failure. It also has a parameter to instruct the command not to return the output produced by the command call (NO_OUTPUT). Instead of flushing that result and not having any way of knowing what went wrong when there is a failure, you can store this output to a table at the same time you are collecting the returned error code.

insert into #OUTPUT exec @iError = master.dbo.xp_cmdshell @vcCommandToRun

You can then raise a better error or store the message to a log table. You can parse it and trigger a command in response to the collected output information.

Notes:

1. Why “close to zero impact”: because I do not believe in zero impact. As soon as you connect to a machine there is an impact. The Unified Database Toolkit concept is based on: 1) connecting to a remote machine, 2) get the needed data out of it and 3) save it to the local machine. There is an impact on connection, another one depending on what the script is going to be collecting or doing and one on the network depending on how much data is being pump out. From there data can be loaded into the Tool database for statistic or analysis purpose. Impact is on the local machine.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbtsp_validateConnection]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dbtsp_validateConnection]
GO
CREATE procedure [dbo].[dbtsp_validateConnection]
-- automated system inputs
 @QUEUE_ID int = 0
 ,@COMP_ID int = 0
 ,@OBJ_ObjectName nvarchar(128) = ''
 ,@TBL_TableName nvarchar(128) = ''
 ,@TBL_OwnerName nvarchar(128) = ''
 ,@DB_ID int = 0
 ,@SRV_ID int = 0
 -- User inputs
 ,@ServerName nvarchar(128) = @@SERVERNAME
 ,@DBMS_Name varchar(10) = 'MSSQL'
 ,@SRV_IP varchar(15) = null
 ,@SRV_Security bit = 1
 ,@SRV_Logon nvarchar(128) = null
 ,@SRV_LogonPassword nvarchar(128) = null
AS
/***********************************************************************
** Creation Date: 05/15/2002
** Modif. Date  : 11/03/2004
** Created By   : avigneau
** Database     : Database Tool Kit DB
** Description  : To verify connection parameters and server existance.
** Parameters   : 
      @ServerName is the db server name you use to connect to a particular db server
@DBMS_Name is of 'MSSQL', 'MYSQL' or 'ORACLE' (see below)
@SRV_IP can be used to connect when name resolution is not responding
@SRV_Security 0 for DB security, 1 for NT
@SRV_Logon DB user login if above parameter = 0
@SRV_LogonPassword for security supported with the use of an encrypt decrypt 
extended proc (not covered)
** Dependenties : dbo.DBT_COMMAND
** Compatibility: MSDE, SQLExpress, SQL Server 7 and up Windows NT4 and up.
** Remark       : This ain't perfect :( does not work well with oracle command line tool
** Example      : exec dbo.dbtsp_ValidateConnection 
@ServerName = TheServerName, 
@DBMS_Name = 'MSSQL',@SRV_Security = 1
*****************************************************************************/-- Set session environment section
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET NOCOUNT ON
SET ANSI_DEFAULTS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET ARITHABORT ON
SET DATEFORMAT YMD
-- Declaration section
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
declare @iError int, @vcCommandToRun varchar(1024)
, @vcErrorMessage varchar(1024), @iRowCnt int
create table #OUTPUT(
rowid int identity(1,1) not null,
result varchar(1024) null)
-- Initialization section
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- I prefer to make procedures (that are used as tools) the more independant as possible. 
-- Not like an application design that need strict data modeling control.
if not exists (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = N'dbo'
AND TABLE_NAME = N'DBT_COMMAND' 
AND TABLE_TYPE = N'BASE TABLE')
 begin
create table dbo.DBT_COMMAND ( 
DBMS_Name varchar(10) not null,
DBMS_CommandName varchar(10) not null,
DBMSServerSwitch varchar(128) null,
DBMSDBSwitch varchar(128) null,
DBMSUserSwitch varchar(128) null,
DBMSPwdSwitch varchar(128) null,
DBMSTrustedSwitch varchar(10) null,
DBMSCommandSwitch varchar(128) null,
DBMSFromFile varchar(256) null,
DBMSToFile varchar(256) null,
DBMSErrFile varchar(256) null,
DBMSFormatFile varchar(256) null,
DBMSTestSwitch varchar(128) null) 
alter table dbo.DBT_COMMAND
add constraint DBT_COMMAND_PK primary key (DBMS_Name)    
 end       
--Spaces has to be saved because some connection needs them and some not too specific for dynamic construct
IF NOT EXISTS(SELECT 1 FROM dbo.DBT_COMMAND WHERE DBMS_Name = 'MSSQL')
INSERT INTO dbo.DBT_COMMAND (
DBMS_Name,-- varchar(10) not null,
DBMS_CommandName,-- varchar(10) not null,
DBMSServerSwitch,-- varchar(128) null,
DBMSDBSwitch,-- varchar(128) null,
DBMSUserSwitch,-- varchar(128) null,
DBMSPwdSwitch,-- varchar(128) null,
DBMSTrustedSwitch,-- varchar(10) null,
DBMSCommandSwitch,-- varchar(128) null
DBMSFromFile,-- varchar(256) null
DBMSToFile,-- varchar(256) null   
DBMSErrFile,-- varchar(256) null   
DBMSFormatFile,-- varchar(256) null   
DBMSTestSwitch -- varchar(128) null
)
VALUES ( 'MSSQL',
            'OSQL',
' -S',
' -d',
' -U',
' -P',
' -E',
' -h-1 -s \t -w8000',
' -i',
' -o',
null,
null,
' -qexit'
)
IF NOT EXISTS(SELECT 1 FROM dbo.DBT_COMMAND WHERE DBMS_Name ='MYSQL')
INSERT INTO dbo.DBT_COMMAND ( DBMS_Name,-- varchar(10) not null,
DBMS_CommandName,-- varchar(10) not null,
DBMSServerSwitch,-- varchar(128) null,
DBMSDBSwitch,-- varchar(10) null,
DBMSUserSwitch,-- varchar(10) null,
DBMSPwdSwitch,-- varchar(10) null,
DBMSTrustedSwitch,-- varchar(10) null,
DBMSCommandSwitch,-- varchar(128) null)   
DBMSFromFile,-- varchar(256) null)   
DBMSToFile,-- varchar(256)null)   
DBMSErrFile,-- varchar(256)null)   
DBMSFormatFile,-- varchar(256) null   
DBMSTestSwitch -- varchar(128) null
      )
VALUES ('MYSQL',
            'MYSQL',
' --host=',
' --database=',
            ' --user=',
            ' --password=',
            '',
            ' --skip-column-name --batch',
            ' < ',
            ' > ',
            null,
null,
            ' --execute=quit'
            )
IF NOT EXISTS(SELECT 1 FROM dbo.DBT_COMMAND WHERE DBMS_Name ='ORACLE')
INSERT INTO dbo.DBT_COMMAND (DBMS_Name,-- varchar(10)not null,
DBMS_CommandName,-- varchar(10) not null,
            DBMSServerSwitch,-- varchar(128) null,
            DBMSDBSwitch,-- varchar(10) null,
           DBMSUserSwitch,-- varchar(10) null,
            DBMSPwdSwitch,-- varchar(10) null,
            DBMSTrustedSwitch,-- varchar(10) null,
            DBMSCommandSwitch,-- varchar(128) null)   
            DBMSFromFile,-- varchar(256) null)   
            DBMSToFile,-- varchar(256)null)   
            DBMSErrFile,-- varchar(256)null)   
            DBMSFormatFile,-- varchar(256) null   
            DBMSTestSwitch -- varchar(128) null
            )
    VALUES (
           'ORACLE',
           'SQLPLUS',
           '@',
           '',
           '',
           '/',
           '',
           '',
           '@',
           ' > ',
  null,
  null,
  ' -L'
        )
-- Script logic section
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- built the command to execute at the DOS prompt 
select @vcCommandToRunn = DBMS_CommandName + DBMSTestSwitch +
CASE WHEN @SRV_Security = 1 THEN DBMSTrustedSwitch
ELSE DBMSUserSwitch + ISNULL(@SRV_Logon,'') + 
DBMSPwdSwitch + ISNULL(@SRV_LogonPassword,'')
END +
DBMSServerSwitch + @ServerName
 from dbo.DBT_COMMAND
 where DBMS_Name = @DBMS_Name
-- put the output to a table so you get the error message to display at your convenience
insert into #OUTPUT exec @
iError = master.dbo.xp_cmdshell @vcCommandToRun
-- built the command with the IP this time to execute at the DOS prompt 
if @iError = 1 AND @SRV_IP is not null
 begin
select @vcCommandToRun = DBMS_CommandName + DBMSTestSwitch +
           CASE WHEN @SRV_Security = 1 THEN DBMSTrustedSwitch
            ELSE DBMSUserSwitch + ISNULL(@SRV_Logon,'') + DBMSPwdSwitch +
ISNULL(@SRV_LogonPassword,'')
           END +
DBMSServerSwitch + @SRV_IP
 from dbo.DBT_COMMAND
 where DBMS_Name = @DBMS_Name
            
insert into #OUTPUT exec @iError = master.dbo.xp_cmdshell @vcCommandToRun
end
-- this will get rid of trash line feeds from the OS
delete from #OUTPUT where result is null
-- This is to capture syntax error from Oracle tool. 
-- RISK!!! number of line returned can change between versions
select @iRowCnt = count(*) from #OUTPUT
set @vcErrorMessage = ''
-- So, connected or not? ... xp_cmdshell returns 1 in case of failure.
if @iError = 1 or @iRowCnt = 11 
 begin
select @vcErrorMessage = result +char(13)+ @vcErrorMessage
 from #OUTPUT
 order by rowid
            
select @vcErrorMessage = 'Command was: '+@vcCommandToRun+' '+ @vcErrorMessage
raiserror('%s',14,1,@vcErrorMessage)
return (1)
 end
-- next procedure calls in line
--Initialize Object Configuration
/*
init_Servers
init_Platform
init_Constant
-- script & batch file location
init_Databases
init_Tables
init_indexes
*/return (0)
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GO