I find it frustrating that the SQL Server 2005/2008 default trace is continually overwritten and there is no way to store x number of files or x MBs of data. As a workaround, I developed an SSIS package to monitor the \LOG folder and automatically archive the default trace file whenever a new file is created.
This consists of a FOR LOOP container, a Script Task and a File System Task plus a whole bunch of variables and property expressions.
The guts of the package is really in the Script Task as this is where I use a WMI query to monitor the \LOG folder for .trc files. The file is then renamed (date-time-servername-file) to another folder\share which can be a UNC structure e.g. \\server\share. This way I have a permanent record of the basic server activity for root cause analysis/troubleshooting.
The screenshot below shows the basic structure of the package.
In case you missed the Tech-Ed 2009 announcement, you can find the info here http://www.microsoft.com/sqlserver/2008/en/us/MDS.aspx
“On initial scoping, it was determined that 'Bulldog' would ship as part of Microsoft Office SharePoint in the O14 wave. At TechEd 2009, we announced a change in packaging for the new MDM capabilities. Project 'Bulldog' will now ship as part of the next release of SQL Server codenamed ‘Kilimanjaro’ as 'SQL Server Master Data Services.
This means that in addition to new capabilities such as Self Service BI and multi-server management, SQL Server ‘Kilimanjaro’ will also provide customers with a rich platform for MDM through SQL Server Master Data Services. Customers who have purchased Software Assurance (SA) should view this as net new value and innovation that they will have access to as a result of their investments in SA.”
After a few late nights, some coffee and a few review cycles, a new article has just been published on the SQLCAT site which provides an overview of the subscriber initialisation techniques for transactional replication and, more specifically using an array-based snapshot http://sqlcat.com/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx
I recently had a requirement to automate downloading a file from an website and then perform ETL on the data in the file. Fortunately, this is possible via the script task in SSIS (note that this is using SQL Server 2008 Integration Services). I found a couple of web references to do this in VB.NET but I prefer C# so modified the code and made some adjustments to suit my (debugging) needs. I set two package variables, RemoteURI and LocalFileName, to store the URL (source) and filename (destination).
This works really well and I can change the variables at run-time using property expressions
public void Main()
{
WebClient myWebClient;
string RemoteURI;
string LocalFileName;
bool FireAgain = true;
Dts.Log("entering download..", 999, null);
try
{
myWebClient = new WebClient();
RemoteURI = Dts.Variables["User::vPipeline"].Value.ToString();
LocalFileName = Dts.Variables["User::vLocalFileName"].Value.ToString();
Console.WriteLine(RemoteURI);
Console.WriteLine(LocalFileName); MessageBox.Show(RemoteURI);
MessageBox.Show(LocalFileName);
// Notification
Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);
// Download the file
myWebClient.DownloadFile(RemoteURI, LocalFileName);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Catch and handle error
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
[Non-SQL related] I’ve just discovered an interesting site called Microspotting which came to my attention courtesy of the grapevine. At first I wasn’t sure what this was about but after a little bit of digging, it would appear that the site is dedicated to sharing stories about Microsoft FTE’s. As the site proclaims, it is a bit like having an internal paparazzi but provides some good insight. Take a look here http://www.microspotting.com/
I recently setup a simple database mirroring config on my latpop (two instances SQL Server 2005 SP3 etc.). The databases were configured for HIGH SAFETY but with no witness. Everything was working fine. I wanted to simulate a communication failure so I could see the send queue growing. I just shutdown the mirror instance and then inserted lots of rows into the principal. Great, so now I executed:
EXEC sys.sp_dbmmonitorresults @database_name=N'dbm1', 6, 0
But it kept failing with invalid object on dbm_monitor_data. Looking at sp_dbmmonitorresults I could see that it was failing on line 130. This was really confusing me so I looked in msdb for the object. It didn’t exist.
I was suprised to find that the object was only created AFTER launching the database mirroring console. This creates the necessary monitoring objects in msdb. Problem solved, after much frustration.
Read the publication here http://sqlcat.com/technicalnotes/archive/2008/12/09/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx
Also note the new SQL Server 2008 transaction log specification:
1. Amount of “in-flight” I/O Limit.
a. SQL Server 2008: limit of 3840K at any given time
b. Prior to SQL Server 2008: limit of 480K at any given time
This is a big announcement made at WinHEC http://www.microsoft.com/presspass/features/2008/nov08/11-06winserverr2.mspx
“[…] we continue to invest in scalability. In Windows Server 2008 R2, we have built in support for up to 256 logical processors, which will allow our customers to more fully exploit today’s powerful CPUs, deploying only the features they choose and scaling those solutions to meet their organization’s needs.”
You can watch the recording here http://www.microsoft.com/presspass/events/winhec/default.mspx
[UPDATE] - Go to about 32 minutes; I particularly like the shot of task manager with 256 processors (about 38 minutes in)
I have been looking at some of the new performance counters that are shipped with SQL Server 2008. Unfortunately some of these are either not well documented or not documented at all. The list so far consists of:
- SQL Server Databases: Tracked transactions/sec - Number of committed transactions recorded in the commit table for the database.
- SQL Server Databases: Write Transactions/sec - Number of transactions which wrote to the database in the last second.
- SQL Server Buffer Node: Remote node page lookups/sec - Number of lookup requests from this node which were satisfied from other nodes.
- SQL Server Buffer Node: Local node page lookups/sec - Number of lookup requests from this node which were satisfied from this node.
- SQL Server General Statistics: Connection reset/sec - Total number of connection resets per second.
- SQL Server General Statistics: Tempdb recovery unit id unit - Number of duplicate tempdb recovery unit id generated
- SQL Server General Statistics: Tempdb rowset id - Number of duplicate tempdb rowset id generated
- SQL Server SQL Statistics: Misguided plan executions/sec - Number of plan executions per second in which a plan guide could not be honored during plan generation
- SQL Server SQL Statistics: Guided plan executions/sec - Number of plan executions per second in which the query plan has been generated by using a plan guide.
- Deprecated Features: Usage <feature> – this is a really nice counter which lists feature usage since last SQL Server startup
I know there are lots more e.g. resource governor etc. but these are the counters which caught my eye.
