Access SQL Server Configuration Manger of remote instances via Microsoft Management Console

You can connect to SQL Server Configuration Manager via Microsoft Management Console (MMC) to remotely manage SQL Server instances, which is especially useful when dealing with the instances deployed on Server Core. Before you connect it, you must deploy inbound rules to the Windows Firewall in the domain controller server, and then force member servers to apply the updated GPO. If the client is a script or a MMC snap-in, the sink is often Unsecapp.exe. Without deploying proper Windows Firewall rules, you might receive error message below when remotely connecting SQL Server Services in Computer Management:
There is no item in this view.
The RPC server is unavailable (0x800706BA).

To configure the specific Windows Firewall rule, please refer to the following steps:
1. Right-click Inbound Rules node in the target GPO, and click New Rule.
2. In the Rule Type step, choose Predefined, and choose Windows Management Instrumentation (WMI).
3. In Predefined Rules step, choose Windows Management Instrumentation (ASync-In).

For example, you are trying to remotely managing SQL Server Services of SQL-B via Computer Management in SQL-A; however, you encounter the issue described above, before you change any security settings in SQL-B, there are some basic ways could help you troubleshoot it:
1. Turn off Windows Firewall in SQL-B.
2. Turn off Windows Firewall in SQL-A.
3. Open Resource Monitor in SQL-B, switch to Network tab, open Computer Management in SQL-A and connect to SQL-B to check what processes, connections and ports are being using.
4. Open Resource Monitor in SQL-A, switch to Network tab, open Computer Management in SQL-A and connect to SQL-B to check what processes, connections and ports are being using.

Unable to connect/restart SQL Server service when deploying Log on as a service policy on SQL Server

Managed Service Account (MSA) is a special kind of domain account managed by a domain controller and is assigned to a single member computer and used for running services. The MSA password is managed by the domain controller. MSAs can register a Service Principle Name (SPN) with Active Directory. MSAs use a $ name suffix; for example, CONTOSO\SQL-A-MSA$.

If you use an MSA as the SQL Server service account, you need to grant the service account the Log on as a service right within the Group Policy Object (GPO) in the Group Policy Management. After configured the Log on as a service right, if you execute command (gpupdate /force) to force server to apply the updated GPO, you might receive error message – “The service did not start due to a logon failure” when connecting to SQL Server service, or “The request failed or the services did not respond in a timely fashion” when restarting SQL Server service. Check System event log in Windows Logs, you will find more details:
Logon failure: the user has not been granted the requested logon type at this computer.
This service account does not have the required user right Log on as a service.

The issue happens on the server instances starting SQL Server service with default virtual account. If you check Local Group Policy, you will find that NT SERVICE\ALL SERVICES is already added into Log on as a service. You can change Startup type of those SQL Server services to Automatic (Delayed Start) to minimize the conflict issue caused by Group Policy, or manually run the following steps as an alternative solution:
1. Open Services console.
2. Double-click the specific server instance you are trying to start.
3. In Properties dialog box, switch to Log On tab.
4. Clear value in the Password field, click Apply, click OK when prompting message “Passwords mismatch”, then click OK.
5. Retry starting the service.

Note that you must execute the steps above via Services console as it doesn't work in SQL Server Configuration Manager.

To execute the steps in Windows Server Core environment, the easiest way is to use Microsoft Management Console(MMC) because it can be used to access SQL Server Configuration Manager of remote instances:
1. Log on to the server that can connects to Server Core server.
2. Start an MMC snap-in such as Computer Management under Administrative Tools.
3. In the left pane, right-click the top of the tree and click Connect to another computer.
4. In Another computer field, type the computer name of the server that is in Server Core mode and click OK.

If you encounter error messages when execute above steps, please refer to Access SQL Server Configuration Manger of remote instances via Microsoft Management Console.


SQL Server - Check orphaned users in databases

An orphaned user is a database user whose corresponding SQL login has been dropped or the database is restored or attached to a different instance of SQL Server. You can detect orphaned users in a database by using the sp_change_users_login stored procedure with the @Action='Report' option.  If Action parameter is specified Report, it lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
EXEC sys.sp_change_users_login @Action='Report';
You can use the sp_change_users_login stored procedure to relink a database user with a SQL login. To link the specified user in the current database to an existing login, reference the following sample statement:
EXEC sys.sp_change_users_login


SQL Server - Use Windows Authentication across multiple SQL Servers via linked servers by using Kerberos

