Tag Archives: SQL

Great Moments in Database Design

I am by no means an expert DBA, but the design mentality behind a product that my company uses leaves me scratching my head.  This product which shall go unnamed (Exact JobBOSS) has a variety of faults, but none more perplexing than their ‘double keying’ of tables.

Now I’m not talking about a compound key here.  No, this is where they’ve gone through and put the key in twice, such as this example from the ‘Address’ table:

JobBoss Address Key
No problem they’re the same (green), until they’re not! (red)

The logic behind this design decision is somewhat puzzling. I guess they figure that a user may want, for example, a new customer to have an old customer’s Address number? Not only does that not make any sense on it’s face, but A) The user has no control over how this number is assigned and (the kicker) B) This second key is allowed to be null within the database. Only the application rules keep a catastrophic event from occurring in making sure that this second ‘key’ is not null.

Now you might be wondering: what’s the big deal if this second key is null, after all, there’s a primary key on the table already. Here we get to square the circle of this puzzling design decision because as it turns out other tables do not ‘key up’ to the primary key of the table, but the second, fake, ‘key’. Here’s the create code from a table that ‘keys up’ to the Address table (emphasis mine):

ALTER TABLE [dbo].[RptPref] WITH NOCHECK ADD CONSTRAINT [FK_Addr_on_RptP] FOREIGN KEY([Address]) REFERENCES [dbo].[Address] ([Address])

Not [Address].[AddressKey], but [Address].[Address]?!?*  Obviously there’s two issues that immediately spring to mind in such a situation, both of which the developer is attempting to control strictly through their Franken-code.  The first is, like I stated above, the field can be null.  What if this record is not inserted properly and it is null?  Fun times I’m sure!

The second issue is that since the field is not a key, there can be more than one record with that same value.  Is their code tight enough to prevent this possible corruption issue?  Like much to do with this product, you’ll have to take it on trust!

Addendum: I should also point out a basic programmatic issue with this whole design methodology.  If I want to modify records in a table which contains a column that is referenced as a key in a different table, but isn’t a key in the source table that I want to modify, there’s no (easy) way to catch this.  In the back of my mind, I’m thinking that maybe the database was designed as a complicated mess in order to make it semi-proprietary.

*(I should point out that the database for this product is small enough that it’s feasible to make a (tiny) entity chart of it within Visual Studio; but beware that easily half the tables will appear to have no relationship with any other table because of situations like the above (‘keying up’ to a possible null non-key, that’s actually never supposed to be null)).

**(While I ranting about JobBOSS I figured I’d bring to light the package’s great love of the ‘rtl70.bpl access violation’ errors that it is prone to getting.  I long thought that it was an issue with my installation, but I’m now convinced that they’ve coded the Delphi portions with little to no error handling (is there no ‘try/catch’ in Delphi?)).

Resetting The Raiser’s Edge Password

I notice that Blackbaud now has a utility out that handles Supervisor password resets without going through the faxing rigamarole, but when I had to do this I don’t know if that would have been a big help anyway as my customer A) Needed supervisor access to Raiser’s Edge right away and B) They had fired the one employee who had both supervisor access and was the Blackbaud support user.

The two things I did have though was administrator access to the Raiser’s Edge MS SQL database and the SQL Server Management Studio.  My quick and dirty fix was to open the database (re7), open ‘tables’ and then find the ‘USERS’ table (dbo.USERS).  I then right-clicked the table and then chose ‘open table’.  There is one other thing that’s needed for this procedure at this point: a password that is known.  In this case I had the one’s below:

I’ll point out that I put no energy into checking out the hashing code used (i.e., can the PASSWORD field be blank?  Are the passwords hashed the same on every RE install?, etc.).  In this case all I did was copied the PASSWORD hash field from an account whose password was known to the Supervisor PASSWORD field.  (It goes without saying that I also backed the database up first (right click the database->tasks->Back Up…))

Forefront TMG IP Issue

