Dmitri GropenAvailability of the applications is important in our Technical Support department. We need the applications to perform tasks that we need to do our job. RemoteApp is the Windows 2012 solution that allows to install Windows applications on the server and deliver those application to the RDP capable clients. The applications are using the resources on the server, but appear as if they are running on the client’s device.

It took me about an hour to install and configure RemoteApp and few more hours to install most of the applications that we use for technical support. Now anyone in the company can open up their favorite browser, go to remote desktop web portal, and launch the applications they need.

If this sounds like something that could be useful to you, these instructions should help you get up and running.

Here is what the Remote Desktop Web Access portal looks like when opened in a browser.


You can install and configure RemoteApp in less then an hour given that you have Windows Server 2012 installed and joined to a domain. Although you can install it on the same physical server, the best is to install it on a Virtual Machine so you can take a snapshot of the VM before installing a new application. To install RemoteApp, please follow the instructions below:

– Click “Manage” in “Server Manager”

– Select “Add Roles and Features” from the drop-down menu


– Click “Next” in “Add Roles and Features Wizard”


– Select the “Remote Desktop Services installation” radio button in “Add Roles and Features Wizard”

– Click “Next” in “Add Roles and Features Wizard”


– Select the “Quick Start” radio button in “Add Roles and Features Wizard”

– Click “Next” in “Add Roles and Features Wizard”


– Select the “Session-based desktop deployment” radio button in “Add Roles and Features Wizard”

– Click “Next” in “Add Roles and Features Wizard”


– Select the server from the “Server Pool” on which you will be installing RD services

– Click the “Next” button in “Add Roles and Features Wizard”


– Select the “Restart the destination server automatically if required” check box in “Add Roles and Features Wizard”

– Click the “Deploy” button in “Add Roles and Features Wizard”


Publishing RemoteApp Programs

– In “Server Manager” navigate to “Remote Desktop Services” / “Collections” / “QuickSessionCollection”

– Under “RemoteApp Programs” section click “Tasks”

– Select “Publish RemoteApp Programs” from the drop-down menu


– Select the applications that you want to publish in “Publish RemoteApp Programs” and click “Next” button.


– Close the “Publish RemoteApp Programs” wizard upon confirmation that your application were published.


Working With Your RemoteApp Applications Remotely

You can access your Remote Desktop Web Portal by typing “” in your web browser, replacing “” with your own host name.

You can ignore the self-signed certificate warning. You can install a trusted certificate later on if you wish.

– Provide the credentials for your domain user at the login prompt and hit the “Sign in” button

AccessRemoteApp01You can now launch and use your applications remotely.


Tagged with:  

Dmitri GropenSQL Server 2012 is the first SQL Server product that can be installed on Windows Server Core. Windows Server 8 is also the first OS which you can switch between Server Core and Server Full (with GUI) implementations. Although SQL Server 2012 can be installed on Windows Server 8 Core directly from the command line or configuration file, the easier way would be installing it on Windows Server 8 Full with GUI and then switching the OS to Windows Core.

Start the Windows Server 8 setup:

Install Window Server 8 with GUI by choosing this option in the setup wizard

Install .NET Framework 3.5 Feature. In Server Manager, click on Add roles and features, and follow the wizard. Make sure the OS has access to the Internet as it will need it to install this feature. Please note that .NET Framework 4.5 is already installed on the server by default.

You can now go ahead and install all SQL Server 2012 features on Windows Server 8 the same way you would do so on any other Windows OS. After the installation, you can do the necessary configurations graphically.

After you have configured and tested your SQL Server instance, you can go ahead and switch the OS to Windows Server Core. Go to your Server Manager and select Remove Roles and Features from Manage menu. Note that the Server Manager in Windows 8 has separate interfaces for removing and adding server roles and you cannot do both at the same time as you were able to do in the previous versions of Windows.

In the Remove Roles and Feature Wizard, clear Graphical Management Tools and Infrastructures and Server Graphical Shell check boxes, and click Next.

After the server is restarted, you will see the command prompt only. Type sconfig in the command prompt and hit Enter and you will be presented with the following screen:

You can switch back to OS with GUI by selecting option 12 “Restore Graphical User Interface (GUI).” Your SQL Server instance is now running on Server Core and you can connect to it remotely with Management Studio.

Tagged with:  

SQL Server 2012 Certifications

On April 10, 2012, in Technical, by Dmitri Gropen

Dmitri GropenThe new SQL Server 2012 certifications are coming up soon and shall be available in June 2012. Instead of three professional-level certifications that have been available before, only two will be offered: Data Platform certification and the Business Intelligence certification.

