I recently (2 weeks ago) took a class from my friend Kalen Delaney on SQL Server Internals.  It was 5 days and she really packed the material in there.  I really like in depth training because it makes me think harder and I excel faster for some reason in my learning.  Well I have always found that conferences and training classes are great for the week that you are there, but then you get back home, and go back into the office.  Then after the 1st week back, it is easy to lose what was learned.

I have also found that if I will come home and teach or apply what I have learned in some way, that it will be retained for a greater amount of time, and maybe even never lost.

So after taking Kalen's class on internals, I remember at the last part of the week, she taught us about SARGs (Search Arguments and how they can affect the usage of indexes).  Recently at work, there was a query that came across my desk and in essence here is what it looked like:

SELECT DISTINCT o.OrderID, o.PostDate, o.ShipMethod, o.Status, ISNULL(o.CenterID,0) AS CenterID
FROM dbo.Orders o
WHERE WarehouseID = 99
      And [Status] = 'Shipped'
      And ISNULL(Iscredit,0) = 0
      And CAST(CONVERT(char(8), ISNULL(PostDate,orderdate),112) AS datetime) BETWEEN '2008-08-24' AND '2008-08-25'
ORDER BY o.OrderID

There were indexes on the Date fields and the table has about 3 Million rows in it.  Now when this query is done, it returns 4 rows of the 3 Million, and has a query plan that includes just 3 operators, a Table Scan (84%), a Compute Scalar (4%) and  a Sort (11%).  But the query returns in 30 seconds, and that just won't do.

[I will insert the query plan image in later]

So I applied the concept of the SARG that I learned from Kalen to encourage the optimizer to get a better plan and use the indexes.  Here is the modified query.

Recently I have had a great need to see inside about 39 servers (Production, Dev, Test, etc) and find out things like

* How many logins have Sysadmin privileges
* How many users have the db_owner role
* How many databases total do we have in the Enterprise
* How much space is being used in total by Databases and their Log files

With this in mind, I came across the article in February in SQL Server Magazine about the DBA Repository and have not had enough time to really dive into it.  But now I have had a chance and a great need, so amidst my powershell and SMO work to get the information I want, I have also leaned on this SSIS package to gather this data.

Those who have not seen this or loaded it for actionable results, it comes with an SSIS package to load the information and a SSRS reporting project that displays some of the data in the tables.  The reporting is interesting, but not yet complete, so I will be venturing there next to get some good reports that can show me some exceptions that are out there and also some of the security concerns.  I will also be tweaking it to allow me to keep a history of the Drive space and the size of the databases to see when they grow, etc. But most of this is for trending of the different statistics kept inside the tables of the database repository.

I have made some changes for those that use this repository. 

These include:

* Added a GroupID field from the sysfiles into the Database_Info table so that I can separate Data and Log groups to know how much space is Datafile and how much is Log file
* Changed the query for SQL 2000 jobs so that I got the jobs for SQL 2000. For some reason it failed to execute so I change the query now it does not fail
* Change the Validation to Delayed so that the generic connections for 2005 and 2000 would not try to connect to the servers, when the connection was incorrect because it was missing the server by design
* Added a few stored procedures and reports for getting some data back the way I wanted to

I will be updating this package and the reporting package as I go along and will be releasing it to the Author and to the wild with permission from the original author, so stay tuned.  For the information it gathered, and for 39 servers my workstation ran it in 8 minutes for all the data.

But the other reason for writing this blog entry is that when I put the package into the SSIS store, I ran into something that I did not know about.  When you have a named instance of SQL instead of a default instance on your machine and there is no default instance, expanding MSDB inside a connection to SSIS, causes an error because it is looking for the default instance.  Most DBAs know that when you connect to SSIS you connect to the machine name and not to an instance and that there is ever only 1 instance of SSIS on a machine, so this could be a problem when you want to store a package inside MSDB.

In the documentation of SQL (BOL), there is hidden a little note on how to get this to work.  In the directory (default place without change on install) C:\Program Files\Microsoft SQL Server\90\DTS\Binn there is a file called MsDtsSrvr.ini.xml.  In this file there is a property that you must change to get it to connect to your instace to get at MSDB.  It is <ServerName>.</ServerName>.  By default it is pointing to a . (period) which is the local default instance.  Because I did not have one, I had to change it to point to my instance .\DESK.  I saved the file and restarted SSIS and I could then expand the MSDB and store the package in the SSIS Package Store.

The other wierdness I came across is that I wanted to schedule it in a job.  The only option by default is to run the step as SQL Server Agent, which is not a very privileged account on my local machine.  So it would not run anything against other servers, just mine.  So I read up on Credentials and Proxies in SQL 2005 and found out that I could create a Credential with a user that had permissions on the other servers (Windows Permissions) and after creating a credential, I created a proxy in SQL Server Agent and told it to use the Credential.  Then I edited the Step and in the dropdown for the Run As... there was my proxy, and sure enough my job with the SSIS package ran successfully, and I am a happy camper.

I decided to finish the scripter even though I could not get the Add-Content to work without giving me a multitude of ??? in the file.  I decided that I could script out the Tables, Views and DB and also output the contents of the INSERT statements into 2 files instead of 1, then concat the files together or just use one for the DDL and the other for the DML.  (good separation of Schema and Data huh?) 

