MetaFrame XP Interoperability with SQL 2000

1. MetaFrame XP Interoperability with SQL 2000

2. Agenda • IMA Overview (Review) • Farm Data • IMA Service • Change Notification/Polling • SQL Basics\Troubleshooting • SQLDIAG • Service Packs • Monitoring • MDAC/Component Checker • SQL Profiler • Best Practices • Maintenance Plans • DB Options/Recovery models/Max Size/Autogrow • Tempdb • FR3 Information • Q & A

3. IMA Overview - XP Farm Data Collector Data Store Metaframe XP Server Farm

4. XP Farm Data • Static - Data Store (DS) • Information that changes infrequently • Farm configuration and settings • Published applications, licenses strings, load evaluators, Citrix administrators, etc. • Local Host Cache (LHC) is a subset of data contained in the DS • Dynamic - Data Collector (DC) • Information that changes frequently • Sessions, disconnected sessions, load, etc. • Responsible for resolutions

5. IMA Service Basics • Data Store collects static configuration data • Using direct mode each XP server maintains an open connection to the SQL server ODBC TDS MS-Jet ODBC Data Store ODBC LHC Access, Oracle, SQL or DB2 Access database

6. IMA DSN Configuration • IMA Service will make an ODBC connection to the SQL server using Persistent Connection mf20.dsn • Example DSN [ODBC] DRIVER=SQL Server UID=sa DATABASE=XP WSID=RAIBMWIN2K APP=Citrix IMA SERVER=(local) Description=XP • To reset password for IMA Service • dsmaint config /user:<user> /pwd:<password> /dsn:<Path> Persistent Connection ODBC Data Store

7. IMA Service Basics How is the LHC kept up-to-date? • Syncing with Data Store at Start-Up • Change Broadcasts • Change Polling

8. IMA Service Start Up • During start-up each subsystem tells the LHC what data it wants cached. • Initially LHC database is empty and all necessary data is copied over from the Data Store. • On subsequent start-ups, a reconciliation is performed on the LHC against the Data Store. • Only changed data is copied from the Data Store. This catches any changes that occurred while the service was stopped.

9. CMC Change Notification Member Server Member Server Citrix Management Console Member Server Member Server A Data Collector Data Collector Member Server Member Server Data Store Member Server • Change is made on the CMC 2)The member server (A) writes the change to the DS and forwards the information to the DC • The DC updates the LHC on the member servers in its zones and forwards the information to all the other DCs 4) The other DCs send the information to their member servers

10. Change Polling • Used as a backup method of discovery • Occurs by default every 10 minutes (w/o hf XE102W064 – previously XE102W036) • FR3 30 minute interval • Configurable in registry under HKLM-Software-Citrix-IMA-DCNChangePollingInterval Data Store

11. IMA Polling SELECT COUNT(*),CAST(AVG(CAST(imalock AS FLOAT)) AS INT) FROM KEYTABLE IMA looks at KeyTable counts the number of rows and does an average of the IMALOCK value to see if anything has changed * Note The Polling window is +/- 10% Polling Interval Time Data Store

12. Data Store Unavailable Data Store • 96 hour license limit • No changes can be made to the farm • CMC – Qfarm will fail if Data Store is down

13. Notification of Data Store Availability • Event written to the Event Log every hour • Also written to registry key HKLM – Software - Citrix – IMA – DataStoreFailureTime • Countdown does not reset with reboot • Counter available in Performance/System Monitor and Resource Manager

14. Microsoft SQL Server

15. SQL Service Packs • When installing a new service pack, the sp makes changes to the system tables for maintenance reasons, and upgrades user and distribution dbs that are members of a replication topology. Due to these changes, service packs cannot easily be removed. There is no automated way to remove a service pack. • Detach all user databases • Stop all SQL Server services • Uninstall SQL Server by using the Add/Remove Programs • Install SQL Server • Apply service pack • Restore the master, msdb, and model databases from backup if you have backups that match the version of service pack to which you want to revert. This automatically attaches any user databases that were attached when you created the backup. Attach any user databases that were created after the last backup of the master database

16. Troubleshooting Tools • Eventlogs (Critical and Severe Errors) • SQL Profiler • Performance Monitor • Component Checker • Enterprise Manager • Query Analyzer • 7.SQLDiag collects the following information: • %systemdrive%\Program Files\Microsoft SQL Server\MSSQL\Binn • Collects SQL error logs • Configuration Information • Database Information • Server Information

17. TempDB Case Study 2002-11-24 08:46:30.83 spid360 BobMgr::GetBuf: Bob write not complete after 60 seconds. 2002-11-24 08:46:32.61 spid319 BobMgr::GetBuf: Bob write not complete after 60 seconds. 2002-11-24 08:46:32.61 spid294 BobMgr::GetBuf: Bob write not complete after 60 seconds. 2002-11-24 08:46:32.74 spid355 BobMgr::GetBuf: Bob write not complete after 60 seconds. tempdb holds all temporary tables and temporary stored procedures tempdb is re-created every time SQL Server is started temporary tables and stored procedures are dropped automatically on disconnect If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing

