Do you want to consolidate data across multiple databases ?

Do you want to have automatically reports about your managing servers ?

Try Sql Consolidate!
Just modify/add ConnectionSql and run every ProcName you want in the exportxml file . Data will be automatically appended to any ExportResult you will define!

After an idea of Radu Calian.


---------------------------
Long description :


The problem

There are actually two kinds of problems:
1. Let’s suppose that you are a database administrator and you have >3 sql servers in different locations to take care of. Let’s suppose that you have a job that performs a backup every night at 4:00 AM – and you want to know in advance (at 17:00 PM) if there is or not free space on the server. Or you want to see daily if the indexes must be rebuild.
2. Let’s suppose that you are in the IT department and you have > 3 databases , in > three different cities( Washington, Paris, London) to develop custom reports for business - let’s say that you must report for each locations the total number active Clients from table Client.You can do that with sql server reporting services – but you must connect to the locations. More, you want to have the historical data.

The idea

It is not very difficult to see that both problems have same hypothesis:
1. You have data on one or more sql servers
2. The servers must be easily add/deleted in order to have data
3. Same sql script must be run on all sql servers – and the name of the database/sql server or other data to be returned with data in order to uniquely identify the problem
4. The data must be returned in a format that can be easily transformed – XML/CSV/Database/ even an EMAIL to be send
5. Data can be retrieved asynchronously, on multiple threads – we can start one thread to retrieve data for each Sql Server or put a maximum number of threads


The solution

Being a programmer and a database administrator I have made a project in C# that can do the previous steps. I have put on http://www.codeplex.com/SqlConsolidate . Just download and unzip the zip file .The solution is composed by a SqlConsolidate.dll that performs all the work , a DOS executable that run the dll with the file passed as an argument and a XML file that you can modify at will in order to execute the procedure.
If you want to have a preview to see in action, please edit the ExportXML.xml file(notepad will be fine…) and edit the
<Connections>
<ConnectionSql Name="first bd" Encrypt="" Connection="Data Source=...;Initial Catalog=...;Persist Security Info=True;User ID=...;Trusted_Connection=true;Password=....;Application Name=internal" />
<ConnectionSql Name="second bd" Encrypt="" Connection="Data Source=...;Initial Catalog=...;Persist Security Info=True;Trusted_Connection=true;Application Name=internal" />
</Connections>
with some Real Connection strings in Connection – fill the Data Source, Initial Catalog and others.
Then run the following command
SqlConsolidateDos.exe exportXML.xml
from the command prompt in the folder you unzipped the file and check your c: drive .If it is ok, there will be an andrei.csv file . If not, please consult the log (more details later on)
The execution
Let’s commence : You have 3 sql servers Washington, Paris, London and you want to see the free space. By searching , you have the following sql :
EXECUTE master..xp_fixeddrives
Now you must return this results as a select in order to save as XML/CSV/etcaetera . So , why not use a temporary table in order to insert the results and then select from here ?
Create table #DrvSpace (
DriveLetter char(02) null,
MB_Free float null
)
INSERT #DrvSpace EXECUTE master..xp_fixeddrives
- - now select with server name and database name and other details you want
select *, @@ServerName as ServerName,DB_NAME() as DatabaseName,getdate() as ServerDate ,
case
when DriveLetter = &#39;C&#39; then -- on C only 4000
case when MB_Free &lt; 4000 then &#39;Bad&#39; else &#39;Good&#39; end
else
case when MB_Free &lt; 10000 then &#39;Bad&#39; else &#39;Good&#39; end
end as OK
from #DrvSpace
drop table #DrvSpace

OK , now the technical problem of retrieving data is done – just configure the file that have all information that must be executed – please edit the ExportXML.xml file with :
1. <ProcName>dbo.MonitorSpace</ProcName> -the name of the procedure that must be executed and return results as one or more “select <fields> from <table>”
2. <BeforeExecute> …. </BeforeExecute> - the text that is executed on each SQL Server in order to create the <ProcName> ( see 1.) that is executed . See the exportxml.xml for an example for creating dbo.MonitorSpace
3. <ThreadNumber>…</ThreadNumber> - number of threads to retrieve data to be executed simultaneously
4. <ExportResultsBase > Can contain more that one export. Let’s see, for an example ,the export in CSV :
<ExportResultsBase xsi:type="ExportCSV">
<ExportFilter><string>DriveLetter = &#39;C&#39;</string></ExportFilter>
<ExportDir>C:\</ExportDir>
<ExportFileName>andrei.csv</ExportFileName>
</ExportResultsBase>

The ExportDir is where to save file, ExportFileName is how you want to name the file and ExportFilter is supposed to make a filter on data (in that case, exports only data from C: DriveLetter = ‘C’ )
The export are for now in XML, CSV, HTML, email. For database export the table/tables must be created first and have column names/types identically with the select column names - in our case DriveLetter , MB_Free, ServerName, DatabaseName, ServerDate, OK .
5. And finally the real part: <Connections> . You can add as many database connections as you have there by adding a new <ConnectionSql . The name is whatever you want to, Connection is a Sql Server connection stringi and Encryption is for a future release , if you want the connection encrypted/decrypted
Now save the file as what name do you want(newexport.xml, for example) and run SqlConsolidateDos.exe newexport.xml
Possible problems
1. If you xml file is not well formed , you can see “There is an error in XML document”
2. If you want to put apostrophes , please use &#39; ( see the example in BeforeExecute)
3. All the things that are executed are in the log file in the log directory
4. If there are errors, there are in the log file and the exit code is not 0
Possible uses
1. You can export data from a single sql server
2. You can put the execution SqlConsolidateDos.exe newexport.xml in a task scheduler and execute at 4:00 at clock in the morning
3. Solution of the 1 and 2 problems at the beginning of this article

Last edited Mar 5, 2009 at 7:29 PM by ignatandrei, version 5