Entropy Reducers Amalgamated

(Boy it's been a while.)

So a few weeks back I had the pleasure of migrating a cilent's website from a shared web host + shared database host to a single VPS (virtual private server) where both the webserver and database server were on the same machine.

This consolidation is actually counter to the standard multi-tier architecture:

          +----------------------+
          |      webserver       |
          |                      |
          +----------------------+

          +----------------------+
          |       database       |
          |        server        |
          +----------------------+

In this case, we performed the consolidation because the shared database server that was available was horribly overburdened, and also the current application wasn't so much "architected" as much as "pieced together à la Frankenstein's monster" and wasn't uniformly careful with cleaning up its database connections, or even using database pooling (for .NET), so at times we'd see up to 4000 (!) separate connections attempting to be made to the database server.

While we were having awful problems with the database server, the technical staff at the hosting provider in question were helpful and responsive, even if they couldn't help us narrow down all the problems--some of which were caused, no doubt, by the client's own applications.

To tie in to the title, one of the big hangups came when we attempted to restore a MS SQL server backup of the original databases onto the local installation. One of the problems is that SQL server has a parallel notion of user IDs, namespaces (for tables and tablespaces and whatnot) and schemata (schemas), with some apparent overlap.

DISCLAIMER: I am not a SQL server database administrator (DBA) by any definition except by dint of having to manage a database on a single small server. Oracle, MySQL, PostgreSQL, these I can deal with, but SQL server? Not really.

So after restoring the databases, I found some standard permissions and ownership errors. And trying to just create a database user using the standard GUI method didn't work. (It never does. Why would anyone be surprised with this?). I found, however, that by dissecting the SQL that the create-a-user script uses, and modifying it ever so slightly, I could get things to work just my way. Perhaps this isn't the Microsoft way of doing things, and anyone who wants to disabuse me of my bad methodology should kindly step up and do so, as I am willing to learn.

The magic (line numbers inserted by me, actual databases and usernames modified, of course)

1  USE [master]
2  GO
3  CREATE LOGIN [loginname] WITH PASSWORD=N'password', DEFAULT_DATABASE=[defaultdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
4  GO
5  USE [defaultdb]
6  GO
7  ALTER USER [loginname] WITH  LOGIN=[loginname]
8  GO
9  USE [defaultdb]
10 GO
11 ALTER USER [loginname] WITH DEFAULT_SCHEMA=[schemaname]
12 GO

The key was that the loginname and the schemaname, even though they are the same text string, are totally independent, and needed to be assigned. But in line 3, the standard CREATE LOGIN creates a disabled user, and that broke the ALTER USER statements in lines 7 and 11. (Or at least, that's what my memory has.)

Posted by Jerry B. Altzman at 10:47 PM | Permalink | Comments (1) | TrackBacks (0)