To run this and get both outputs, you can run it like this:  (inside Powershell)

PS C:\Scripts > ./ScriptDB.ps1 >somefile.txt

Have a great one and enjoy.

# ScriptDB.ps1
# Scripts db, tables, views in database of your choice
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null

$dbname = "Database Name"
$filename = "c:\temp\output.txt"
$serverName = "Server Name"

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]

$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"

$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.DriAll = $True
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.IncludeHeaders = $true
$options.AppendToFile = $false
$options.FileName = $filename
$options.ToFileOnly = $true

$scr.Options = $options
$scr.Script($db)
$scr.Script($db.Tables)
$options.AppendToFile = $true
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scr.Script($views)

$tree = $scr.DiscoverDependencies($db.Tables, $True)

$depwalker = New-Object "Microsoft.SqlServer.Management.SMO.DependencyWalker"
$depcoll = $depwalker.WalkDependencies($tree)

#Using the sp_generate_inserts from the webpage http://vyaskn.tripod.com/code.htm

$col = $depcoll | foreach {
 "EXEC sp_generate_inserts '" + $_.Urn.GetAttribute("Name") + "'"
}

$ds = New-Object "System.Data.DataSet"
$ds = $db.ExecuteWithResults($col)

# using the Piping features to go from Tables, to Table, to Rows, to Row and to Column 1 $_[0]
$ds.Tables | foreach { $_.Rows | foreach {$_[0]} }

In trying to squeeze performance out of the SMO stuff that I have been learning and doing for SQL Server I came across the method on the Server object called SetDefaultInitFields.  In my previous posts on Powershell or .NET and SMO, when you start scripting Views and Stored Procedures you start to get into areas where there are great amounts of System Objects.  In order to not script them out in the scripter, you either need to iterate over the collection or check IsSystemObject then script them, but either way you do it, you have to worry about the round trips to the server for more properties.

By default SMO gives minimal properties back to you from the server when going after collections of objects.  The SetDefaultInitFields allows you to specify a Type of object and a string array of properties to put in the return by default so that round tripping is not necessary. (See Michiel Wories blog post).  The challenge for me was to figure out how to use typeof() (C#) in Powershell.  I found a couple of references to creating a PS Add in which would work, but I just needed it to pass a type so that the method would work.

I found out in a forum post long ago (2006) that if you want to do a typeof(Microsoft.SqlServer.Management.SMO.StoredProcedure) then you would just do this :

$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")

This line tells the server to return IsSystemObject as part of the properties that come back for the collection, which by default does not get returned, and when I query the property, it will get it from the object instead of asking the server about it, and it cut my execution time from 2 minutes to 10 seconds.  This is a great amount of savings when you have thousands of procedures and then you add in all the System Stored Procs, what a time killer and the SetDefaultInitFields is a time saver.

Thanks to Allen White and his talk at PASS, I have been inspired in my plight to create a complete build script of the database that I am in charge of for a dev project at work.  So I wanted to start by scripting the tables that exist in my database.  I was going to have it be configurable but that is later on.  My end product will actually derive the Foreign Keys and script the PK tables first so that the FK tables do not load first, and also it will create the INSERT statements for all the data that is in the database... So stay tuned for the rest of the parts.

So goes PART 1:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using Microsoft.SqlServer.Management.Smo.CoreEnum;
using System.Configuration;
using System.Collections.Specialized;

namespace Application_Build
{
    class Program
    {
        static void Main(string[] args)
        {
            string dbName = ConfigurationManager.AppSettings["databaseName"];
            
            Server srv = new Server();
            srv.ConnectionContext.LoginSecure = false;
            srv.ConnectionContext.Login = ConfigurationManager.AppSettings["loginName"];
            srv.ConnectionContext.Password = ConfigurationManager.AppSettings["loginPassword"];
            srv.ConnectionContext.ServerInstance = ConfigurationManager.AppSettings["serverName"];
            
            Database db = new Database();
            db = srv.Databases[dbName];
            
            StringBuilder sb = new StringBuilder();

            foreach(Table tbl in db.Tables)
            {
                ScriptingOptions options = new ScriptingOptions();
                options.ClusteredIndexes = true;
                options.Default = true;
                options.DriAll = true;
                options.Indexes = true;
                options.IncludeHeaders = true;

                StringCollection coll = tbl.Script(options);
                foreach (string str in coll)
                {
                    sb.Append(str);
                    sb.Append(Environment.NewLine);
                }
            }
            System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
            fs.Write(sb.ToString());
            fs.Close();
        }
    }
}

With the app.config for the config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="loginName" value="sqladmin"/>
<add key="loginPassword" value="sql123ms"/>
<add key="serverName" value="(local)\SQL"/>
<add key="databaseName" value="INFOGUIDE" />
</appSettings>
</configuration>

This simply uses the Table object and scripts to a StringBuilder so that I can do something with the text afterwards. I know that there are many other ways of doing this, but this is a learning process to learn a new thing. SMO scripting is a very powerful thing and I think I will translate this to Powershell since it is the new thing to use in scripting.  Allen did part of his session on Powershell as well.

Stay tuned for Part 2... where I will include a Powershell version and also add in the ordering of it by FK-PK relationships to script them in the right order.