Migrating from 2 Instances to 1 Instance (SQL 2005)

Hello, someone please help!!!!
I am trying to simplify life by combining 2 sql instances that are on the
same box into 1. Basically there is only 1 database per instance that is
being used for any real time production activity. My main problem is, that
there identical user logins (name and pw) setup on the two instances, and
when I try to combine them and call stored procs it can't find them in some
cases. In my research I found that this is because for the user, SQL really
only looks in the user's default database for the SPs. My problem with thi
s
is that I have different applications using the same login and it will be a
lot of trouble to change the user that the apps are using.
Also, it doesn't appear that the initial catalog param I am using in my
connection string is actually doing anything. Actually, I can completely
remove it and it makes no difference in how the application acts (mainly
because the connection just gets established to the default DB no matter wha
t
I pass in), any ideas on this? I mean, what is the point of having the
option if it doesn't do anything.
So, I guess my main question is, is there any way to make SQL look at more
than just the default DB to connect to, or is there a setting in SQL 2005
that allows me to connect to a different DB other than the default if I want
to by looking at the connection string.
Any help will be greatly appreciated...At the very least, have you looked at sp_change_users_login? That should
help you to consolidate servers.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff Gibson" <Jeff Gibson@.discussions.microsoft.com> wrote in message
news:7BBD384B-A311-493B-B7F9-D66985374B2B@.microsoft.com...
Hello, someone please help!!!!
I am trying to simplify life by combining 2 sql instances that are on the
same box into 1. Basically there is only 1 database per instance that is
being used for any real time production activity. My main problem is, that
there identical user logins (name and pw) setup on the two instances, and
when I try to combine them and call stored procs it can't find them in some
cases. In my research I found that this is because for the user, SQL really
only looks in the user's default database for the SPs. My problem with
this
is that I have different applications using the same login and it will be a
lot of trouble to change the user that the apps are using.
Also, it doesn't appear that the initial catalog param I am using in my
connection string is actually doing anything. Actually, I can completely
remove it and it makes no difference in how the application acts (mainly
because the connection just gets established to the default DB no matter
what
I pass in), any ideas on this? I mean, what is the point of having the
option if it doesn't do anything.
So, I guess my main question is, is there any way to make SQL look at more
than just the default DB to connect to, or is there a setting in SQL 2005
that allows me to connect to a different DB other than the default if I want
to by looking at the connection string.
Any help will be greatly appreciated...|||Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a messag
e
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is accessin
g
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I a
m
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to. Wha
t
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||Hmm. I can't understand why it would ignore the database in the connect
string - unless somewhere in the app it's hard-coded to issue a USE
<database> command. Another explanation is that the connect string is
simply incorrect. It may not throw an error; rather it simply doesn't
switch to the specified DB and goes with the default. Can you post the
connect string (sans password)?
What method was used to migrate the logins, BTW? Have you looked at:
http://support.microsoft.com/defaul...kb;en-us;246133
http://forums.microsoft.com/MSDN/Sh...309521&SiteID=1
The scripts preserve the SID's. I'm not sure if that's what happened here.
That said, you may be able to assign default DB's to the new DB, based on
the old system, using SQL-DMO. Just step through the Logins collection of
the source server and look at the Database property. For the same Login on
the destination, set the Database property to be that of the source server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Gibson" <JeffGibson@.discussions.microsoft.com> wrote in message
news:A1DCE3CB-AF90-47DB-A28D-AD209B3C157C@.microsoft.com...
Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a
message
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is
accessing
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to
change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I
am
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to.
What
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||I'll reiterate - check out the sp_change_users_login. Your "identical"
user ids really aren't. They are from two different instances,
therefore have two different SIDS. unless your DBA removed all the
users and completely recreated them, they won't have access. Just
recently ran in to this, and that stored procedure saved the day.|||Hi
Tell your DBA to read this article
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148583484.249905.46360@.u72g2000cwu.googlegroups.com...
> I'll reiterate - check out the sp_change_users_login. Your "identical"
> user ids really aren't. They are from two different instances,
> therefore have two different SIDS. unless your DBA removed all the
> users and completely recreated them, they won't have access. Just
> recently ran in to this, and that stored procedure saved the day.
>

Migrating DTS packages on instances

I have problem and don't know how to resolve it.

I'm running multiple instances(developpement,acceptation, production) on one server with the same db's on each one of them.

Now I'm trying to find out a way to transfer DTS packages from one instance to another (I know it is straight-forward if you don't have to change anything) but in my case I would want to change the connection properties within the dts package e.g.

on my server\developpement instance I've created a DTS package with connection properties server name server\developpement

Now I want to transfer it to my server\acceptation instance and there I want the connection properties server name to be server\acceptation .

I've tried doing it with a dynamic properties task but I can't seem to find the instance name where the package is saved. I can only find the server name(environment variable).

This of course is no problem if you have but a few packages, but if you have over 50 to transfer

Any help would be more than welcome.

Rosko

PS: I know it is possible between two different servers using (local) as the servername but that's not an option for my boss.I have a very similar situation. I currently have global variables. When I migrate, I am changing this from Package Properties.

If I do not find a solution, I plan to have a flat file and read the information from that file using ActiveScript.

