SQL Express Migration Auto Close Setting

With over 700 databases to look after at MyWork automation is high on my list of priorities. I have two PowerShell scripts which run regularly checking SQL Error logs. One checks for the output from DBCC CHECKDB and one for errors. They then email the results to the DBA team.

This week we noticed that a new database was creating a lot of entries. It appeared to be starting up every few minutes. A bit of investigation by my colleague revealed that this database had been created on SQL Express and migrated to SQL Server.

SQL Express sets AUTO_CLOSE to on by default and this is what was creating the entries.

What does the AUTO_CLOSE setting do?

According to BoL Link

DescriptionDefault value
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

 

When set to OFF, the database remains open after the last user exits.

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

That explains what was happening, the database was shutting down as the session finished and then starting back up again when the next one started. Repeatedly. Filling up the log files with entries, resetting the DMVs and using resources unnecessarily.

To find databases with this setting on query the master.sys.databases for the is_auto_close_on column Link or check the properties page in SSMS

image

You can change the setting there or with T-SQL

Of course I like to do it with PowerShell!!

To find the databases with AUTO_CLOSE setting on

To change the setting with PowerShell

$svrs = ## list of servers Get-Content from text fiel etc

foreach ($svr in $svrs) {
    $server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
    foreach ($db in $server.Databases) {
        if ($db.AutoClose = $true) {
            Write-Output "$Server - $($DB.Name) AutoClose ON"
        }        
    }
    
}

$Svr = 'SERVERNAME'
$DB = 'DatabaseName'
$server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
$db.AutoClose = $false
$db.Alter()

Checking for SQL Server logins with PowerShell

As some of you may know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which database does this account have access to?

This question can come from Support Desks when they are investigating a users issue, Developers when they are testing an application as well as audit activities. It is usually followed by what permissions do they have which is covered by my next blog post.

I start by getting the list of servers from my text file and creating an array of logins for each domain as I work in a multi domain environment

image

Then loop through each server and if the login exists write it out to the window.

image

I then repeat this but loop through each database as well

image

A little bit of formatting is added and then a quick easy report that can easily be copied to an email as required.

To call it simply load the function

image

and get the results

image

The code is below

<#
.Synopsis
   A workflow to display users server and database logins across a SQL estate
.DESCRIPTION
    Display a list of server login and database user and login for SQL servers listed 
 in sqlservers.txt file from a range of domains
    AUTHOR: Rob Sewell http://sqldbawithabeard.com
    LAST UPDATE: DATE:07/01/2015
.EXAMPLE
   Show-SQLUserLogins DBAwithaBeard

   Shows the SQL Server logins and database users matching DOMAIN1\DBAWithaBeard,DOMAIN2\DBAWithaBeard, DBAWithaBeard
#>

Workflow Show-UserLogins
{
    
param ([string]$usr)
$servers = Get-Content '\\hdat01\ICTS\ICTS\DBA-SAP\sql\Powershell Scripts\sqlservers.txt'
$ErrorActionPreference = "SilentlyContinue"

# Create an array for the username and each domain slash username

$logins = @("DOMAIN1\$usr","DOMAIN2\$usr", "DOMAIN3\$usr" ,"$usr" )

Write-Output "#################################" 
Write-Output "SQL Servers, Databases and Logins for `n$logins displayed below " 
Write-Output "################################# `n" 

#loop through each server and each database and display usernames, servers and databases
Write-Output " Server Logins`n"

foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {              
            foreach ($login in $Using:logins)
                {      
                if($srv.Logins.Contains($login))
                    {
                    Write-Output " $Using:server -- $login " 
                    }           
                else
                    {
                    continue
                    }
                } 
            }         
        }
    }
Write-Output "`n###########################"
Write-Output "`n Database Logins`n"
foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {                                                                
            foreach($database in $srv.Databases|Where-Object{$_.IsAccessible -eq $True})
                {
                foreach($login in $Using:logins)
                    {
                    if($database.Users.Contains($login))
                        {
                        Write-Output " $Using:server -- $database -- $login " 
                        }
                    else
                        {
                        }   
                    }
                }
            }
        }
    }    
