Moving SQL Datastore... to new SQL Server 2

[bran2235]

Hello everyone...
I need to move my XPe SQL Datastore to a new SQL server. The new SQL Server will be identical to the current database/server.

Can anyone explain how I can do this?


MANY THANKS!!
Brandon

 

[tobrooksie]

Brandon,

I'm about to embark on this process very soon. Have you found an answer to the question? Any information you can provide would be appreciated.

Thanks,
Keith

 

[bran2235]

Keith,

No, but did find out that the process is easier as long as the two SQL servers are identical. We are running SQL 2000, so I will definently stay with that... I have some other info, but I have to leave now- I'll post back more later... when are you migrating/moving?



Thanks,
Brandon

 

[bran2235]

Keith,
Did you move your datastore??
We pushed this project but I have to start reading up again on how to do it... How did it go?


Thanks,
Brandon

 

[LloydGill]

Guys,

have you done your move yet? I have done exactly what you need to do in a test environment recently, documenting the steps. Would that help? There are lots of documents out there that don't make alot of sense until you go for it!

Lloyd

 

[ascotta]

I think posting you steps would be extremly usefull for people here, or a link to the docs you found that were good. Let me guess the Advanced Admin Guide ?

[blue] Oh you know, just doing what I do.[/blue]

Cheers
Scott

 

[bran2235]

Lloyd-

This is what I was going to do:
(Moving SQL 2000 Datastore to another SQL 2000 Server)-

I will have my SQL person, use the "Copy Wizard" to move the database to the new SQL Server-

FIRST
Backup existing Datastore and create/copy database on new SQL server. New database should be named the same as old Datastore database with same username account with same password and assigned db_owner permission.

SECOND
Stop IMA service on all XP servers.

THIRD
Open 'M:\program files\citrix\indepenent management architecture\MF20sql.dsn' with Notepad and rename the database server name entry to name of new server.
(This is done on all XP servers.)

FOURTH
Start IMA service on each server.

Is there anything else???
Someone mentioned that all I need to do is STOP the IMA svc and then do a dsmaint config /usr:<user> /pwd:<password> /dsn:"C:\progrma files\citrix\independent management architecture\mf20.dsn" on each server. I think this is the shortcut to my original way... your thoughts???


Thanks!
Brandon

 

[LloydGill]

Scott - I even found the Advanced Admin guide a little difficult to fathom (I'm no SQL expert) although now I have done it, I understand it. Always the way.

Brandon, I think I can see how your way works. This is how I did it - you should do backups etc and I certainly recommend doing it in a test environment first (even though it means creating two SQL servers).

The users etc are just examples I used.

Feel free to add comments, especially if it not easy to understand or if it is incorrect in some way.

1. Create new database on new SQL server

On new server, in Enterprise Manager:
Right click databases folder and choose New Database
Name it citrixds

Expand security folder and right click New Login
General Tab – enter name (citrixds)
Click SQL authentication and choose password
Defaults – choose database citrixds

Database Access tab – put tick against citrixds
In database roles, select dbowner. Click OK and confirm password

2. Copy mf20.dsn

Go to c:\program files\citrix\independent management architecture and copy mf20.dsn. Call the copy mf21.dsn

3. Configure mf21.dsn

Open ODBC in control panel \ admin tools and find mf21.dsn in file DSN and locator. Change mf21.dsn details to new server and citrixds user and password. Notice default database changes also

4. DSMAINT MIGRATE

In DOS, :>dsmaint migrate /srcdsn:”c:\program files\citrix\independent management architecture\mf20.dsn” /srcuser:sa /srcpwdassword /dstsdn:”c:\proram files\citrix\independent management architecture\mf21.dsn” /dstuser:citrixds /dstpwdassword2

This gives a DOS style progress bar. Wait for it to complete.

5. DSMAINT CONFIG

In DOS :>dsmaint config /user:citrixds /pwdassword2 /dsn:”c:\program files\citrix\independent management architecture\mf21.dsn”

6. Stop and start IMA service

In DOS: :>net stop imaservice
:>net start imaservice

7. Check it all worked

Go to regedit.
HKEY LOCAL MACHINE\Software\Citrix\IMA\Database driver= sql something
HKEY LOCAL MACHINE\Software\Citrix\IMA\datasource name=mf21.dsn

8. Update other farm servers

Copy mf21.dsn onto other servers in the farm, in the same directory. Follow stage 5 onwards on EACH server.


+++I am glad I can give something back to this highly useful forum+++

 

[tobrooksie]

Hello everyone,

I have moved the SQl server, and actually it was very simple. I had the freedom to move data from the old SQL server and give the new server the same IP and name. This made the migration very easy. If you are in the same boat, all you will have to do is stop the SQL server services on the old server, rename and give it a new IP, give the new server the IP and name, install SQL and service pack it, stop the sql services and then copy the data directory from the old server to the new one. Once this has been accomplished, start up the SQl services on the new server and check to see if the Databases have moved. At this point, your databases are on the same server (technically) and carry the same security as they did on the old server, and your Citrix environment should link back up and carry on running just as it did before. It worked for me and took very little time and effort, it's worth trying. By the way, we had about a dozen other apps running off that SQL server, and all of them relinked following this procedure flawlessly.