If you are interested in allowing users to use Windows Authentication across multiple SQL Servers via linked servers, then Kerberos Constrained Deletgation is a feature in Active Directory Domain Services that can help you achieve the goal. In order to use Kerberos, you must have the Service Principal Names(SPNs) set, and have Kerberos contrained deletegation configured. When the SQL Server service starts, an attemp to register the SPN in Active Directory Domain Services is attempted. By default, only the following accounts have permission to register SPN: Local System, Network Service, and Domain Admin.

In SQL Server 2012, by default Virtual Account is assigned and created when installing SQL Server instance, Virtual Account can access the network in a domain environment. To configure Kerberos, referencing the following steps:
1. Login to domain controller.
2. Go to Administrative Tools -> Acitve Directory Users and Computers.
3. Right-click the source machine object that hosts SQL Server -> Properties -> Delegation tab
4. Click Trust this computer for delegation to specified services only, and leave Use Kerberos only by default.
-> click Add button
-> Users or Computers
-> Advanced
-> Find Now
-> choose the destination machine(s)
-> choose the objects under MSSQLSvc service type, each instance has two objects under this type, please note that the port values respresent the default instance are displayed blank and 1433 for instance name and default port respectively.

If you use Managed Service Account(MSA) to start on SQL Server service, you need to grant sufficient permission to the account so that it can register SPN, and also you must configure Kerberos by accounts in domain controller via the following steps:
1. By default the Delegation tab is hidden in Users object. You must run the following sample of command to make it visiable: setspn -a MSSQLSvc/SQL-A.Contso.com spiner_tsai
2. Go to Administrative Tools -> Acitve Directory Users and Computers.
3. Right-click the account -> Properties -> Delegation tab
4. Same action as step 4 above.


SQL Server - Precautions against Copy Database Wizard

The Copy Database Wizard will create an Integration Services package to copy or move database(s), there are a couple of things you need to be aware of:

1. To use the Copy Database Wizard successfully, SQL Server Agent must be started on the destination instance.

2. You must select an Integration Services Proxy account that has access to the file system on both the source and destination instances.
1) Create an Integration Services Proxy account by first creating a credential under the Security node mapping to a user that has the appropriate permissions on the destination instance.
2) Add an SSIS Package Execution Proxy mapped to the newly created credential:
I. Start SQL Server Agent (if it is disabled).
II. Expand Proxies node, right-click SSIS Package Execution and choose New Proxy.
III. In New Proxy Account dialog, specify name in Proxy name field, and map to the newly created credential in Credential name field.

3. If destination instance does not have features that have been installed on source instance, the package may not be successfully executed. For instance, if Full-Text Filter Daemon Launcher has been installed on source instance while destination instance does not have,  you will receive an error message as follows:Full-Text search is not installed, or a full-text component cannot be loaded.
Note that select Text file in Logging options within Copy Database Wizard is helpful to find more details about the encountered error.

SQL Server - New built-in T-SQL features in SQL Server 2012 (compare with SQL Server 2008)

--Date/Datetime Functions
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 997)
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 998)
Be careful the millisecond part, 998 returns the same result as 997.
Be careful the millisecond part, 999 returns the result as 000 and add 1 to second part.
Because the milliseconds part of the end point 999 is not a multiplication of the precision unit, so SQL Server ends up rounding the value to next second.
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 999)

--Returns the end of input month date.
--String Functions

Substitutes a NULL input with an empty string.

Formats an input value based on a format string.
For more details, refer here: http://msdn.microsoft.com/en-us/library/26etazsy%28v=vs.110%29.aspx
SELECT FORMAT(123456789,'####-##-#.00')

Accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs Or return something else by designating the last expression.
SELECT COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product
The result above is same as the following, after testing, its performance is the same as well,
the only advantage is simplify the statement.
    WHEN ProductNumber IS NOT NULL THEN ProductNumber
FROM Production.Product
There are a couple of subtle differences between COALESCE and ISNULL. The type of the COALESCE is determined by the returned element, whereas the type of the ISNULL expression is determined by the first input.
DECLARE @x AS VARCHAR(3) = NULL, @y AS VARCHAR(10) = '1234567890'

Returns NULL instead of failing convert the input expression to the target type.
Note that it still needs to follow the rule of conversions table.
--Order Functions

Specify the OFFSET clause indicating how many rows you want to skip (0 if you don’t want to skip any); you then optionally specify the FETCH clause indicating how many rows you want to filter.
FROM TransmaxDW.dbo.JiraIssues
--Aggregate Functions

