All posts by Sam

The many caveats of installing a cluster

There are quite a few things to remember when you are installing a clustered instance of SQL server.

For 2005, you need to remember a few things

  • Global groups for your accounts.
  • Service accounts to go in those groups.
  • Rights to add those accounts to those groups during the install.
  • Set up MSDTC as a clustered resource.
  • Get your IP address from the network admin and ensure your SQL virtual name is not used.
  • Get your drives set up properly and make sure to test failover before you begin.
  • Make sure you are logged out of node 2 and have the task scheduler service running.

So I thought I had everything gathered up and ready to go tonight.  Nope.

Near the end of the install I see this:

remote setup failed

After a moments google I find this:  http://support.microsoft.com/kb/910851 , which gets me to the task scheduler log on the remote machine.

Unable to load task.
The specific error is:
0x80070002: The system cannot find the file specified.

I found a suggestion from a MS MVP saying that you need to make sure your install account has access to the install files. I figured that had to be it – so I moved them to the c:\temp directory and voila, a good install.

Evidently, whatever account the install was running under couldn’t access my desktop – but I did enter MY domain account to start the account, so maybe it switched to the SQL service account to do the install? Doesn’t make sense to me.

The lesson I learned to day is to never install from a network share or a user directory – put the install files on a drive which has an admin share and this shouldn’t happen.

Currently Reading

The Practice of System and Network Administration

Still on chapter one, but I know I’m going to like it.

1.40 My Dishwasher Leaves Spots on My Glasses
• Spots are usually the result of not using hot enough water rather than
finding a special soap or even using a special cycle on the machine.
• Check for problems with the hot water going to your dishwasher.
• Have the temperature of your hot water adjusted.
• Before starting the dishwasher, run the water in the adjacent sink until
it’s hot.

Not SA related, but gives me some valuable info for a problem I am having.

SSIS and Oracle

Working with Oracle and SSIS/DTS on a 64-bit windows server has been a painful experience.

Here are the steps to connect to Oracle if you’re setting up a new SSIS package.

  1. Create a connection using the .net Providers\OracleClient Data Provider
  2. Use the DataReader source type.  Select the Oracle connection you created in step one.
  3. Use whatever destination you need to – it doesn’t matter at this point.  I typically use the OLE DB for SQL Server destinations.
  4. Schedule as a job with the integration services type step.  No 32 bit DTEXEC modification needed as was the case with DTS steps that hit Oracle. 

You may hit some quirky errors with overflows or numeric data types.  It’s good to make sure you have the latest oracle patchset applied before proceeding with workarounds.

Backups

I’ve always been a stickler for backups, even before I was a sysadmin.  I create music on my home computer, creating massive volumes of data just waiting to disappear from my life.

I have been using SuperDuper and TimeMachine on OS X.  I don’t backup my windows machine, since it is a media center we mostly just stream stuff off the web.

When I leave town I throw the backup drives in an unlocked fireproof save with a note begging thieves not to steal it.  This data is truly unique and irreplaceable.  

But what if there was a fire when I didn’t have my drives in the safe?  I’d be very sad, that’s what.

There are a few online backup services which are starting to look very attractive.  Most offer 2GB for free.  I’ll be needing at least 50GB for audio mixes.  Actual song files are another story….maybe 300GB or so.  Then there are various video, picture and music files I’d hate to lose.  So it’s looking like I’ll be needing around 500GB to lock it all up.

Enter Mozy – $4.95 a month for unlimited storage space.  I assume this is for one computer.  What a deal.  That puts a year at $60.  40 years at $2400.

Sold.

Helping out the auditors

If you’d like to give an auditor their very own copy of your data on their machine and your DB is less than 4 GB, then read on.  As a responsible IT person, you won’t be putting sensitive information on this auditor machine, unless it is properly secured, electronically and physically, right?

1. Set up the auditor machine

