Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Wednesday, July 27, 2016

XenDesktop 7.x Database Maintenance & Considerations

I recently spent time with a client having SQL issues related to XenDesktop. As it turns out they were exhausting transaction log space. I figured I would throw together a post about some of the considerations and best practices I have seen around this topic.

Considerations N'at

  1. Monitoring: 
    • First and foremost, whatever you design, something-will-fail-eventually. I cannot stress monitoring enough. From simple OS-level monitors such as disk space and event log scraping to more-in-depth (and expensive) end-to-end monitoring solutions. My personal favorites over the years have been ControlUp and Solarwinds. You can write your own via PowerShell with a little research if you can't spring for a third-party product. I just can't harp on this item enough.
  2. One-or-many? 
    • It is recommended to separate the 3 primary DB data sources into separate actual databases. A Site, Logging, and Monitoring DB. In the latest versions this is the option up front. Original releases this had to be done after the site was deployed. 
  3. The Databases:
    1. Site: 
      • Size corresponds to user activity (since session information is tracked). Peak usage = peak size.
      • Two-days worth of connection data is kept in the site DB, thus the largest size will be achieved two days after peak user connectivity.
      • Activity is greatest during high login periods as information is frequently requested.
      • An outage to this DB can cause the inability to launch sessions (session leasing not withstanding)
    2. Logging: 
      • Historical logging of site changes
      • Sizing is not predictable as it depends on the number of changes in the site (including administrative actions in Director)
      • Low I/O needs
      • There is no retention policy so old data must be purged manually.
      • If site policy does not allow changes to be made without the logging DB present, no site reconfiguration will be allowed. 
    3. Monitoring:
      • Used by Director to display data. 
      • Max size is determined by retention period (7 days for non-platinum; platinum defaults to 90 and is configurable). Peak size will be when this period is reached.
      • Data is collected in a manner that limits transactions, thus lowering I/O needs
      • Data flushing occurs overnight. 
      • An outage prevents collection of historical data
  4. The TempDB:
    • The SQL Temporary DB impacts the performance of SQL and the performance of XenDesktop. Usually I will configure SQL with specific drives for Data, Logs, Backups (depending on solution), and the TempDB. This allows for flexibility in regards to sizing and performance. The purpose of the TempDB is to eliminate locks on the Site DB (and others) which in a large scale environment could lead to slow performance. This DB is usually small, and a sizing guide is in the references section. 
  5. Use Read-Committed Snapshot Isolation:
    • Say what? In short, this isolation improves performance, particularly during intense login periods. If you have a larger environment, users of Studio and Director will thank you.
  6. Sizing:
    • As much as I would love to write a novel about DB sizing - the below table from the Citrix document linked under references sums up a ballpark for your site-DB sizing needs. The other DBs are tougher to predict and should be monitored and are more variable. The Monitoring DB will likely grow the largest of all three. There are some estimates in the sizing guide on pages 6 & 7 if review is desired.
  7. CPU Usage:
    • Summary of Citrix's intensive testing of SQL performances recommends a single quad-core SQL server for XenDesktop deployments. 
  8. DB Maintenance:
    • Usage of DB Mirroring or Always-On features results in full transaction logging mode. This results in the transaction logs of all three DBs to continue to grow until a log backup is taken. This will cause issues without monitoring (disk space utilization, performance).
    • It is recommended to back up transaction logs regularly if utilizing one of the mentioned high availability methods. This could be done with scheduled backups or with SQL Server Agent. 
    • Citrix recommends Index rebuilds occur over night or on weekends. Page 13 of the Sizing Guide provides more details on scripts for this. (They actually recommend using the Windows Server Update Services script modified for XenDesktop.)



References (just so you know I didn't make all of this up).

Saturday, December 6, 2014

Moving that DataStore, is there still someone attached?

While migrating a Citrix XenApp datastore isn't difficult (see How to move or migrate data store) can can rather quickly be scripted across all your farm servers, one question always remains... did I miss one?

If you try to take your original DB and perform the SQL Management Studio action "Take Offline" and all you get is a progress wheel that, well, never progresses, you may have one or more connections still active.

So how do you check? If you open a new query in SQL Management Studio prior to trying to take the DB offline you type and execute the command "sp_who" - the results of this will show all connections to the SQL Server including the source, user, and database. If your server is dedicated to your SQL infrastructure you can quickly parse this list to see what servers may have been missed.

Alternatives if you have a shared environment could be the query below that will tell you the number of connections active to each DB:

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName, nt_domain as NT_Domain, nt_username as NT_UserName, hostname as HostName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, hostname, loginame,nt_domain,nt_username order by NumberOfConnections DESC

Finally, you could combine the two options and build a temporary DB to see only the connections open to your data store. 

There are a number of samples on the web for this method, one such is listed below. Just change the DB name 'master' to the DB name of your Citrix XenApp DataStore. 

DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
                     loginname varchar(50),   
                     hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int) 
INSERT INTO @temp  
EXEC sp_who
SELECT * FROM @temp WHERE dbname = 'master'