--Calculate accumulating value
In the window frame clause, you indicate the window frame units (ROWS or RANGE) and the window frame extent (the delimiters). With the ROWS window frame unit, you can indicate the delimiters as one of three options:
1. UNBOUNDED PRECEDING or FOLLOWING, meaning the beginning or end of the partition, respectively.
2. CURRENT ROW, obviously representing the current row.
3. <n> ROWS PRECEDING or FOLLOWING, meaning n rows before or after the current, respectively.
SELECT custid, orderid, orderdate, val,
  SUM(val) OVER ( 
          PARTITION BY custid
          ORDER BY orderdate, orderid
          --ROWS UNBOUNDED PRECEDING --Shorter form, same result as above
         ) AS runningtotal
FROM TSQL2012.Sales.OrderValues;
If you define a order clause without a frame clause, the default is as follows, unless you are after the special behavior you get from RANGE that includes peers (tied rows), make sure you explicitly use the ROWS option. In SQL Server 2012, the ROWS option usually gets optimized much better than RANGE when using the same delimiters.
--Offset Functions

--Retrieve data from a previous row or next row
SELECT custid, orderid, orderdate, val,
    LAG(val) OVER
     PARTITION BY custid
     ORDER BY orderdate, orderid
    ) AS prev_val,
    LEAD(val) OVER
     PARTITION BY custid
     ORDER BY orderdate, orderid
    ) AS next_val
FROM TSQL2012.Sales.OrderValues;
The second argument represents offset position, the number of rows back/forward from the current row from which to obtain a value. If not specified, the default is 1. It can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. The third argument represents the value to return when scalar_expression (first argument) at offset is NULL. If a default value is not specified, NULL is returned. It can be a column, subquery, or other expression, but it cannot be an analytic function, it must be type-compatible with scalar_expression.
SELECT custid, orderid, orderdate, val,
    LAG(val, 2, 0) OVER
     PARTITION BY custid
     ORDER BY orderdate, orderid
    ) AS prev_val,
    LEAD(val, 2, 0) OVER
     PARTITION BY custid
     ORDER BY orderdate, orderid
    ) AS next_val
FROM TSQL2012.Sales.OrderValues;

Return a value expression from the first or last rows in the window frame.
SELECT custid, orderid, orderdate, val,
     PARTITION BY custid
     ORDER BY orderdate, orderid
    ) AS first_val,
    PARTITION BY custid
    ORDER BY orderdate, orderid
   ) AS last_val
FROM TSQL2012.Sales.OrderValues;
When a window frame is applicable to a function but you do not specify an explicit window frame
clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Also, if you’re after the first row in the partition, using the FIRST_VALUE function with the default frame at least gives you the correct result. However, if you’re after the last row in the partition, using the LAST_VALUE function with the default frame won’t give you what you want because the last row in the default frame is the current row. So with the LAST_VALUE, you need to be explicit about the window frame in order to get what you are after. And if you need an element from the last row in the partition, the second delimiter in the frame should be UNBOUNDED FOLLOWING.
--Full-Text Data

You can create a search property list to define searchable properties for your documents. You can include properties that a specific filter can extract from a document. See the Books OnLine for SQL
Server 2012 article "Find Property Set GUIDs Property Integer IDs for Search Properties" for the list of some well-known ones, or refer to this article "Search Document Properties with Search Property Lists" see how Full-Text Search works with search properties.
ADD 'Authors'
   PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
   PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.'
--Sequence Object

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables.
It is similar to IDENTITY, all numeric types with a scale of 0 are supported. If you don’t indicate a type explicitly, SQL Server will assume BIGINT by default. If you need a different type, you need to ask for it explicitly by adding AS <type> after the sequence name. There are a number of properties that you can set, all with default options in case you don’t provide your own. The following are some of the properties and their default values:
1. INCREMENT BY   Increment value. The default is 1.
2. MINVALUE  The minimum value to support. The default is the minimum value in the type. For  example, for an INT type, it will be -2147483648.
3. MAXVALUE   The maximum value to support. The default is the maximum value in the type.
4. CYCLE | NO CYCLE   Defines whether to allow the sequence to cycle or not. The default is NO  CYCLE.
5. START WITH   The sequence start value. The default is MINVALUE for an ascending sequence  (positive increment) and MAXVALUE for a descending one. */


INSERT INTO TSQL2012.Sales.MyOrders(orderid, custid, empid, orderdate)
VALUES (NEXT VALUE FOR Sales.SeqOrderIDs, 1, 2, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 3, '20120620');