18. MDAC – Microsoft Data Access Components IMA Service uses ODBC to contact Data Store Its recommended to use 2.6 SP2 or 2.7 on the SQL server

19. Troubleshooting ToolsComponent Checker • Identifies version of ODBC in use • Identifies version mismatches • Set MultipleInstances=1 to use under Terminal Services in comcheck.ini Available at www.microsoft.com/data

20. Monitoring Database Connections from SQL • From SQL you can determine which servers are currently connected to the SQL server • SQL Enterprise Manager or Query Analyzer • Server/Management/Current Activity/Process Info

21. T-SQL Commands in Query Analyzer • Sp_who,sp_who2 - Activity information • sp_helpdb databasename – Shows size information • sp_helptext sp_name – List stored procedure text • sp_help – Will list all database objects • Print @@version – Displays • ExampleSQL version 8.00.760 = SP3 • Kill (spid) – Terminates SQL connection on database • Sp_configure List and change SQL server settings

22. Best Practices Citrix Support Staff

23. Data Store Power • CPU power affects the following actions: • Installing a server into the farm • Adding a license to the farm • Uninstalling a server from the farm • Starting IMA on multiple servers • LHC coherency check

24. Data Store Tuning • Increase SQL Server Worker Threads • Increase worker threads if there are more than 250 servers in the farm. (Enterprise Manager or stored procedure/stop and restart SQL service) • Right click properties of server/Processor tab/Max Worker Threads in Enterprise Manager • SQL Clustering • Provides redundancy but does not give a performance boost • NIC Teaming • Watch the throughput on the data store’s network card. Consider teaming an additional NIC for increased throughput. • RAID tuning • Disks should be RAID 5 or 1+0 (recommended). If possible, tune the RAID controller for 100% reads, 0% writes.

25. Automating Data Store Administration Note: SQLServerAgent Service must be running to execute plan

26. SQL Maintenance Plan Setup Select the database(s) to be included in the Maintenance Plan. The recommended setting when checking “Reorganize data and index pages” is to select “Reorganize pages with the original amount of free space. You have the ability to choose specific days & times for each part of a SQL Maintenance Plan via the Schedule section.

27. SQL Maintenance Plan Setup At the completion of the maintenance plan setup process, ensure that the SQL Server Agent service is running in order for the plan to execute.

28. Transaction Logs • Recovery Models • Simple Recovery • Full Recovery and Bulk-Logged Recovery • To change recovery model: Select Database properties, Options tab, recovery model can be selected here (be sure to discuss your back-up plan with your DBA) • Log truncation occurs at these points: • At the completion of any BACKUP LOG statement • Every time a checkpoint is processed, provided the database is using the simple recovery model

29. Suggested Hotfixes • XE102W064 (formerly XE102W036) • Large MetaFrame XP Feature Release 2/Service Pack 2 server farms sometimes experienced data store contention problems when simultaneously adding or upgrading multiple servers to the farm. • XE102W028 • The initialization of a global variable indicating the default name of the ODBC configuration file was moved. As a result, the global variable was not initialized and the DSN file could not be loaded, causing the creation of the database connection to fail.

30. Performance Tips • Maintenance Plan • Stop and restart SQL to flush Tempdb daily in large farms • Initially set XP DS and Tempdb to large size to limit auto grow • Keep Transaction Log and Database on separate drives and not on System drive • Make sure Auto Stats Option is turned on

31. Support SQL Subject Matter Experts • Card, Tim Tech Engineer 2 Enterprise NT Support Americas • Santos, Ray Tech Support Lead Eng. NT NT Support Americas • Aldridge, Michael Technical Relationship Mgr EMEA SE Team • Birk, Rob Tech Engineer 2 NT NT Support Americas • Bomfim, Jacqueline Tech Engineer 1 NT NT Support Americas • Conran, Andre Tech Engineer 2 NT NT Support Americas • Davy, Andrew Tech Engineer 2 Enterprise NT Support Americas • Garten, Kevin Tech Engineer 1 NT NT Support Americas • Harding, Stacy Tech Engineer 2 NT Web/Unix Support • Hines, Patrick Technical Relationship Mgr Enterprise Americas • Keyzer, Kim Technical Relationship Mgr Enterprise Americas • Maharaj, Ken Tech Engineer 2 Enterprise Web/Unix Support • Moody, Rob Technical Relationship Mgr Enterprise Americas • Nittel, Darnell Tech Engineer 2 NT NT Support Americas • Plant, Chris Technical Relationship Mgr EMEA SE Team • Rabinowitz, Jon Tech Engineer 2 NT NT Support Americas • Sai, Suh Tech Engineer 1 NT Japan Support Engineers • Salvatierra, Manuel Tech Engineer 1 NT NT Support Americas • Schwalbe, Enrico Technical Relationship Mgr Enterprise EMEA • Serriere, Frederic Tech Support Lead Eng. NT Enterprise EMEA • Yokohara, Tsuyoshi Sr. Product Readiness Manager Japan Technical Support

32. Questions?