Install SQL Express on the auditor machine. Enable remote connections via TCP/IP with the config tools. Grant permissions for your agent account on the source server to be an admin on the auditor’s machine. You’ll also need to grant access for the agent account to write to a backup directory on the auditor machine.

2. Create a script that will restore the database and do some other steps. This script is referenced from sqlcmd in step 3. My script does these things:

MASTER.dbo.usp_killusers ‘MyDB’ –something similar to this script ->> http://www.sqlservercentral.com/articles/Administration/71/

RESTORE DATABASE MyDB FROM DISK = ‘\\workstation\backup\MyDB_Auditor.bak’
WITH REPLACE,
MOVE ‘MyDBtrainingsys’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf’,
MOVE ‘MyDBtrainingdata’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_1.ndf’,
MOVE ‘MyDBtraininglog’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_2.ldf’

–Drop our auditing ddl trigger from the database
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N’DBA_Audit’ AND parent_class=0)
DROP TRIGGER [DBA_Audit] ON DATABASE

EXEC sp_changedbowner ‘sa’

–Add rights for whomever needs access
CREATE USER [Domain\AuditorLogin] FOR LOGIN [Domain\AuditorLogin]
EXEC sp_addrolemember N’db_datareader’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_datawriter’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_ddladmin’, N’Domain\AuditorLogin’

3. Create the job with these steps on your source server.

(T-SQL type) – Create backup on workstation
BACKUP DATABASE MyDB TO DISK = ‘\\workstation\Backup\MyDB_Auditor.bak’

(OS Command) – Restore
sqlcmd -i \\Share\auditorRestore\MyDBAuditorRestore.sql -Sworkstation\sqlexpress -o \\share\sf.log

(OS Command) – Cleanup
del \\workstation\backup\MyDB_Auditor.bak

Partied with Microsoft today

I attended the Microsoft technology roadshow today. After being paged out at 4:30 due to some I/O nonsense related to statistics updates which I stayed up until 6:30 for trying to prevent happening again, I made my way to downtown Phoenix.

Arrived just in time to see the new nastyness that is windows 7/server 2008 R2 licensing.

Sharepoint 2010

Looks nice. Ajaxified. Working in Firefox with a silverlight plugin installed I would assume. Although they do also claim it works in smartphones so maybe it is just ajax. Sharepoint = pricey.

BI/Insight

This is the real magic. Excel is nothing to laugh at. Excel 2007 has some nice features when it comes to connecting to alternate data sources such as analysis services. I have some plans to work with this tech in the future. 2010 takes it further and adds a dash of manageability which actually making things look good. When I say good, I mean Apple good…almost. They even have little carousels for browsing reports.

They have improved manageability of user created content by leaps and bounds. Users can publish Excel docs. Other users can create reports based on those docs. If usage of the base Excel doc starts to accelerate, you will know it and can act accordingly by taking over the management of the ‘application’. That’s what I’m talking about Napoleon.

A hotshot consultant showed us how he did a break out/data driven report in reporting services. I was hoping for something a little more impressive from this ‘prodigy’, as his boss referred to him.

I see ‘self-service’ BI as a bit of a pipe dream. MSFT is putting the framework in place so that if you users manage to figure out this mess of tech, you’ll at least know what is bombing on your servers.

Also, Gemini has been officially dubbed PowerPivot. Reminds me a bit of Cognos Power Play.

Seeing all the connections for all SSIS packages on your server

So you have a mess of packages on your server and you’d like to know what they connect to without going into each package? Yeah, me too.

Create a visual studio c# console application with Visual Studio (express will probably work).

Paste this code in:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Text.RegularExpressions;
using System.IO;