Please let me know if you have a better solution.|||See

"LoadFromSQLServer' & 'SaveToSQLServer'
in SQLServer Books online or on the web.
this can be called in VB and will repopulate on the other server.

For more advanced changes the attached structe can be populated and manipulated in VB to create new DTS Packages.|||Correct me if I am wrong. I think this still does not address the issue.

The issue is to set the datasource property to the connections within the package(s) while the package is migrated from one instance to another.

LoadFromSQLServer and SaveToSQLServer seem to help in migrating from one instance to another.|||Unfortunately jayaramanc,

The connections held in the connections object must also be updated with the new server name, as does any ADODB connections in ActiveXScript in the Steps object.

migrating cluster instances to stand alone install

Hello all: looking for some general advice on proper approach. looking to move databases off of a clustered instance to a new (non-clustered) server.
Got several issues i'd like some advice on.

1. This is an OLTP instance that's been around for a while & it's pretty well encrusted with apps & processes that attach to it. Therefore it would be a very good thing if we didn't need to change the connection information in several hundred places after the move.
I've tested one approach to this that seems to work: moved the instance to a server which has the same name as the cluster resource associated with the clustered instance and an instance that has the same name as the clustered instance.
For example: cluster install is aiproddb\production, moved it to a box called aiproddb with a named instance called "production". There's a bunch of tedious network stuff that has to be done to make this work (binding an IP address to the MAC address of the new box and some murky DHCP reservation fiddling), but after the network crew got done cursing me it did finally work.
Does this seem like a reasonable approach?

2. What's the best way to transfer security info to the new instance? I used the transfer Logins DTS widget but had some problems with it not being able to find some groups in AD.

3. what's the best way to transfer DTS packages?

4. is it necessary for the new instance to be at the same patch level as the old instance? the old clustered instance is still at SP3 and i threw the latest SP4 on the new location. good/bad/indifferent?

5. i was planning on taking a full backup & restoring it to the new machine. Is there a better way? Is the wizard for copying databases a good thing?

Those are these issues i'm aware of and have given some thought to. There are probably things about this i haven't considered and would appreciate some word on.

thanks,
Garth:D 5. [...] Is the wizard for copying databases a good thing?

thanks,
Garth

NOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!!!

:D

hmscott|||ok. that's one vote against the copy database wizard...|||Hello all: looking for some general advice on proper approach. looking to move databases off of a clustered instance to a new (non-clustered) server.
Got several issues i'd like some advice on.

1. This is an OLTP instance that's been around for a while & it's pretty well encrusted with apps & processes that attach to it. Therefore it would be a very good thing if we didn't need to change the connection information in several hundred places after the move.
I've tested one approach to this that seems to work: moved the instance to a server which has the same name as the cluster resource associated with the clustered instance and an instance that has the same name as the clustered instance.

I know this does not help you now, but in the future, try adding a layer of virtualization in between by using a DNS zone specific to your apps. Then you can move physical servers in and out of production smoothly without impacting application connection strings. Use one DNS A record per database (e.g. MyDB.dev.apps, MySecondDB.dev.apps and MyDB.test.apps and MySecondDB.test.apps). The apps point to the DNS name, the DNS name translates to a physical IP. When it comes time to migrate a database (or an entire server) you have one place to go to update IP addresses (the DNS server).

2. What's the best way to transfer security info to the new instance? I used the transfer Logins DTS widget but had some problems with it not being able to find some groups in AD.

Try exporting the logins to a file, cull selected logins that you don't want/need (sa comes to mind) and then inserting them with proper syntax. Somewhere there is an MS article about using BCP to do this. If I recall correctly, use "bcp log shipping sgl server logins" for your google. I also did it by copying and pasting into an Excel spreadsheet and then using formulae to build the SQL syntax. Crude, but it worked.

This will only build the logins and it will not link the users within the database to the logins (SID mismatch). For that you can use sp_change_users_login.

3. what's the best way to transfer DTS packages?

Depends on how much of your connection info is embedded. A while back I posted a script for backing up DTS packages to a structured file. If there aren't too many, this is a workable approach.

By the way, is this an upgrade to SQL 2005, or a lateral to another SQL 2000 instance? If the former, you have more work cut out for you. If the latter, you should really consider the former (ie, you should be working on an upgrade).

4. is it necessary for the new instance to be at the same patch level as the old instance? the old clustered instance is still at SP3 and i threw the latest SP4 on the new location. good/bad/indifferent?

Should be all right. Watch the AWE memory thing with SP4, but I don't remember specific issues with SP4. Be careful if you are using replication.

5. i was planning on taking a full backup & restoring it to the new machine. Is there a better way?

sp_detach and sp_attach?

The advantage with your method (if you have a large database) is that you can do a partial restore (which might take a long time) and then apply just the last log file to bring the new instance up to date to minimize your outage.

Is the wizard for copying databases a good thing?

Noooooooooooooooo!!!!!

But enough on that subject :D .

Those are these issues i'm aware of and have given some thought to. There are probably things about this i haven't considered and would appreciate some word on.

thanks,
Garth

Test, test, test, practice, practice, practice.

Good luck.

Regards,

hmscott