INSERT INTO TSQL2012.Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid), custid, empid, orderdate
FROM TSQL2012.Sales.Orders
WHERE custid = 1;

ALTER TABLE Sales.MyOrders
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;

INSERT INTO TSQL2012.Sales.MyOrders( custid, empid, orderdate)
VALUES (1, 2, '20120620')
If you accidentally reset start value, and the target table also has unique constraint on sequencing column to cause failure of violating constraint. Execute the following command to restart the sequence.
DECLARE @orderid_max INT, @str_alter VARCHAR(500)

SELECT @orderid_max = MAX(orderid) FROM TSQL2012.Sales.MyOrders

SET @str_alter = ' ALTER SEQUENCE TSQL2012.Sales.SeqOrderIDs
        RESTART WITH ' + CONVERT(VARCHAR(5), @orderid_max + 1)
--Error Handling
DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';

SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
RAISERROR (@message, 16, 0);

SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
THROW 50000, @message, 0;
The THROW command behaves mostly like RAISERROR, with some important exceptions. Errors must have an error number of at least 50000. The statement before the THROW statement must be terminated by a semicolon (;). This reinforces the best practice to terminate all T-SQL statements with a semicolon. RAISERROR does not normally terminate a batch; however, THROW does terminate the batch.

SQL Server - New management features in SQL Server 2012 (compare with SQL Server 2008)

FileTables are a special type of table that enables you to store files and documents within SQL Server 2012. These files and documents can be accessed from Windows applications as though they were stored normally in the file system. For example, you can add files and folders to the FileTable by dragging and dropping them in Windows Explorer. You can remove them from the FileTable by using the same method.

--Contained Database
Contained databases include all the settings and metadata required to define the database. Contained databases have no configuration dependencies on the Database Engine instance on which  the database is deployed, so users connect to a contained database without authenticating at the Database Engine level. An advantage of contained databases is that you can easily move them to other instances or to SQL Server 2012 Azure. Having all database configuration settings within the database enables the database owners to manage all those settings for the database.

--Server Roles
In SQL Server 2012, you can modify the permissions assigned to a new type of server role known as a user-defined server role. User-defined server roles are a new SQL Server 2012 feature. You can  use user-defined server roles to create custom server roles when using one of the existing server roles does not suit your specific requirements.

SQL Server - TCP/IP Properties in SQL Server

Instead of specifying 1433 in the TCP Port text box of each IP type section, you can only specify it in IPAll section.
You cannot specify 1433 in the TCP Port text box in the named instance when it is being used as default TCP port in the default instance. You must specify another fixed port which is not currently being used by other applications, you can run this command to check current port usage
netstat -a -n -o
For any instances which is not configured with 1433 default port, you must specify it in the connection string when remotely connecting to the named instance, for example, "SQL-B\ALTERNATE,3341".

The Window Management Instrumentation (WMI) is not available in Windows Server Core environment. The WMI provider is a published layer that is used with the SQL Server Configuration Manager snap-in for Microsoft Management Console (MMC) and the Microsoft SQL Server Configuration Manager. In that case there are two methods to configure TCP/IP properties of SQL Server in Windows Server Core environment.

Method 1
1. Open File Explorer, right-click Computer, and then choose Manage.
2. In Computer Management, right-click Computer Management (Local), and then choose Connect to another computer.
3. In Another computer field, type the computer name of the server that runs Windows Server Core.
4. After connection is successfully authenticated (if you have sufficient permission) you should be able to see SQL Server Configuration Manager snap-in under Services and Applications node.

If you encounter error messages when using Method 1, please refer to Access SQL Server Configuration Manger of remote instances via Microsoft Management Console.

Method 2
You can use SQL PowerShell to configure port by referencing the following steps:
1. Launch the SQL PowerShell in a command prompt.

2. Initialize the namespace that contains the classes representing the core SQL Server database engine objects.
$smo = 'Microsoft.SqlServer.Management.Smo.'

