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

Delete your old logfiles

In most cases where you have developed a on-premise applikasjon (console, service) these generates logfiles. Either for  debugging purpose, or for validating the day-to-day execution wether there are errors or warning.

I tend to use text files, where I keep them for at least 14 days. The following command will delete all files older than 14 days from the c:\temp\logging folder. I have this at the top of my startup.cmd script.

rem delete files older than 14 days
forfiles /p "C:\temp\Logging" /m "*.*" /c "cmd /c del @path" /D -14

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

Blog milestone – 25k

This blog had a nice, little milestone this week – 25K hits since October 2012. This is an average of 1200 hits/month since I first started blogging regularly with at least 4-5 blog posts each month.

This blog is still used as my personal “notebook” where collect tricks and tips on things I’m working on day-to-day basis. I hope you all enjoy reading, and I will try my hardest to make the blog post as interesting for everyone else 🙂

I will still write posts on Microsoft Technologies like Windows Server, Azure, SQL Server and other things I find interesting.

 

Start & stop perfmon collector set from powershell

I have been working quite much with perfmon lately, and have just started to explore Powershell the last few weeks. I just loving this language since this is pretty much similar to old school unix programming as we used at the University back in the early 90’s. Powershell offers a rich set of commands and libraries for accessing Windows server components.

In my case, I needed some commands to start and stop data collector sets in “Performance Monitor”. This code will use COM object Pla.DataCollectorSet to start and stop the collector set.

The first code piece will start the perfmon data collector set “SQLServer”.

$datacollectorset = New-Object -COM Pla.DataCollectorSet;
$datacollectorset.Query("SQLServer", $null);
$datacollectorset.Start($false);

…while the second code piece will stop the same data collector set.

$datacollectorset = New-Object -COM Pla.DataCollectorSet;
$datacollectorset.Query("SQLServer", $null);
$datacollectorset.Stop($false);

 

The RPC server is unavailable (Windows Mobile 6.X)

Today, I encountered yet another problem that required some thinking and googling. I’m bug fixing and extending an old Windows Mobile 6.0 application and needed to deploy directly to device from Visual Studio 2008. It worked yesterday, but today it did not work. Google didn’t came up with the immediate answer, so I tried to change the target framework from 6.0 to 6.5.3, but still not working.

I checked the “Services” and restarted the RPC services, but still not working. I rebooted my laptop and discovered a new services “RPC Endpoint Mapper” that was running on my machine, and suddenly the device deployment worked fine after connecting with ActiveSync

*happy*