namespace SSISPackageInspector
{


public static class CsvWriter
{
private static StreamWriter _writer = new StreamWriter(@"c:\temp\ssis_list.csv");

public static StreamWriter Writer
{
get { return _writer; }
}
}




class Program
{

static void Main(string[] args)
{
string outfile = null;
string folder = null;
string server = null;


if (args.Length == 0)
{
System.Console.WriteLine("Please enter a numeric argument.");
Console.Read();
return;
}
else
{
int i = 0;
foreach(string s in args)
{
if (args[i].Substring(0,2)==@"\o")
{
outfile = args[i].Substring(2);
}

if (args[i].Substring(0, 2) == @"\f")
{
folder = args[i].Substring(2);
}

if (args[i].Substring(0, 2) == @"\s")
{
server = args[i].Substring(2);
}

i++;
}

}


if (outfile != null)
{



}
//Look up All package names for a server
PrintDirectoryContents(folder,server);

if (outfile != null)
{
// CsvWriter.Close();
}
Console.Read();
}


static void PrintDirectoryContents(string path, string server)
{


string sqlFolder;
string sqlServer;

Application ssisApplication;
PackageInfos sqlPackages;

sqlServer = server;

ssisApplication = new Application();


sqlFolder = path;
sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer);
if (sqlPackages.Count > 0)
{
Console.WriteLine("Packages stored in " + path);
foreach (PackageInfo sqlPackage in sqlPackages)
{
Console.WriteLine(sqlPackage.Name);
Console.WriteLine(" " + sqlPackage.Flags);

if (sqlPackage.Flags.ToString() == "Package")
{
GetConnections(@"\" + sqlFolder,sqlPackage.Name,server);

}

if (sqlPackage.Flags.ToString() == "Folder")
{
PrintDirectoryContents(path + @"\" + sqlPackage.Name,server);
}

}
}
}


static void GetConnections(string path,string pkgname,string server)
{

// Create a package, and retrieve its connections.
Application mApplication = new Application();

//Package pkg = mApplication.LoadPackage(@"C:\temp\DBA_Repository_Load.dtsx", null);
Package pkg = mApplication.LoadFromDtsServer(path + @"\" + pkgname , server, null);
Connections conns = pkg.Connections;

//Create the Enumerator for each connection
ConnectionEnumerator myEnumerator = conns.GetEnumerator();
Console.WriteLine("------" + pkg.Name.ToString() + "----------");

while ((myEnumerator.MoveNext()) && (myEnumerator.Current != null))
{
string myLine = null;
myLine += server + "," + path + "," + pkg.Name.ToString() + "," + myEnumerator.Current.Name;

Regex r = new Regex("([^=;]*)=([^;]*)");

MatchCollection mc = r.Matches(myEnumerator.Current.ConnectionString);

Console.WriteLine("Conn Name:" + myEnumerator.Current.Name.ToString());

string s = myEnumerator.Current.ConnectionString.ToString();
if (s.Length > 0)
{
if (s.Substring(0, 2) == @"\\")
{
Console.WriteLine(" " + myEnumerator.Current.ConnectionString);
myLine += "," + myEnumerator.Current.ConnectionString;

}
}

foreach (Match m in mc)
{
if (m.Groups[1].ToString() == "Data Source" || m.Groups[1].ToString() == "Initial Catalog")
{
if (m.Groups[1].ToString() == "Data Source")
{
Console.WriteLine(" Server:" + m.Groups[2]);
myLine += "," + m.Groups[2];
}
}

if (m.Groups[1].ToString() == "Initial Catalog")
{
Console.WriteLine(" Database:" + m.Groups[2]);
myLine += "," + m.Groups[2];
}
}

CsvWriter.Writer.WriteLine(myLine);
//Console.WriteLine(myLine);


}

Console.WriteLine("------End Package-------");


}
}

}

Go to project > properties > debug > start options

Enter these parameters

\sMyServer \fmsdb\mydirectory \oc:\temp\ssis_list.csv

The first is your SSIS server you’d like to connect to.

The second is your ssis directory to process – you can just use msdb and do the whole shebang.

The final is your output file. Duh.

You can now press play in VS and see the console window spin by. Look for your file in the directory you specified. You can do all kinds of spiffy stuff to packages using .net.

More information on running and managing packages programmatically is available from the good folks at MSFT.