Each of those new professional certification tracks will include five exams. In addition, those professional-level certifications will require recertification every three years. Recertification requirements will be limited to one or two exams.

Upgrading from SQL Server 2008 to 2012 professional certification will now require passing three exams. The details on upgrading and recertification examination are not available yet. The following are new SQL Server 2012 certificates:

Common Certifications for Both Tracks

  • Exam 70-461: Querying Microsoft SQL Server 2012
  • Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Data Platform

  • Exam 70-464: Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

Business Intelligence

  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Tagged with:  

Partially Contained Databases in SQL Server 2012

On April 3, 2012, in How-to, Technical, by Dmitri Gropen

Dmitri GropenA database is called contained when it is isolated and independent from the instance of SQL Server that hosts it and from the objects stored in system databases of that instance. The databases that are dependent on the SQL Server instance objects, such as SQL Server logins mapped to database users, are called non-contained. All databases that are hosted on SQL Server instances prior to SQL Server 2012 are non-contained.

SQL Server 2012 introduces the partially-contained databases where the dependency on SQL Server instance is significantly reduced. One of the most important dependencies that can be bypassed in partially-contained SQL Server 2012 databases is the databases dependency on the SQL Server logins mapped to database users. You can create a database user with password in a partially-contained database and then connect directly to that database. No SQL Server login principal is needed. You will need to specify the initial catalog (name of the database) you are connecting to.  SQL Server will verify that this database exists, and then will let the database handle the authentication.

To be able to use partially contained databases, you will need to enable them at the instance level. In Object Explorer, right-click the server name, and then click Properties. In the Server Properties go to Advanced settings and set Enable Contained Databases to True.

Then you will need to set the Containment Type to Partial in the Database Properties of your database.

You can also set the containment type option to partial during the creation of your database. The system databases such as model database cannot be converted to partially contained databases.

The next step is to create user(s) with password(s) inside your partially contained database(s). In the Object Explorer of your Management Studio go to Databases >> YourDatabase >> Security, right-click on Users folder and select Properties. In the Database User properties go to General and select SQL user with password in the User type drop-down menu. Then enter User name and Password for your new user.

On the Membership page of your Database User option select the fixed database roles that you want your new contained database use to be part of.

Once the contained database user is created, you can connect to that database directly. To do so, you will need to specify the Initial Catalog, i.e. the database to which you are connecting with this user. When connecting to a contained database with Management Studio, in the Connect to Server dialog box, enter contained database user and password, and hit Options button.

In the Connect to database box type in the name of your contained database which you are connecting to.

If no initial catalog is specified, connection to a contained database will not be made, but the authentications will be passed to SQL Server instance instead. If the SQL Server login with the same name exists, connection will be made to the default database of that login. Contained database can have both SQL user with password and SQL user with login, therefore you can establish the connections to your database either directly with contained database user with password or through SQL Server login principal. Below is the approximate algorithm of the authentication process.

When you connect to a contained database with contained db user, you will see this database only. No SQL Server instace objects or other user databases will be displayed. Contained database user can still access mater and tempdb system databases as a guest, as well as other user databases on the same instance where the guest user was enabled.

Contained databases do not support change data capture, change tracking and replication,

Temporary tables in contained database are collated with the collation of that database instead of the collation of tempdb as in non-contained databases.

Contained databases are important in high availability solutions like AlwaysOn Availability Group. When databases are failed over another instance, it is easier for applications and users to re-connect to those databases as their connections do not depend on the SQL Server logins in a new instance.

Dmitri GropenSQL Server 2012 introduces a new beyond-relational platform called Semantic Search.  Semantic Search extends Full-Text Search capabilities by allowing querying not only for words in the text source, but also for meaning of that text.

Semantic Search builds its indexes using the indexes created by Full-Text Search. Semantic Search is also called Statistical Semantic Search because its intelligence is based on statistics.

Before you can use Statistical Semantic Search in SQL Server 2012, you will need to download, install, attach, and register Semantic Language Statistics Database.  This database contains the statistical language models that Semantic Search depends on.

Please follow this link to download msi package that contains the database.

Run the installer, and after installation is complete, find semanticsDB.mdf file in Microsoft Semantic Language Database folder under your Program Files directory. Attach this database file by right-clicking Databases folder in Object Explorer of your Management Studio, and selecting Attach button. Then click Add button and locate the database .mdf file to attach. Once the database is attached, register it by executing the following command:

USE master
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';

Verify that the database is installed by running the following query:

SELECT * FROM sys.fulltext_semantic_language_statistics_database;

To list the languages supported for semantic search, run this query:

