Undocumented SQL Server 2012 Express LocalDB

As a developer of Microsoft Visual Studio, SQL Server 2012 Express LocalDB probably has gotten onto your machine without your notice. I will document some of my exploration on LocalDB in this post.

Installation and Location

There are three ways to get LocalDB onto your machines:

  1. Install together when installing Microsoft Visual Studio 2013 (this is my case);
  2. Install by using SqlLocalDB.msi found in SQL Server 2012 Express
  3. Install by downloading from Microsoft Download Centre directly (here).

The installation location is default to C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn where sqlserv.exe is the main application.

The tools (utilities) to operate the LocalDB are SqlLocalDB, SQLCMD and bcp which are located at C:\Program Files\Microsoft SQL Server\110\Tools\Binn. Make sure to include it into your PATH.

SqlLocalDB

This is the utility to administrate the localdb instances.

  • to get help: sqllocaldb -?
  • to print the version: sqllocaldb versions
  • to create an instance: sqllocaldb create “YourInstanceName”
  • to delete an instance: sqllocaldb delete “YourInstanceName”
  • to start an instance: sqllocaldb start “YourInstanceName”
  • to stop an instance: sqllocaldb stop “YourInstanceName”
  • to share an instance: sqllocaldb share “YourInstanceName”
  • to unshared an instance: sqllocadbl unshare “YourInstanceName”
  • to list all your instances: sqllocaldb info
  • to list the status of an instance: sqllocaldb info “YourInstanceName”
  • to set the trace on and off: sqllocaldb trace on|off

If you’ve used VS 2013 to connect to LocalDB, VS 2013 would have created an instance for you (in my case is v11.0).

Even your instance is stopped, it will be auto-started when you try to connect to it first time either via VS 2013 or SQLCMD.

SQLCMD, BCP, process ‘sqlservr’

Both SQLCMD and BCP are well documented. The only difference between LocalDB and SQL server is that we need to put a bracket ‘()’ to indicate it is a LocalDB instead of hostname for the named instance like:

sqlcmd -S ‘(LocalDB)\v11.0’

This is also applied to SSMS and VS 2013 connections. There is only one process “sqlservr” related to LocalDB. It is very lightweighted by using about 12MB RAM on my machine.

Some Examples

The following is executed in powershell:

Windows PowerShell
Copyright (C) 2013 Microsoft Corporation. All rights reserved.

PS C:\Users\henry> sqllocaldb info
Projects
v11.0
PS C:\Users\henry> sqllocaldb info "v11.0"
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              PolarBear\henry
Auto-create:        Yes
State:              Stopped
Last start time:    12/31/2013 2:37:39 PM
Instance pipe name:
PS C:\Users\henry> sqllocaldb start "v11.0"
LocalDB instance "v11.0" started.
PS C:\Users\henry>  ps | where-object {$_.ProcessName -match 'sqlservr'}

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    492      20    67780      17140   311     3.83   2248 sqlservr


PS C:\Users\henry> sqllocaldb stop "v11.0"
LocalDB instance "v11.0" stopped.
PS C:\Users\henry> sqlcmd -S "(LocalDB)\v11.0"
1> use test
2> go
Changed database context to 'test'.
1> select count(*) from HR.Employees
2> go

-----------
          9

(1 rows affected)
1> shutdown
2> go
Server shut down by request from login PolarBear\henry.
1> exit
PS C:\Users\henry>

Sharing or Not

From A TechNet Article
When sharing a SqlLocalDB instance with a non-owner, you must re-start the instance for the other users to be able to see the instance you have shared. A non-owner cannot start an instance, so if you are going to share an instance with other users who can access your machine, you also need to be sure it has been started. When you create an instance you can do this as follows:

sqllocaldb create “MyInstance”
sqllocaldb share “MyInstance” “OurInstance”
sqllocaldb start “MyInstance”

You should add users explicitly when connected to the instance as the owner, e.g.

CREATE LOGIN [Domain\User] FROM WINDOWS;
GRANT CONNECT TO [Domain\User];
— other permissions…

In general, though, the purpose of SqlLocalDB is to serve as a sandbox for an individual developer on a machine, not to serve as a development environment for multiple users. Each user should be able to create, start, administer and use his/her own LocalDB instances.

Virtualbox: How to sync time VM client in Windows

I am running a few Virtual Machines on my PC by using Oracle Virtualbox. There is always a need to sync the time on VM clients. The following commands are used in my Windows 8 VM client to sync to the time server in Canada by ‘run as Administrator’:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>net stop w32time
The Windows Time service is stopping.
The Windows Time service was stopped successfully.

C:\Windows\system32>w32tm /debug /disable

C:\Windows\system32>w32tm /unregister
W32Time successfully unregistered.

C:\Windows\system32>w32tm /register
W32Time successfully registered.

C:\Windows\system32>net start w32time
The Windows Time service is starting.
The Windows Time service was started successfully.