3. Set the ManagedComputer object that represents a Windows Management Instrumentaion(WMI) installation on an intance of SQL Server.
$wmi = new-object ($smo + 'Wmi.ManagedComputer'

$uri = "ManagedComputer[@Name='SQL-CORE']/ServerInstance[@Name='ALTERNATE']/ServerProtocol[@Name='TCP']"

$Tcp = $wmi.GetSmoObject($uri)

6. Check the value of the IsEnabled propoerty.

7. Set the property to true if it is on false.
$Tcp.IsEnabled = $true

8. Check the properties of each IP type, input different types in @Name parameter, for example, @Name='IP1', @Name='IP2', ..., @Name='IPAll'.
$wmi.GetSmoObject($uri + "/IPAddress[@Name='']").IPAddressProperties

9. Except IPAll, remove any value under TcpDynamicPorts section of each IP type, replace the question mark with specific number.
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IP?']").IPAddressProperties[3].Value=""

10. Remove value of TcpDynamicPorts section in IPAll type.
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPALL']").IPAddressProperties[0].Value=""

11. Specify fixed port of TcpPort in IPAll.
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPALL']").IPAddressProperties[1].Value=""

12. Validate all the changes.

13. Return all Windows services on local machine that contains key word 'SQL'.
Get-Service *SQL*

14. Stop SQL Server Database engine service of the named instance.
Stop-Service -Name 'MSSQL$ALTERNATE' -Force

15. Start SQL Server Database engine service of the named instance.
Start-Service -Name 'MSSQL$ALTERNATE'


SQL Server - Tuning performance of Project Server 2010 databases


As more users complain that different issues derived from the Project Server when managing projects and timesheets, the IT team has to spend more time troubleshooting issues. It not only downgrades users' working efficiency, it consumes internal resources on fixing problems.


To understand what problems users and IT team are currently dealing with, some actions have to be performed: 1. List issues that are reported from users. 2.Gather errors that are generated from system. 3.Monitor status of queue jobs.

Here is a list of the most commonly problems:
  1. Users have to wait for a long time until Project Server finishes updating/publishing projects.
  2. Timesheet issues:
    • Users are unable to update/submit their timesheet.
    • After users' timesheet has been rejected, they fail to resubmit/recall it.
  3. Some reports used for retrieving data from the Project Server seem to be executed inefficiently.
  4. The OLAP Database Build job is being configured to update the OLAP database every 1 hour.

Overview of Project Server

Let's have a brief of the target software. Microsoft Project Server 2010 is used for project portfolio management (PPM). Project stakeholders can prioritize project portfolio investments and deliver intended business value by either utilizing its web portal or Project Professional 2010.

The following diagram shows an overview of the Project Server 2010 architecture.

Here I focus only on tuning database performance. For more information about the architecture and the definition of these databases, please refer to the following links:

  1. Project Server 2010 architecture.
  2. Overview of Project Server 2010.


One thing I notice that timesheet issues very often happen during the process of updating/publishing projects, especially when some projects take long time to finish the process. It is caused by heavy blocking when doing these tasks in databases, and it is very likely to have poor query performance come across these operations.

Some legacy reports are being deployed with inadequate and inefficient queries to retrieve data directly from tables, and some of which access a large amount of data. It leads to increase the number of locked rows and contention.

The OLAP Database Build job takes up to 15 minutes to finish updating OLAP databases within company time. This can consume extensive time and resources.


Reviewing objects of the Published database is primary to solve conflicts between timesheet and project because the Published database contains all of the published projects and hosts the tables that are used by the Timesheet Queue. After examining 2,144 objects such as views, functions and stored procedures, 241 of which have great potential to enhance query performance.

Secondarily, monitor any external connection that communicates with tables directly. The best policy is to redefine security principals such as logins, roles, and users of least privilege. For any reporting services regarding the Project Server, connections should use the Reporting database as the main data source; and further, it should connect to views rather than tables, and also identify whether any operation retrieves enormous data at a time.

The OLAP Database Build job is an expensive task to the system. Use real-time dimensions only when absolutely necessary. The workaround is to adjust updating frequency to mitigate loading, or schedule it to be executed during off-peak hours.


After tuning process 6,342 database objects (except tables and indexes) are reviewed. A list of the number of revised objects is as follows (revised/total):

  1. Archive database: 0 / 260
  2. Draft database: 35 / 1,037
  3. Published database: 241 / 2,144
  4. Reporting database: 74 / 868
  5. Content database: 24 / 2,033
Rectifying these objects has dramatically improved performance of the Project Server, and also has greatly reduced issues. The positive result can be evaluated by the following facts:
  1. The queue jobs are executed efficiently, which jobs are no longer getting stuck in queues. This can be monitored in the Manage Queue Jobs. For instance, even one of the largest projects takes only up to 3 minutes to finish publishing.
  2. The number of issue between timesheet and project becomes considerably small.