How the SQL database works with Citrix? 1

[isterios]

Hello,

Sorry I am a newbee with SQL.

I set a SQL database with my Citrix presentation server 4.0. The problem was that I didn't create a specific base for Citrix, so Citrix created its base (apparently) in Master.

So I created a new database on my SQL server, named Citrixdb. I just changed the DSN path to this citrixdb instead of master. Apparently it works perfect.

But I don't understand something: I want Citrix to use a SQL Datastore. But by default, where is this SQL datastore? In master?

In fact I don't understand where citrix creates the datastore using SQL.

Does the DSN (on the citrix server) exists only for reports?

thank you.

 

[ascotta]

Ok here we go.

1 You don't ever want to play with the master SQL database.

2 You should create the database before installing the first server in the farm.

3. The DSN is used by the IMA service to update no-volatiles data to the data store +- general data updates every fiteen mins or so.

Be very carefull when using defaults in any install.

If I was you, I would be uninstalling Citrix from your box and starting again, to make sure it is playing with the correct datastore.

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

Cheers
Scott

 

[isterios]

Thanks Ascotta.

More precisely, I would like to change the default base which aims on Master to my new database called Citrixdb.

I know it's easier to reinstall Citrix but I would like to learn how to do it (this is my test configuration so no problem if I break something).

Can I do it with dsmaint migrate? If yes, what would be the correct syntax?

For information, both bases are on the same SQL server, in the same folder.

 

[ascotta]

Yes it is DSMAINT /migrate /user:usename /pwdassword /dsn:"full path to dsn" It MUST be the full path.

You just set your DSN to point to your SQL database that you want it to point to.

Then Re-start the IMA Service. Full details in the Advanced Administrators giude on the Citrix web site.

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

Cheers
Scott

 

[mattjurado]

How big is your farm going to be? Don't forget to backup the database if its important.

Matt J.

Please always take the time to backup any and all data before performing any actions suggested for ANY problem, regardless of how minor a change it might seem. Also test the backup to make sure it is intact.

 

[isterios]

Thanks but precisely, what is the syntax for /dsn:"full path to dsn" It MUST be the full path.

Is it for example /dsn:\\sqlserver\citrixdb ?

What and where is "dsn"?

 

[ascotta]

The DSN is an ODBC file that your create on each Citrix server to connect to your SQL database. Normal location of this is

C:\Program Files\Citrix\Independent Management Architecture\MF20.dsn

remember the quotes round it too.

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

Cheers
Scott

 

[isterios]

Ok there is still something I don't undertsand:

In summary, my citrix works on a database called Master (default)
I create, on the same SQL server, another database called citrixdb. Just a simple creation.
I want Citrix to put his global datastore in Citrixdb and not in Master.

I change on my citrix server the DSN settings and put: "change the default database to: citrixdb" (instead of master)

Now, I use dsmaint migrate c:\...\mf20.dsn to c:\...\mf20.dsn (problem: I have only one file mf20.dsn in the same directory, so I migrate a file to exactly the same file...)
By the way I do this migration:
Result: "an unknown error occurred while migrating the table, please verify the destination datasource is clean. [Microsoft][ODBC SQL server Driver][SQL server]violation of primary key constraint 'CL'. Cannot insert duplicate key in object "keytable". Indexes have been successfully patched.

So is it normal to migrate a dsn to the same dsn?

 

[ascotta]

You are not migrating the dsn, you are migrating the database.

What to do is.

1) create new shiny database on sql server.
2) Copy mf20.dsn to oldmf20.dsn
3) dsmaint migrate the database
4) In ODBC Maanagement in Administrative tools on EACH citrix server change the IMA dsn to point to new database

Restart the boxes.

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

Cheers
Scott

 

[isterios]

Thanks for your patience ascotta

Just to be sure: about your point 4)"change the IMA dsn".

In my System DSN, I have only the "RsSummaryDatabase" I created (following the Administrator Guide), which have a SQL driver.
I have a "IMA directory" but driver is a Microsoft Access Driver => lmalhc.mdb

So are you talking about my RsSummarydatabase? (only which have a SQL driver)

Thks.

 

[ascotta]

That is for Resource Manager.

The imalhc is for the local host cache which is an in memory copy of the data store.

I will go and have a look at the exact syntax for you.

As an aside, geet the Advanced administrators guide(pdf) from the Citrix web site, it tells you exaclty how to do it.

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

Cheers
Scott

 

[ascotta]

Hi isterios,

this is from a pdf doc called Data Store Maintenance from citix dot com




To point a MetaFrame XP server farm to a new database For the best performance, execute this procedure on the data collectors after all other servers are reconfigured.

1. Back up the existing farm database and restore the database to the new server.

2. Create a new DSN file that points to the database restored in Step 1.

3. Execute the dsmaint config command on the server with the new DSN file from Step 2.

4. Stop and restart the IMA service. Important: Restarting the IMA service instead of restarting the server might cause the SNMP service to Dr. Watson if SNMP is enabled. This error is benign.

5. Verify that the server is pointing to the new data store by looking at the following registry setting: HKEY_LOCAL_MACHINE\SOFTWARE\Citrix\IMA\DataSourceName

6. If the IMA service started successfully, copy the DSN file created in Step 2 to all servers in the farm.

7. Execute the dsmaint config command to change the IMA service configuration on all remaining servers in the farm.

8. Stop and restart the IMA service on all servers in the farm.

Tip: You can execute Steps 6 through 8 on all the servers from a simple batch file placed in a central location

An alternative method for migrating between same version databases is to use the export/import process instead of a backup and restore process. Microsoft SQL Server 7 uses OLE for the export/import functions, which truncates any binary data greater than 256 bytes.

As such, using export/import on Microsoft SQL Server requires that the following steps replace Step 1 above:

1. Export data from the original (source) database and import data into the new (destination) database using the standard export/import procedures described in the database vendor’s documentation.

2. In the destination database, delete the table DATATABLE. This table contains the truncated data as described above.

3. Execute the following SQL command (on one line) in the destination database context to recreate the DATATABLE table with non-truncated data: SELECT * INTO DATATABLE FROM [SourceServerName].[SourceDBName].dbo.DATATABLE;

Note: The brackets in the above SQL command are required if the name contains a space or special character. In addition, you must register the source server with the new database server as a linked server and the source server must be running when the SQL command is executed.

4. Recreate the following indexes on DATATABLE: DATATABLEINDEX cluster index on nodeid DATACONSISTENT unique index on (nodeid and partno) Note: You can recreate the index by choosing Run a Wizard > Database > Create Index Wizard from the Enterprise Manager Console.

5. Follow Steps 2 through 8 above to complete the database migration process.

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

Cheers
Scott

 

[manuel992000]

Hi everone,
I m newer here, i need few help about SQL 2000 n Citrix PS4.
I ve created my sql db called ctxdb have i ve assigne an active directory user like the db owner.

When i create my farm i choose my sql server i select windows n sql authentification. After write the user login n password, i ve got this error:

MS SQL Server login
connection failed
SQLState:'28000'
SQL Server Error: 18456
Login failed for the user

I ve patched MDAC with the v2.8 sp1.

TY for your help