Windows 2008 Terminal services and SQL SSMS

Windows 2008 Terminal services and SQL SSMS

Rate This
  • Comments 3

One of our Principal Architects, Eddy Bell, shared a great writeup with me that I think is a great way to leverage Windows 2008 and SQL Server 2008. It answers a need that I've seen over and over again - the ability to connect to SQL Server Management Studio at the server, rather than running it locally. Here's the information as Eddy's shared with me:

At a customer visit a few months ago I was asked when would we come out with a web client to manage SQL Server?  I was interested in the reasons that this customer had for wanting a thin client. Since the customer had some time and was willing we grabbed some coffee and began an interesting exchange. The net result was the following list in order of importance.

1)      Access from anywhere, including from a guest machine

2)      No install on the client machine

3)      To have multiple versions of the client tools

A few weeks ago I had a chance to play with some of the features in Windows Server 2008 and one that jumped out at me was the new features in TS (Terminal Services). More specifically the TS Web Access and the TS RemoteApp. I decided to explore running the SQL Server 2008 management tools in the new TS environment. Below is what I did and how it came out.

1)      I installed W2k8 (Windows 2008) including the TS components and dependent components like IIS. A good place to start is http://technet2.microsoft.com/windowsserver2008/en/library/ddef2b89-73cf-4d74-b13b-47890fd1a6271033.mspx?mfr=true

2)      I installed SQL Server 2008 CTP6 management tools on the W2k8 server.

3)      I followed the http://go.microsoft.com/fwlink/?LinkID=84895 in setting up first the TS RemoteApp and then the TS Web Access.

 

a.       Configured the users and groups that would have access

 

b.      Enabled Network Level Authentication for TS

 

c.       For licensing I did nothing which gives me 120 days to do the evaluation.

 

d.      Started the (start->Administrative Tools->Terminal Services->TS RemoteApp Manager->Action->Add RemoteApp Programs) wizard. From the list of applications that are installed on the server I selected several of the SQL ones, SQL Server Management Studio, SQL Server Books Online and SQL Server Profiler.

 

e.      For SSMS and SQL Server Profiler I edited the properties and set the “Allow any Command-line Arguments”, I did this because I wanted to control how these two apps came up. As it turns this really is only useful for the TS RemoteApp and not for the TS Web Access.

4)      From the TS RemoteApp Manager I selected SSMS and SQL Server Profiler right mouse clicked and selected “Create .rdp File”. This started a wizard that created the rdp files. These files can be emailed, copied … to a client machine and when evoked, will start the remote application on the TS Server and route the display and keys back to the client machine using an encrypted channel. The .rdp file can be edited to add in things like command line switches, which drives to share through rdp protocol, if sound should be routed to the client, 

5)      From the TS RemoteApp Manager I selected SSMS and SQL Server Profiler right mouse clicked and selected “Create Windows Installer Package” This started a wizard that would create the install package. What the install package gives you over just the rdp files is:

 

a.        automatic Desktop icon creation

 

b.      Creation of a Start menu folder

 

c.       You can also associate client extensions for this app.

It was now time to play. I started out with my VISTA RTM laptop. I will include some of the screen shots give a better idea of what to expect.

TS Web Access

1  1 2 2

I encountered a problem; the TS RemoteApp only supports RDP 6.1 and later. After a little research it became apparent that the RDP 6.1 install package was not available as a standalone install. I needed to install SP1 for Vista or SP3 for Xp. A few hours later I was able to avoid the screen #2. After the prompt for device sharing, there was a slight pause for the one time only per user install. This sets up the SSMS user space on the TS. SSMS then came up just like a locally installed SSMS.3 4

 

3  4  5

TS RemoteApp via rdp: double clicked the desktop icon and SSMS comes up just like it was locally installed.

6  7  8 
Now for the real test can you tell which SSMS is remote and which is local?

9

Summary:

In using both the remote and local copies of SSMS I could not tell which one was which. The only exception was when accessing the file system from SSMS, I could tell by the number of drives and directory names. I did do some accessibility testing with both clients and found that unless you install the accessibility screen reader on both the local and remote TS server you would get no accessibility events for the TS RemoteApp.

 I believe that applications hosted in TS RemoteApp offer a viable alternative to a web based thin client. The TS RemoteApp server coupled with the TS Gateway and TS Web Access offer a more secure solution than comparable web applications.  For me the TS RemoteApp coupled with the SQL tools gave me:

Pros:

1)       A rich client experience.

2)      An extremely small client side footprint.

3)       The responsiveness of the UI was so good I could not distinguish between the local and remote client.

Cons:

1)      The requirement for RDT 6.1 with no standalone install was painful

2)       The accessibility problems have me concerned for the experience of anyone that needs accessibility.

3)      The need for a separate license for the TS Server (CAL)

4)      Client side footprints for SSMS; persistent, runtime

SSMS

Persistent size(footprint)

Runtime size

TS WebAccess

0K

VS=200M,PB=50M,WS=40M

Rdt file on desktop

2K

VS=200M,PB=50M,WS=40M

Msi installed

75K

VS=200M,PB=50M,WS=40M

VS(virtual size) PB (private bytes) WS(working set)

Links of interest:

http://technet2.microsoft.com/windowsserver2008/en/servermanager/terminalservices.mspx A good starting Place

http://go.microsoft.com/fwlink/?LinkID=84895 How to set up TS for Applications rather than for a remote desktop. This also includes setting up the http://technet2.microsoft.com/WindowsServer2008/en/library/95258ce8-5ddd-42cb-9e95-cec19ef4f43d1033.mspx

 

http://go.microsoft.com/fwlink/?LinkID=85872 If you need to bridge a firewall for your TS RemoteApp

 

http://go.microsoft.com/fwlink/?LinkID=85873 A necessary evil

http://go.microsoft.com/fwlink/?LinkID=110395

http://go.microsoft.com/fwlink/?LinkID=101748

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Buck Woody has an interesting post about Windows 2008 Terminal services and SQL SSMS . He includes setup

  • That's pretty cool

    So this is similar to Citrix Remote App then?

    just like you can put MS Office, MS Outlook into TS Web Access (or Citrix)

  • To make better use of features and enhancement within Windows Server 2008 release and SQL Server 2008,

Page 1 of 1 (3 items)