Write-Output "`n#########################################"
Write-Output "Finished - If there are no logins displayed above then no logins were found!"    
Write-Output "#########################################"  
}

 

SQL Server 2014 CTP on Azure. Powershell won’t create XTP tables

Having a couple of hours to play I created a SQL 2014 box on Azure with Windows Server 2012 R1 and followed This Post on SQLServerCentral  to create a File Group and table with T-SQL. Everything went well. Don’t try and provision an extra small box with that image though as it is so slow as to be unusable!!

I then decided to take a look at doing it with Powershell and I cannot create the table. I think it is to do with my Index creation. Can anyone see my mistake as I am going round in circles now. I have posted to DBAStackExchange so answer there

    -- Create new database
CREATE DATABASE TestDatabase
GO
--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE TestDatabase
ADD FILEGROUP XTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
NAME = N'HekatonFile1',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HekatonFile1')
TO FILEGROUP [HekatonFileGroup]
GO
-- Let's create a new Memory Optimized Table
CREATE TABLE TestTable
(
 Col1 INT NOT NULL,
Col2 VARCHAR(100) NOT NULL,
 Col3 VARCHAR(100) NOT NULL
CONSTRAINT chk_PrimaryKey PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON)
GO

And Here is my Powershell

# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)  | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’)  | out-null
$server= new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$dbname = "HekatonTest"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $dbname)
# Add FileGroups
$FG1Name = "PRIMARY"
$Normalfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG1Name)
$db.FileGroups.Add($Normalfg)
$FG2Name = "MemOpt"
$MemOptFG = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG2Name)
$MemOptFG.FileGroupType = "MemoryOptimizedDataFileGroup"
$db.FileGroups.Add($MemOptFG)
#Create datafiles
$normallogname = "HekatonTest_Data"
$dbdfnormal = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($Normalfg, $normallogname)
$dbnormalfile= $server.Information.MasterDBPath + '\' + $normallogname + '.mdf'
$normalfg.Files.Add($dbdfnormal)
$dbdfnormal.FileName = $dbnormalfile
$dbdfnormal.Size = [double](5.0 * 1024.0)
$dbdfnormal.GrowthType = 'Percent'
$dbdfnormal.Growth = 25.0
$dbdfnormal.IsPrimaryFile = 'True'
$MemOptFilename = "MemOpt_Data"
$MemOptDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($MemOptFG, $MemOptFilename)
$MemOptDataFilePath= $server.Information.MasterDBPath + '\' + $MemOptFilename + '.ndf'
$MemOptFG.Files.Add($MemOptDataFile)
$MemOptDataFile.FileName = $MemOptDataFilePath

#Create Database
$db.Create() 
#Create Table
$dbname = "HekatonTest"
$db = $server.databases[$dbname]
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "MemOptTable")
#Add Columns
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"Col1", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col2", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$col3 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col3", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$tb.Columns.Add($col1)
$tb.Columns.Add($col2)
$tb.Columns.Add($col3)
$C1Name =$col1.Name
#Create Index
$IX = New-Object Microsoft.SqlServer.Management.Smo.Index ($Tb, "PK_PrimaryKeyName")   
$IX_col = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn ($IX,$C1Name)
$IX.IndexedColumns.Add($IX_col)
$IX.IndexKeyType = "DriPrimaryKey"
$IX.IndexType = "HashIndex"
$IX.BucketCount = "1024"
$IX.FileGroup = "PRIMARY"
$IX.IsMemoryOptimized = $true
$Tb.Indexes.Add($IX)
$tb.FileGroup = "MemOpt"
$tb.Durability = "SchemaAndData"
$tb.IsMemoryOptimized = $true
$tb.Create()

I can create the filegroups and the database with PS and use SSMS to create Memory Optimised Tables (XTP) and if I take out the index creation then the table creates but isnt XTP obviously.

All you get is the frustrating Create() failed error which doesn’t help me to find the problem.

I know I can take the T-SQL and put it into Invoke-SQLCMD but I figured you ought to be able to do it with Powershell.

Can anyone point me in the right direction? For interest only, maybe it’s not available in the CTP. Answer over here