Create SQL service accounts fast

The past few months I had a few reinstalls of my local development environment due to hardware failure and a new work laptop. And I have installed a local SQL Server instance at least 4-5 times. Luckily, I created a silent install for SQL Server 2016 earlier this year, but I didn’t create a script for pre-install tasks, like creating SQL Server service accounts. Today, I took the time to create the script for creating these quickly.

In my local development environment, I normally install database engine, SSIS and agent. It is not completely necessary with these accounts locally, but it is a good practice. Change the password “XXX” to your own, and run the script from command-line.

NET USER svc-sql-db "XXX" /ADD /passwordchg:no /fullname:"SQL Engine service account"
NET LOCALGROUP "Administrators" "svc-sql-db" /add

NET USER svc-sql-ag "XXX" /ADD /passwordchg:no /fullname:"SQL Agent service account"
NET LOCALGROUP "Administrators" "svc-sql-ag" /add

NET USER svc-sql-is "XXX" /ADD /passwordchg:no /fullname:"SQL Integration service account"
NET LOCALGROUP "Administrators" "svc-sql-is" /add

The only thing I need to improve is to fix the “Password never expires” check box. NET USER have a “/expires:never» switch, but doesn’t seem to work. This could be written in Powershell, but found these command very easy.

Advertisements

SQL – find where column is used as foreign key

The T-SQL script  below will find all tables and columns for a particular Primary Key column (located in WHERE clause [pk-table].[pk-column]). This script is pretty useful when you are working close the database, manipulating data directly and so on.

SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
		ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
		ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
		ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
	INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = '[pk-table]'
  AND PT.COLUMN_NAME = '[pk-column]'

Create team project from command-line with TFS Power Tools

Our department had an interesting challenge the last week. We have an old local on-premise “Team Foundation Server” (TFS) in our data room with two VMware hosts containing a number virtual machines. Due to new company policies we needed to move all domain bound VMs to a new domain. As we feared, this cause a few problems due to old versions and incorrect editions of different software.

The first thing I did was to perform a “Get Latest” on all source code just, in addition to a VM snapshot before we started the actual migration process. We needed to have a “Plan B” if the migration failed. After a few days with migration failures with loads of issues between Sharepoint, Project Server, SQL Server and TFS, we decided to make a clean install and move the source code into new team projects. The problem now was that the old TFS server had about 60 team projects that need to be created manually.

As a lazy programmer, I prefer a command-line utility to help me with this project creation. Luckily, 99% of all team projects didn’t use the Sharepoint site, so for the moment I just have to migrate source code to the version control of the new TFS server.

The command-line tool need for the team project creation is called “TFS Power Tools”, and exists in the latest version of Visual Studio – 2012 and 2013.  Here is the command template I have used for our team projects.

tfpt createteamproject 
	/collection:"http://[IP or Hostname]:8080/tfs/DefaultCollection" 
	/teamproject:"[project name]" 
	/processtemplate:"Microsoft Visual Studio Scrum 2.2" 
	/sourcecontrol:New 
	/noreports 
	/noportal

Since I found the list of team project directories by using “dir /b” from the DOS-prompt and put this directory list into Excel and generated one command for each project based on the command-line above. All these command where put into a command file (cmd) and run. When this is completed I will add all files from the projects from the “Source Control Explorer” in Visual Studio. Some manual work is needed.

Happy migration!

 

T-SQL String2Date function

I have a private project going on where I’m in the data cleaning and import phase. This weekend’s problem has been a lot of date columns stored as string on 2 different formats (yyyymmdd and ddmmyyyy) and different seperator characters (‘-‘ and ‘.’) all over the place. And these strings I want in date datatype. I’m a lazy programmer and had to make string2date function to use for my import stuff. Here is my first version of the function.

CREATE FUNCTION [dbo].[String2Date]
(
	@string NVARCHAR(100),
	@style smallint = 104
)
RETURNS date
AS
BEGIN
	IF (CHARINDEX('-', @string, 1) = 5 OR CHARINDEX('.', @string, 1) = 5)
	BEGIN
		SET @style = 102 ;
	END;

	RETURN CONVERT(date, @string, @style) ;
END

This function is not perfect, but suits my purpose for the moment. It would probably give some exceptions now and then, but I don’t care 🙂

Deleting duplicate rows in SQL Server

Today I found a pretty nice way to delete duplicate rows in a table on SQL Server. I had a table with 25,000 rows where 7,500 rows where rows containing one or more duplicates. I was not very eager to manually delete these duplicate, so I started to googling for answers. I found many different approaches, but suddenly I found my answer at stackoverflow.com. This thread help me rewriting a simple SQL statement after I added an [Id] column to uniquely identify a row. The clue is to use Common Table Expression (CTE) in SQL Server together with the OVER() function to create an unique row number for all duplicates within the key expression (in my case [Name] column), and ordering by the [Id] column. I only want to keep the first row for each duplicate item. Therefore, deleting all [rowno] greater than 1.

WITH cte_duplicates AS 
(
	SELECT [Name], 
		row_number() OVER
		(
			PARTITION BY [Name] ORDER BY [Id]
		) AS [rowno]
	FROM [dbo].[fm2011]
)
DELETE FROM cte_duplicates WHERE [rowno] > 1

When is SQL Server restarted?

It has been a while since last post now due to summer vacation and busy working hours

In my post on simple database activity monitoring, I described a method by calculating periodical values for read  write operations for databases. These values are calculated based on DMVs (Dynamic Management Views) that stored performance counters. The problem is that most of these DMVs is reset when the SQL Service or the server isself is restarted.

Method 1

SELECT login_time FROM sysprocesses WHERE spid = 1

 Method 2

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

 Method 3

SELECT [crdate] AS [LastStartupDate]
FROM [dbo].[sysdatabases]
WHERE [name] ='tempdb'
AND [crdate] < GETDATE()

GetDatabaseVersion – Stringify Compatibility Level

Are you working on lots of databases with different compatibility level and different SQL Server versions? I have this problem almost every day. I have to make my code stable and robust enough to work on different SQL Server versions and on the code must use functionality on databases with different compatibility levels.

I have to check for the database compatibility level before configuring the different performance counter and custom features for my database activity monitor, described in my previous post. I was pretty baffled when I discovered this logical error last week.

As a result, I created a helper function used in my day-to-day work to avoid mixing different compatibility levels.

CREATE FUNCTION [dbo].[GetDatabaseVersion]
(
	@dbname NVARCHAR(100) 
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @ver NVARCHAR(5) ;
	DECLARE @returnvalue NVARCHAR(100) ;

	SELECT @ver = compatibility_level
	FROM sys.databases WHERE name = @dbname ;

        SET @returnvalue = 
            CASE 
               WHEN @ver = '80' THEN 'SQL Server 2000'
               WHEN @ver = '90' THEN 'SQL Server 2005'
               WHEN @ver = '100' THEN 'SQL Server 2008 (R2)'
               WHEN @ver = '110' THEN 'SQL Server 2012' 
               WHEN @ver = '120' THEN 'SQL Server 2014'
            END;

	RETURN @returnvalue
END