Remote Development Trick

Here's one of those tricks that I'm sure I wouldn't need if I knew a bit more about what the heck I'm doing, but it helps me nonetheless....

A typical scenario for me, is that I get an assignment to work on a project for some new client, and I create the database on my laptop, and begin work.  Sooner or later they give me connection information for the "real" server where I will be deploying to.

That's when the fun begins :)  Out of habit, my local Enterprise Manager logs in to my local SQL Server (on the laptop) as SA.  I know, many of you will quit reading and yell at me just over this.  But, it's my laptop, not a production server.  It's easy....  So, I'm happily creating tables procs, etc.  Not that I do all of that in EM, but I get to Query Analyzer after going into EM via the pulldown menu, which nicely brings me to the same database I'm currently "in."  I'll bet lots of people do it that way.  This article is for them :)

So then, all of my objects are owned by dbo!  Then I need to deploy them to some remote server, where I can only login as a designated user, who perhaps is NOT dbo :)  This is where the problems begin.  Trying to re-create objects on the remote server via sql scripts is a major pain, because all of the tools for generating these scripts throw the owner in as well.  Once you are deployed and want to do a "diff" with any of the tools that do that, you are hosed as well for the same reason, your DIFF tool will find dbo.mytable as different from someuser.mytable.  This is the 9th circle of hell, I think.

So, the trick?  It's amazingly simple, and will probably make many of you stop reading my blog forever.  But hey, it works for me, so I thought it might work for someone else :)  Here it is:

1) Edit your hosts file on your development machine.  Set for a hostname like sql.thisapp or sql.thatapp.  Anything like that.  Just make each entry unique.

2) In your Enterprise Manager, you can then add a new sql registration to your same localhost as many times as you'd like, with different names, like the above sql.thisapp or sql.thatapp.  Login to those connections with the name/password that you will be using on the remote server, instead of SA, or windows.

That's it.  So you can have your localhost sql server referenced many times in the same instance of EM, each logging in with a different username, having access to different databases, etc.  Of course, you still have the default (local) registration, where you login as SA, or using Windows, whatever, so you can manage the installation as needed.

Print | posted on Tuesday, November 18, 2003 8:55 PM


# re: Remote Development Trick

left by AjarnMark at 11/18/2003 11:50 PM Gravatar
Some thoughts:

1) I just add my Windows login into my SQL Server as a SysAdmin privileged login. That means that I don't have to worry about passwords, etc. going into QA or EM, I still get full privileges, and I'm not using the dreaded SA login.

2) Why wouldn't you want all your objects owned by dbo when you distribute them? If they are owned by someuser, then only that user sees them by default and anyone else, if given permissions, has to reference the table as someuser.tablename. I prefer to have all my objects owned by dbo so I don't have to worry about multiple versions of the same tablename (and thus different data) are on the server.

3) You can always run sp_changeobjectowner.

# re: Remote Development Trick

left by Shannon J Hager at 11/19/2003 12:21 AM Gravatar
in reverse order... 3) no, you can not "always" run that sp.

2) when dealing with hosted solutions, you usually do not have the ability to run as dbo.

1) that doesn't help the remote hosted problem. It is great if your remote server is yours but I don't think that's what he's talking about.

# re: Remote Development Trick

left by mohdowais at 11/19/2003 2:31 AM Gravatar
doesn't this entire problem boil down to not using the SQL Server Query Analyser menu in EM :)? I have a shortcut to QA in my Quick launch bar, and always login with the username that needs to own the objects, and run the script!

# re: Remote Development Trick

left by Travis Laborde at 11/19/2003 6:30 AM Gravatar
Mark, Shannon responded before I could :) The problem that I've faced being, that I can only login to the remote server with a specific login, which may or may NOT be dbo :) So, I want to paste in my script to QA to create objects. So they get created with username as owner instead of dbo. Yet while on my development machine, when I create objects, they are dbo, because, well, I'm dbo! :) I'm sure there are other ways to deal with this problem, but this way works easiest for me, out of the ways that I currently have tried.

Mohdowais, probably! :) Maybe I'm just incredibly lazy. I always login via EM, which I keep open for purposes of quickly getting into DTS, or looking at jobs, assigning permissions, etc. Then I hit QA from there to do all the actual "development" work. I used to think it was just me, but I'm finding that other people have this habit as well.

I'm VERY excited about the new SQLWorkbench product in Yukon, which seems like it will be a killer EM/QA combination tool that might make these problems go away. The problem is not the technology, but the software. Just like in my opinion, VS.NET is the weakest part of .NET. I absolutely LOVE .NET and I have a love/hate relationship with VS.NET :)

# re: Remote Development Trick

left by Shannon J Hager at 11/19/2003 8:30 PM Gravatar
I guess I should mention my way around this... I take the create script generated by my local DB, open it in notepad, ctrl+H, replace all "[dbo]." with "", replace all "dbo." with "", save it, then run that on the remote server.
Comments have been closed on this topic.