Home » Computers » SQL

Category Archives: SQL

Create the Key Distribution Services KDS Root Key

Create the “KDS Root Key” for use with Managed Service Account (MSA) and Group Managed Service Accounts (gMSA). Use the New-KdsRootKey PowerShell cmdlet for set up and initialize the KDS root key.

  1. On the Windows Server 2012 domain controller, run the Windows PowerShell from the Taskbar. (I normally run it as Administrator.)
  2. At the Windows PowerShell, type the following command, and then press ENTER:
    Add-KdsRootKey –EffectiveImmediately

The domain controllers will wait up to 10 hours from time of creation to allow all domain controllers to converge their AD replication before allowing the creation of a gMSA. The 10 hours is a safety measure to prevent password generation from occurring before all DCs in the environment are capable of answering gMSA requests. If you try to use a gMSA too soon the key might not have been replicated to all Windows Server 2012 DCs and therefore password retrieval might fail when the gMSA host attempts to retrieve the password. gMSA password retrieval failures can also occur when using DCs with limited replication schedules or if there is a replication issue.

Even if there is only one DC you still have to wait the 10 hours.  If you don’t want to wait you can refer to the Microsoft TechNet article this information was taken from: https://technet.microsoft.com/en-us/library/jj128430.aspx

SQL MSA and gMSA info

“Managed Service Account” (MSA) and “Group Managed Service Account” (gMSA) articles:
   and

DPM Errors with SQL Server Windows Management Instrumentation

Error in DPM:

“DPM could not enumerate SQL Server instances using Windows Management Instrumentation on the protected computer”

In order to fix the error:

  1. Login to your database server
  2. Open Command Prompt and browse to C:\\Program Files (x86)\\Microsoft SQL Server\\100\\Shared
  3. Run the command: mofcomp sqlmgmproviderxpsp2up.mof

mofcomp C:\\Program Files (x86)\Microsoft SQL Server\\100\Shared\\sqlmgmproviderxpsp2up.mof

Connect to Windows Internal Database (WID) on Server 2012

There has been a change to the Named Pipe path of the Window Internal Database (WID) on Windows Servers 2012.  The new path uses “tsql” instead of “sql” that has been in all previous versions.

Use this string in the “Server name” field of the “Connect to Server” dialog of the Microsoft SQL Server Management Studio program:

\\\\.\pipe\\MICROSOFT##WID\\tsql\\query

SQL Script Permissions Option

If you would like to have Microsoft SQL Server Management Studio (2008R2) include a tables permissions when using the “Script table as>” feature go to menu “Tools -> Options -> SQL Server Object Explorer -> Scripting -> Script permissions – True”

Where are my TEMPDB files?

Use this query to find the physical location/path of the SQL TEMPDB files:

SELECT name as 'File Name', physical_name as 'File Directory'
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

SQL Slipstreaming

By Perry Whittle, 2010/12/27

How to: SQL Slipstreaming SP\CU During Setup

Most of us by now have encountered the issues with the RTM SQL Server 2008 installation media, the issues are more apparent during the installation of a clustered SQL Server instance. There are various ways of coping with the installation issues, but it is far easier to update the SP\CU files during the setup process.

SQL Server 2008 has a new feature called “Slipstreaming”, this basically enables you to integrate the Service Pack and Cumulative Update installation during the RTM SQL Server setup process. There are various prerequisites depending upon the Operating System in use, they are covered further on in this article.

So, how do we slipstream the Service Pack and the Cumulative Update?

The first operation is to obtain the relevant SP and if required the relevant CU. At the time of writing SP1 and SP2 are available for SQL Server 2008 (this does not apply to SQL Server 2008 R2). I prefer to copy my update executable(s) onto the server and then extract locally onto a folder on the C drive.

Note: Extracting and then launching the SP from the C drive forces the SP to use a temporary folder on that drive, this can be very useful in clustered environments as clustered drives may offline and online during installation (an issue many have encountered).

For this article I am going to slipstream my SQL Server 2008 installation with SP1 and CU7 for SP1. I have the following executables which i have copied to the server

  • en_sql_server_2008_sp1_x64.exe
  • SQLServer2008-KB979065-x64.exe

To extract the updates, use the following syntax noting my chosen paths

  • C:\en_sql_server_2008_sp1_x64.exe /X:C:\SP1
  • C:\SQLServer2008-KB979065-x64.exe /X:C:\CU7

Two folders will be created on the C drive containing the update files. If you are using Windows 2003 server you must first install the following prerequisites. If the SQL Server 2008 DVD autoruns, cancel this and proceed manually.

  • Filestream Hotfix KB937444 (download from MS site)
  • Windows Installer update (this is on the SQL Server 2008 install DVD)
  • .NET 3 Framework (this is on the SQL Server 2008 install DVD)

For a Windows 2008 server simply go into server features and enable the .NET 3.51 feature

Once the prerequisites are installed you need to launch the SQLSupport.msi inside the C:\CU7\x64\setup\1033 folder. With the support files installed, launch the SQL Server setup process using the following from a command prompt.

Note: my DVD drive is drive D:

D:\setup /CUSource=C:\CU7 /PCUSource=C:\SP1

The SQL Server Installation Center opens, you may now install your SQL Server instance leaving the installation center and the command prompt open in the background. The following screenshots from the installation process, indicate that a slipstream action is being performed,




Login to the new SQL Server instance and a quick version check reveals 10.0.2766, SQL Server 2008 SP1 CU7! You may also create a “Merged Drop” media, this comprises of the original RTM media with the update files overlaid creating an updated install media. More on this can be found at the following Microsoft link: http://support.microsoft.com/kb/955392

By Perry Whittle, 2010/12/27

Reduce SQL Log and TempDB File Sizes

Shrink the TempDB:
use tempdb
go
-- this command shrinks the primary data file
dbcc shrinkfile (tempdev, 'target size in MB')
go
-- this command shrinks the log file, look at the last paragraph.
dbcc shrinkfile (templog, 'target size in MB')
go

Shrink Log File:
DBCC SHRINKFILE ('Test_log',TRUNCATEONLY)
BACKUP LOG "Test DB Name" WITH TRUNCATE_ONLY

MS SQL Server Version

A select statement that returns the Version, Service Pack Level, and Edition information: (MS SQL 2000 and above)

SELECT @@VERSION as 'MS SQL', SERVERPROPERTY('productversion') as 'Version', SERVERPROPERTY ('productlevel') as 'Level', SERVERPROPERTY ('edition') as 'Edition'


For MS SQL 7.0 and earlier use:

SELECT @@VERSION


See Microsoft Knowledge Base KB321185 for more information.