SELECT * FROM sys.fulltext_semantic_languages;

You can create Semantic Search indexes on a table by running T-SQL commands, Full-Text Indexing Wizard, or in Full-Text Catalog properties.

To create Semantic Search indexes in Full-Text Catalog properties, in the Object Explorer of your Management Studio navigate to YourDatabase / Storage / Full Text Catalogs.  Right click on catalog name and select Properties.

Select the Tables/Views from the left list view. Add the tables for which you want to create indexes to the Table/View objects assigned to the catalog list. Select the columns under Available Columns list. Finally, select the check boxes under Statistical Semantic list next to the columns for which you want to build Semantic Search indexes.

Full-Text Catalog properties
Tagged with:  

SQL Server 2008 R2 vs. 2005

On July 27, 2010, in Technical, by Dmitri Gropen

Before I began to write this article, I had planned to cover all possible new features in SQL Server 2008 R2 comparing it to the 2005 edition. This appeared to be impossible because there are so many additions and enhancements to this edition that writing about each of them would fill a book. After I excluded the features that are not included in the edition of the servers we are running in our data centers, those that are disabled for the security or performance reasons, and those that require administrative rights at the instance level, I was still left with a significant amount of information to cover. So here are the additions that in my opinion will be useful to the most users.

New Features in Management Studio

The Management Studio also comes with new features and functionality with the release of SQL Server 2008 R2. The most useful features for developers and administrators are IntelliSense and Regions in Query Editor.

IntelliSense in the Query Editor

One of the most exciting features added to SQL Server 2008 R2 is the IntelliSense support in Query Editor of Management Studio. To be able to use this feature you need to be connected to SQL Server 2008 R2. It is not backward compatible with SQL Server 2005 edition. Unless you are one of those developers or DBAs who like to memorize all objects, their parameters, and T-SQL statements, or enjoy spending hour in books online looking for this information, this feature is a must. Besides completing a word and identifying an incorrect syntax, IntelliSence will list database objects and parameters required by functions and stored procedures as you type.

Query Editor Regions

Regions helps with organizing T-SQL code by grouping certain region of code. Regions can be expanded or collapsed by clicking the ‘-‘ or ‘+’ signs next to them which helps navigating and examining the code. A region is automatically created for batches ending with a GO statement, and for each BEGIN / END block.

Going Beyond Relational

There are more and more applications that require storing beyond relational data types. SQL Server 2008 R2 provides the functionality to store, search, and retrieve the beyond relational data types such as XML, Full-Text, Geometry and Geography types, Hierarchies, and Media.


Object-Oriented class inheritance, organizational charts, Family trees and Bills of Material are all examples of hierarchy which is hard to model with the traditional relational databases. The problem becomes more complicated when you need to implement functionality such as navigating and manipulating the tree or defining many-to-many relationships. Microsoft introduced a new data type called HierarchyID where the entire hierarchy can be stored. Although HierarchyID is a CLR data type, it does not require CLR to be enabled on the instance.

Geospatial Data Types

SQL Server 2008 R2 introduces new GEOGRAPHY and GEOMETRY data types that allow storing geospatial data in the form of points, lines, and polygons in the tables. These spatial types enable users to perform SQL queries and operations on spatial data within SQL Server 2008 R2 directly. You can also index those columns that store spatial data types. The geospatial data types and functions in SQL Server 2008 R2 are based on standards established by the Open Geospatial Consortium. Geospatial data can be imported into SQL server from the following formats: WKT, WKB and GML (Geospatial Markup Language). Geography and Geometry data types are supported by many major GIS (Geography Information Systems) such as ESRI ArcGIS, Mapinfo, Autodesk Map 3D 2007, Manifold, and more.

Working with Date and Time

Until Microsoft SQL Server 2008 R2, the ability to store date and time was limited. The only options SQL Server 2005 offered were the datetime or smalldatetime data types. The problem with those data type is that they do not allow storing only date or only time. Moreover, they do not account for time difference between physical locations (time zones). The results of those limitations are inconsistent data, storage and coding overhead.

SQL Server 2008 R2 introduces 4 new date and time data types; date, time, datetime2, and datetimeoffset.

datetime2 occupies 6-8 bytes and is able to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds, whereas datetime takes up fixed 8-bytes and stores date/time in range of January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds. The new datetimeoffset date type is similar to datetime2, but in addition it stores time zone offset in the range between -14:00 through +14:00.

The range of the date data type is between 0001-01-01 and 9999-12-31, whereas its predecessor datetime could only store the date range between 01-01-1753 through 9999-12-31. The range of time data type is between 00:00:00 and 23:59:59.9999999.

Upgrading to SQL Server 2008 R2

Mainstream support for SQL Server 2000 ended in April, 2008. The mainstream support for SQL Server 2005 will come to an end in April of 2011. If you are considering deploying your web application to SQL Server, we strongly recommend deploying it to 2008 R2 version. If you are currently using SQL 2005, SQL 2008, or even SQL 2000 versions, our advice would be to upgrade to 2008 R2 as soon as you can. The new SQL Server is fully backward compatible with its predecessors while providing better and faster performance for your applications. The steps to upgrade SQL Server are detailed in this community forum post.

Dmitri Gropen
Technical Support


SQL Trace for Query Optimization

On June 8, 2010, in How-to, Technical, by Dmitri Gropen

DiscountASP recently introduced SQL profiling service. The profiling service produces a trace file. The results of the trace are especially useful in determining the source of real-world problems that you are unable to replicate in your development environment. The goal is query optimization, and this article will give you some guidelines to help get the most out of your SQL database.
The query optimization process can be broken into two main steps:

  • Isolating long-running queries.
  • Identifying the cause of long-running queries.

The first step in optimizing queries process is to locate the queries that have the longest run-time, and the best tool for that is SQL Trace. Next, you need to analyze the long-running queries to determine where exactly they are spending their time and whether they can be improved.

Replicating the Problem

In order to be able to successfully locate the troublesome queries, the problem should be replicated while SQL Trace is running. This can be the most difficult part of the process because the issue can be random and intermittent, and trace can only be run for a very short period of time. Try to determine when your site is most likely to experience the problem and use that time in your trace request.

Requesting the Trace

When requesting the trace, the following information should be provided:

  • The amount of time to capture ranging from 2 to 15 minutes in 1 minute increments.
  • The exact date and time that the capture should start running.
  • The exact database name that the trace will be running against.

Getting the trace file

When the trace session is complete, the trace file will be placed in your site and you will be notified by email of your request completion. At this time you can do a number of things with the trace file including, but not limited to:

  • Opening and analyzing trace results in Profiler. Microsoft SQL Server Profiler is a graphical user interface to SQL Trace. Although you can view and analyze the trace results in Profiler, it is not useful for this task due to the large amount of information presented and because it lacks the capabilities of sorting columns.
  • Importing trace results to a table. This is the most powerful way to analyze the trace results since you can run custom queries against this table and thus have the most flexibility. A trace table can be used by Tuning Advisor and Profiler in the same way the trace file is used.
  • Using other third-party tools to analyze trace and tune your database.

Importing Trace into a Table

Start Management Studio, open a new query window and use the following T-SQL code to import your trace file to a table:

 USE [MyDatabase]
 SELECT * INTO MyTraceTable
 FROM ::fn_trace_gettable('c:\MyTraceFile.trc', default)

Replace [MyDatabase]with the database name where you are creating MyTraceTable. Replace:


With your actual trace file name and the path to the file.

Locating the Long-running Queries

You are now free to run any custom query against your trace table or you can use the sample add-hoc query below. This query locates queries that run for 1 millisecond or more and sorts them by duration with highest duration being on top. In this query I selected only three columns: EventClass, Duration, and TextData. The TextData column will contain the complete T-SQL code of the captured queries and can be run against your production database directly.

 SELECT           AS [Trace Event],
     T.TextData        AS [T-SQL Code], -- actual T-SQL Code
     (T.Duration/1000) AS [Duration (ms)] -- time in milliseconds
     dbo.MyTraceTable T
     sys.trace_events TE
     T.EventClass = TE.trace_event_id
 WHERE Duration > 1000 -- Filter out the queries that run for less then 1ms

Beginning with SQL Server 2005, the server reports the duration of an event is reported in microseconds. That’s why I divided it by 1000 to convert to milliseconds.

Examining Long-running Queries

You can now see the troublesome queries in the query results window after you run the query against your trace table above. Select the T-SQL code to examine by right clicking on T-SQL Code row, then copy and paste it to a new query window. You can now format this code in the query window the way you want and examine it noting which tables and columns are being used.

Replaying the Query

You can run any query in from the trace results against your database again. The good news is that you can replay those queries against your production database directly, whereas you cannot replay the trace using profiler and you cannot run tuning advisor against your production database because that requires sysadmin permissions that cannot be granted on production server.  At this point you may be able to fix the timing issue by indexing the tables and columns being used in the troublesome query. You can also examine the query execution plan to locate the exact problem if you have this advanced knowledge.

Dmitri Gropen
Technical Support

iBlog by PageLines