[THIN] Re: Migrating Data Store question
- From: "Jeff Durbin" <techlists@xxxxxxxxxxxxx>
- To: <thin@xxxxxxxxxxxxx>
- Date: Fri, 5 Dec 2003 09:37:14 +1300
I did this for a customer once - this is from my field notes that day. There
are probably some easier ways to deal with some of the SQL stuff, but I
don't know SQL well and had to fumble through it. It did work, though. Sorry
about the formatting - it's from a Word document and I used indenting, which
didn't translate properly to a text format. If anyone is really interested
and struggles with reading this, email me directly and I'll send a Word
version of it.
Jeff Durbin
Process for moving SQL datastore from one server to another:
o On source server:
§ Open Enterprise Services Manager.
§ Expand Microsoft SQL Servers, SQL Server Group, your server
name, Databases.
§ Make note of the file path to the Data Store database:
? Highlight the database, right-click, choose Properties, and
click the Data Files tab.
§ Highlight the Data Store database, and right click.
§ Choose All Tasks, Detach database.
§ Answer yes that you want to close connections. Answer no to
notifying clients.
§ Copy the database to the new server (usually to C:\Program
Files\Microsoft SQL Server\MSSQL\Data). Be sure to get both files ? the MDF
file and the LDF file.
o On target server:
§ Attach the database:
Open Enterprise Services Manager.
Highlight Databases, right-click, and choose Attach
Database.
Browse to the database?s MDF file and select it.
Select the SA account as the owner.
Click Ok in the Attach Database dialog.
§ Create a new owner for the database. (These are the
credentials that are specified when joining a server to the farm). This can
be the same name as the previous owner on the other server, but, you?ll see
in a moment, this can make it confusing. Let?s say the previous owner was
called ?citrix? and the new owner will also be called ?citrix?.
Expand the Security folder and highlight Logins.
Notice that there is no ?citrix? user. The database
was transferred, but the users associated with that database were not.
Right-click in the right pane and click ?New Login?.
Create a user called citrix. Use SQL Server authentication.
§ Create a temporary owner for the database (if you?re using
the same username as the old server).
In the security folder under logins, create a new
user called temp.
Highlight Users below your database.
Right-click in the right pane and click New Database
User.
Select the user in the Login Name dropdown listbox.
Check the db_owner checkbox.
§ Change the database objects? owner:
Right now it looks like things are fine, but they?re
not. There is a database that has stored procedures tied to a user called
?citrix?, and there is a user called ?citrix? on this server. However, these
users are not the same, even if they have the same name and password. To
prove this, try to remove the ?citrix? user from the database. Highlight the
database and choose Users. The ?citrix? user is listed. Highlight it and hit
delete. A message appears telling you that ?the selected user cannot be
dropped because the user owns objects?. The objects the user owns are stored
procedures. The owner of these stored procedures needs to be changed to the
new ?citrix? user. Unfortunately, you have to change the owner to the temp
user first. To change the owner to the temp user:
o Click Start, Programs, Microsoft SQL Server, Query Analyzer.
o Authenticate using the SA account.
o Select your database in the dropdown listbox at the top to the right
of the VCR-plas button.
o Expand the your database and then Stored Procedures. My database is
called ?Citrix MF DataStore?.
o The following stored procedures will be prefaced with the owner
?citrix?:
§ citrix.AddEntry
§ citrix.AddEntryData
§ citrix.AddIndexEntry
§ citrix.AddIndexEntryBlock
§ citrix.DelIndexEntry
§ citrix.JoinEnumerateEntry
§ citrix.JoinIndexSearch
§ citrix.JoinReadEntryByName
§ citrix.JoinReadEntryByUid
o These procedures must have their owner changed to the new ?citrix?
user.
o In the right-side pane, which is a query window, type the following:
EXEC sp_changeobjectowner 'Citrix.AddEntry', 'temp'
o Click the VCR-play button above the window to execute the query. You
will get a warning about breaking scripts, which is fine. Ignore it.
o Repeat this process for all objects that are prefaced with the
Citrix user name.
o Once you?re finished, you can refresh the object list on the left to
see the changes. You?ll see that the procedures now start with ?temp?
instead of ?citrix?.
? Delete the old Citrix user and add the new one.
o You should now be able to remove the old ?citrix? user from the
database.Highlight Users below your database. Highlight the ?citrix? user
and press delete. The user should be deleted.
o Add the new ?citrix? user to the database in the same way you added
the ?temp? user. Be sure to make the user a db_owner.
Change the objects? owner to the ?citrix? user:
o Go back to the Query Analyzer and change the owner of all the
?temp?-owned objects to ?citrix?. The syntax this time is:
EXEC sp_changeobjectowner 'temp.AddEntry', 'citrix'
o Notice that the name of the store procedure is now ?temp.AddEntry?
and the user name that you?re assigning ownership to is ?citrix?.
Move the Citrix servers to the new farm.
o Execute CHFARM on each citrix server.
o Looks like you can also reconfigure by using DSMAINT CONFIG. See
CTX677542.
Jeff Durbin
-----Original Message-----
From: thin-bounce@xxxxxxxxxxxxx [mailto:thin-bounce@xxxxxxxxxxxxx] On Behalf
Of Brian Delaney
Sent: 5 December 2003 9:11 AM
To: thin@xxxxxxxxxxxxx
Subject: [THIN] Re: Migrating Data Store question
ok, I am interested in how you moved from one SQL servet to another
********************************************************
This Week's Sponsor - ThinPrint .Print Server Engine
Thinprint can help you save money, protect resources,
simplify administration, save time and increase
flexibility by solving all of your printing needs.
http://www.thinprint.com
**********************************************************
Useful Thin Client Computing Links are available at:
http://thethin.net/links.cfm
Domains currently for sale by The Kenzig Group
http://www.kenzig.com/serv01.htm
New Site: Free Weblogs!
http://www.blogvortex.com
***********************************************************
For Archives, to Unsubscribe, Subscribe or
set Digest or Vacation mode use the below link:
http://thethin.net/citrixlist.cfm
Other related posts:
