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.

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

Enable Dynamics CRM Tracing with powershell

If you have some problems with Dynamics CRM On-premise you are able to enable tracing with PowerShell. In my case, I needed to get debug information on why my solution import is failing when I’m going to move it to a new organization.

Open the powershell prompt and use the Add-PSSnapin command shown in 1). Thereby, You can list the trace setting with the command shown in 2). Before you start the tracing, you show determine the timeline for when the error occurs and just enble it as close as the error as possible. Run the command in 3) to start the tracing. You should stop the tracing immediately after the error has occured. Use command in 4) to stop the tracing.

# 1) add
Add-PSSnapin Microsoft.Crm.PowerShell 

# 2) get crm trace settings
Get-CrmSetting TraceSettings 

# 3) enable tracing
$Setting = Get-CrmSetting TraceSettings
$Setting.Enabled = $True 
$Setting.CallStack=$True
$Setting.Categories="*:Verbose"
$Setting.Directory="C:\temp\crmtrace"
Set-CrmSetting $setting

# 4) disbale tracing
$Setting = Get-CrmSetting TraceSettings
$setting.Enabled = $False
Set-CrmSetting $setting

When you have tons of log file, the trace tool CRM Trace reader is nice to use for searing and filtering.

Powershell – download all videos from a RSS feed

In these vacation times there is always a risk for tech abstinence and low bad width. We discussed this at work yesterday, and start looking for ways to mass download videos from channel9. An off course, powershell is the solution to almost every problem 🙂

A collegues of mine found a nice post on the topic from by Scott Hanselmann. There were some errors in his script that I traced to a local path issue. I corrected the problem and modified it to a script with parameters for URL and local path where the videos should be stored.

Param(
    [Parameter(Mandatory=$true)]
    [string]$url,
    [Parameter(Mandatory=$true)]
    [string]$storageDir
)

If (!(Test-Path $storageDir))
{
    Write-Host "Path '" $storageDir "' does not exist";
    return ;
}

$feed=[xml](New-Object System.Net.WebClient).DownloadString($url)

Write-Host "Downloading from : '" $feed.rss.channel.title "' to folder " $storageDir

foreach($i in $feed.rss.channel.item) 
{
    $url = New-Object System.Uri($i.enclosure.url)
    $file = $storageDir + "\" + $url.Segments[-1]

    $started = Get-Date;
    Write-Host "Starting: " $started
    Write-Host "URL: " $url.ToString()
    Write-Host "Local file: " $file

    (New-Object System.Net.WebClient).DownloadFile($url, $file)

    $runtime = $(get-date) - $started
    $secs = $runtime.TotalSeconds.ToString("#0.0");
    $downloadtime = [string]::format("{0} sec(s)", $secs)

    Write-Host "Completed in " $downloadtime

    Write-Host "--------------------"
}

Suggested “watch list”:

Happy vacation everybody…

 

 

Prices for Azure Virtual Network, Web and SQL VMs

My last post on virtual networks and virtual machines had a good response, and here is the follow-up post with more prices and recommended setup for different.

There are two types of virtual machines in Windows Azure – Basic and Standard. The “Basic” virtual machines sizes don’t have the possibility to auto-scaling or load balancing. In addition, the “Basic” sizes are restricted up to 4 cores and 14GB memory.

Most of the cost for a virual machine is connected to licences for operating system and other Microsoft software such as SQL Server, Biztalk and Sharepoint. In this post I will focus on SQL Server since this is my primary working area 🙂

The prices for Windows Azure virtual machines are located in the “Price Page“.

The following basic virtual machine types and size are are available. The column “Price/month” is the licencing cost for the Window Server 2012 R2, and the column “Web” is the cost for the SQL Server Web Edition, and the “Standard” column is the price for SQL Server Standard Edition for the given virtual machine size. All prices are given in USD.

General purpose (BASIC), prices from May 1st 2014 SQL Server
WinSrv 2012 R2 data center CPUs Memory Price/month Web Standard
A0 (Extra Small, 1Ghz) Shared 768mb 14
A1 (Small, 1.6GHz) 1 1.75gb 56 90 466
A2 (Medium, 1.6Ghz) 2 3.5gb 112 146 522
A3 (Large, 1.6Ghz) 4 7.0gb 224 258 634
A4 (Extra Large, 1.6Gz) 8 14.0gb 447 514 1.266

The “Standard” virtual machine sizes have the following specifications and prices:

General purpose (STANDARD), prices from May 1st 2014 SQL Server
WinSrv 2012 R2 data center CPUs Memory Price/month Web Standard Enterprise
A0 (Extra Small, 1Ghz) Shared 768mb 15
A1 (Small, 1.6GHz) 1 1.75gb 67 101 477
A2 (Medium, 1.6Ghz) 2 3.5gb 134 168 544
A3 (Large, 1.6Ghz) 4 7.0gb 268 302 678 1.831
A4 (Extra Large, 1.6Gz) 8 14.0gb 536 603 1.355 3.661
A5 (1.6Ghz) 2 14.0gb 224 258 634 1.787
A6 (1.6Ghz) 4 28.0gb 447 481 857 2.010
A7 (1.6Ghz) 8 56.0gb 893 960 1.712 4.018
A8 (2.6Ghz) 8 56gb 1.823
A9 (2.6Ghz) 16 112gb 3.646

I hope the prices are correctly converted into the table. Please check the price page on the azure portal if you suspect incorrect price. Orignally, I had these prices in “norwegian kroner”. It might be some errors in this post.

 

Azure VM Part 2 – Create a development network

…or create a small customer network, for that matter…

The last month is been dedicated to learning more about Windows Azure, and how to facilitate features like SQL databases, web sites, virtual network, DNS servers and virtual machines to be able to create my own optimized development environment.  I have been playing around with my work MSDN subscription and measured the cost for a small environment – with its own domain controller, web and SQL server.

Basic CPUs RAM Price/month
A0 (Extra Small) 1 768mb $15
A1 (Small) 1 1.75gb $55
A2 (Medium) 2 3.5gb $110

When creating such an environment in Windows Azure, you should do the following:

  1. Create Affinity Group
    1. Select the datacenter nearest your location. Mine is North Europe.
  2. Create Storage Account
    1. Select affinity group from (1) as Region/Affinity Group.
    2. Also choose Geo-Redundat as replication
  3. Register DNS server
    1. Set internal IP address to 10.0.0.4
  4. Define Virtual Network.
    1. Remember to select affinity group from (1) and DNS server from (3)
    2. Cost is about $50/month
  5. Create VM for AD
    1. Select “basic” tier
    2. It’s enough with a size on “A0 (shared core, 768MB memory)” for a development environment ($15/month), opposite to $55/month for A1. This can be changed in 5 minutes (including reboot).
    3. Set local administrator username and password
    4. Write the global name for the AD machine
    5. Select the virtual network defined in (4)
    6. Select the storage account defined in (2)
    7. Validate IP for AD (should be 10.0.04)
    8. install the AD domain services role and promote VM to a domain controller
    9. Create administrator and service accounts
  6. Create VM for SQL Server
    1. Create a new VM from gallery (SQL Server 2014 Web Edition)
    2. Select “Basic” tier. You don’t need load balances and auto-scaled development environment
    3. Select Basic-A1 size (easily changed to Basic-A2 og Standard-A5 for that matter)
    4. Select administrator username and password
    5. Select the virtual network defined in (4)
    6. Select the storage account defined in (2)
    7. Create a 1433 (standard MS SQL port) endpoint
    8. Join the server in created domain from (5.8)
    9. Add SQL administrator with sysadmin
    10. Create an inbound rule in the firewall for SQL Server
    11. Configure SQL Server “Max memory” (70-80% of total memory)
    12. If production server, you should add two disks for data and log formatted with 64k block size.
  7. Cerate VM for Web server
    1. Create a new VM from gallery (Windows Server 2012 R2 Datacenter)
    2. Select Basic-A0 or Basic-A1 size
    3. Same as 6 d-f
    4. Create http and https endpoints
    5. Join the server in created domain from (5.8)
    6. Install IIS web role and install software for web deploy

Powershell commands for (5.8) – install AD and promote as domain controller:

Set-DnsClient –InterfaceAlias "Ethernet*" –ConnectionSpecificSuffix contoso.com 
Install-WindowsFeature AD-Domain-Services –IncludeManagementTools 
Install-ADDSForest –DomainName contoso.com

Powershell command for joining a computer to domain (as in 6.8 and 7.5):

Set-DnsClient –InterfaceAlias "Ethernet*" ` –ConnectionSpecificSuffix contoso.com 
Add-Computer –Domain contoso.com 
Restart-Computer

The cost for this setup is about $130-$250, depending on server configuration you need:

  • Small development
    • AD + VN = Basic-A0 + $50
    • Web = Basic-A0
    • SQL = Basic-A1
    • Cost = $50 + 2*A0 + 1*A1 = $50 + $30 + $55 = $135/month
  • Small production
    • AD + VN = Basic-A0 + $50 = $65
    • Web = Basic-A1 = $55
    • SQL = Basic-A2 = $110
    • Cost = $65 +$55 + $110 =  $230/month

Now you should be ready for coding…