I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:

  1. I have certain blocks of IP addresses blocked on the Cisco firewall and occasionally a user will repeatedly hit a website that sits in one of those blocks.  I will then have to find out who is doing this as the firewall will always just report the address of the proxy server for the traffic.
  2. Occasionally a user will download malware and I’ll need to find out where they got it from so that the source of the traffic can go into the block list in point #1.

Either way I end up running a report on a system at the site which has SQL Server Management Studio installed on it with a query something like the below:

SELECT [ClientIP]
      ,[ClientUserName]
      ,[ClientAgent]
      ,[ClientAuthenticate]
      ,[logTime]
  FROM [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog]
 WHERE [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog].[DestHostIP] = ‘d3bd0e17-FFFF-0000-0000-000000000000’

You’ll note that the address on the bottom line is rather unintelligible since it uses a semi-IPv6 format used by TMG.  To work around this I had been doing hand translation using the calculator in Windows to figure out what the address I am looking for should be.  However, I grew tired of that and wrote a program that will handle the translation/conversion to and from a regular IPv4 address and Forefront TMG 2010 address (because of some apparent vague interest, I’ve packed a zip with just the executable (no code) here).

I want to note though, that since it was casually put together I didn’t code it to handle any exceptions.  The source code is included though, so if you want to code around some fat fingering then feel free.

Migrating from 32bit SharePoint Services

This is my definitive guide on migrating from 32 bit Windows SharePoint Services 3 to SharePoint Foundation 2010 (64 bit only, oof!).  It’s not quite as definitive as I would like as I barely had time to work this through once, let alone multiple times:

  1. I changed the user account that runs WSS3 from ‘NETWORK SERVICE’ to a domain account (though I’m unsure if this step is required).  I originally did this when trying to restore the 32bit WSS3 onto the 64bit box and then using 64bit WSS3 to try and upgrade it.  Don’t do that by the way.  Anyway it made working with the database easier on the whole anyway.
  2. Backed up the SharePoint content database, called STS_servername_1 in my case. (This server used the ‘Windows Internal Database’, an extra crippled version of the already crippled SQL express.  I had installed the SQL 2005 tools on there for a different project so I used the ‘SQL Server Configuration Manager’ to change the service argument to add “;-T7806” and I then used the SQL management studio to do the backup).
  3. Installed 64bit Sql Server 2008 express on the new 64 bit server (I named the main instance the same as the instance on the 32bit box, not sure if that matters)
  4. Installed SharePoint Foundation on the new 64 bit server (it automatically set itself up in the SQL instance I installed).
  5. Restored the content database to the new server (again, using the management studio; I know that it can be done through the command prompt but I like the visual feedback).
  6. Within the Central Administration site on the new server I went to ‘Manage Content Databases’, clicked on the preinstalled content database and removed it in the subsequent interface.  This step removes the content linked to ‘/’ so that the restored database can go there.
  7. Added the restored database to the farm using stsadm. (in our case: stsadm -o addcontentdb -databasename STS_servername_1 -databaseserver newserver-url http://newserver/)
  8. Upgraded the content database using the ‘upgrade-spcontentdatabase’ command within the Sharepoint PowerShell.
  9. The content should now show up when you pull up http://newserver/ , but to complete the upgrade (and keep the content database from showing as ‘mostly upgraded’ or something along those lines) I had to upgrade the view under ‘Site Settings’ within the Sharepoint site and then going to (I believe) ‘Visual Upgrade’.

Obviously our setup is fairly simple with one server driving the whole ‘stack’.  Our content is also fairly static (it didn’t matter that the database backup that I was working with was a day old).  Your results, of course, may vary (but will probably do a sight better than trying to put something together from the amalgamation of websites that only convey one piece of the puzzle!).

SQL Express Upgrade Error

I went to upgrade my old MSDE database to SQL 2005 Express on our McAfee EPO server and I was receiving a ‘-1’ error during the upgrade process with the detail of:

You selected Mixed Mode authentication, but did not provide a strong password. To continue you must provide a strong sa password.

I went through a bunch of different items, but this link detailed that the error is in fact caused by a bum installer of Microsoft’s design:

Copy C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin\BPAClient.dll to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA

This was one of those ‘no way’ errors that aggravated me to no end.  Why didn’t they fix this?