C:\Windows\system32>w32tm /config /manualpeerlist:time.nrc.ca,0x8, /syncfromflags:manual /update
The command completed successfully.

C:\Windows\system32>w32tm /resync
Sending resync command to local computer
The command completed successfully.

Reading XML with the XmlReader in Powershell

The XmlReader class in .Net Framework reads XML data from a stream or file. It provides non-cached, forward-only, read-only access to XML data. I wrote a simple program in powershell to explore XmlReader class as belows. Have fun!

A sample XML input file: books.xml

<?xml version="1.0"?>
<books>
 <book> 
   <author>Carson</author>
   <price>31.95</price>
   <pubdate>05/01/2001</pubdate>
 </book>
 <pubinfo>
   <publisher>MSPress</publisher>
   <state>WA</state>
 </pubinfo>
</books>

A powershell script: xmlreader1.ps1

##  
# xmlreader1.ps1  
## 
$PWD=Get-Location
$xmlfile=$PWD.Path+"\books.xml"

# Create XML Reader  
$reader = [system.Xml.XmlReader]::Create($xmlfile) 

# Parse the XML document.  
$result=$reader.Read()  
$reader.ReadStartElement("books")

# start book node
"The following is from book:"
$reader.ReadStartElement("book")

# Read and Display auther  
$reader.ReadStartElement("author")     
"author: {0}" -f $reader.ReadString()  
$reader.ReadEndElement()  

# Read and display price  
$reader.ReadStartElement("price")  
"price: {0}" -f $reader.ReadString()  
$reader.ReadEndElement() 
# Read and display pubdate
$reader.ReadStartElement("pubdate")  
"pubdate: {0}" -f $reader.ReadString()  
$reader.ReadEndElement()

# end book node
""
$reader.ReadEndElement()

# start pubinfo node
"The following is from pubinfo:"
$reader.ReadStartElement("pubinfo")

# Read and Display publisher  
$reader.ReadStartElement("publisher")     
"publisher: {0}" -f $reader.ReadString()  
$reader.ReadEndElement()  

# Read and display state  
$reader.ReadStartElement("state")  
"state: {0}" -f 
$reader.ReadString()  

$reader.ReadEndElement() 
# end pubinfo node

$reader.ReadEndElement()
# End of Script 

Execution Result

PS C:\henry416\> .\xmlreader1.ps1

The following is from book:
author: Carson
price: 31.95
pubdate: 05/01/2001

The following is from pubinfo:
publisher: MSPress
state: WA

Delete older files using Powershell?

One of thing I like linux (unix) is the scripting. The one I like most is to remove files older than 10 days:

find /yourpath/yourfiles* -type f -mtime +10 -exec rm '{}' \;

But I  need to do this in Windows Powershell, and how? Here is the answer:

$Now=get-date

$LastWrite = $Now.AddDays(-10)

Get-ChildItem \yourpath\yourfile* |Where {$_.LastWriteTime -le "$LastWrite"}|remove-item -recurse

Resource Usage in Powershell Script

To get all the detail from processors

Get-WmiObject win32_processor

To shows the CPU load in every 5 sec

while ($true) {
    Start-Sleep -s 5
    cls
    ‘CPU Load is’
    Get-WmiObject win32_processor | select LoadPercentage  |fl
}

To list all the processes (like ps in Linux)

get-process
will produce the following preocess list...
Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    146       7     4992       1844    52     0.92    268 ACLIENT
     59       8     1188       2176    31     0.11   5948 AClntUsr
    136       5     4496       7132    49     4.55   5412 acrotray
    139       3     1728       3540    33     0.30   4192 AeXAgentUIHost
   1792     143    54748      11296   270   570.22    924 AeXNSAgent
    110       5     1364       1784    33            2388 alg
    257       6    15080      13104    89   124.14   1744 ArelliaACSvc
     31       2      488        764    15     0.06    956 CCSRVC
    113       4     1636       2928    42     0.08   2276 Client
     33       2     2160       2996    30     0.02   3588 cmd
     61       3     1384       1432    25     0.05   1952 CNTAoSMgr
    949      23    35120      31764   200    11.52   4216 communicator
    123       4     1532       2540    41     0.03   5132 concentr
   1102       8     3712       9140    74            2036 csrss
     79       4     1108       2364    31     0.06   5208 ctfmon
    675    5719    27204      26284   137   245.34   3212 explorer
     47       2     1572       2368    19     2.42   4244 FNPLicensingService
     91       3     1176       1840    33     0.06   3512 hkcmd
     29       2      576       1204    16     0.03   2332 hpmup094.bin
     90       5     4476       3504    52     0.03   6080 hqtray

get-process | where-object {$_.WorkingSet -gt 10000000}
to get those process name with higher working set.
stop-process –name processname
can be used to stop (kill) those processes

Some references

PowershellPro

The Powershell Guy

The Po$herLife