Dinsdag 4 oktober was het weer SQL PASS tijd. Ditmaal waren we weer te gast bij IVENT en niet zoals eerder in Amsterdam maar op voormalig vliegbasis Soesterberg. De officiersmess was ook de perfecte setting voor deze avond.

Ik zag bekende maar ook nieuwe gezichten, zeker de nieuwe gezichten maken de avond al geslaagd. Fijn om te zien dat steeds meer mensen de PASS avonden weten te vinden. Helaas was er ook één gezicht niet, André Kamman. Wegens ziekte verhinderd (sterkte André). Omdat André het eerste deel van de avond voor zijn rekening zou nemen moesten we op zoek naar een alternatief. Toevallig had Jasper Hengefeld, Sr. Ontwerper bij IVENT de week daarvoor een presentatie gegeven aan de ontwikkelaars bij IVENT over de wijze waarop zij beheer uitvoeren en waarom bepaalde keuzes zijn gemaakt. Na overleg met Jasper hebben we besloten om die presentatie te laten zien en deze te gebruiken om een discussie op gang te brengen over de keuzes die gemaakt zijn. Er zijn leuke en interessante discussies zijn gevoerd. Zeker gezien de korte termijn waarop alles geregeld moest worden wil ik Jasper bedanken voor zijn inzet en het regelen van de lokatie.

Na de koffie was het dan Denali tijd en specifiek security in Denali. Er zijn heel wat features bijgekomen en zeker met betrekking tot Auditing. Maar ook Contained Databases, User-Defined Server Roles en Default Schema for Groups zijn een welkome aanvulling op de toch al uitgebreide featureset.

Mocht je nog vragen hebben of de presentatie van Jasper willen hebben neem gerust contact op.

Tot slot hier nog wat links:

Voor degene die speciaal voor het verhaal van André waren gekomen staat hier de meeste informatie:

• SQL Server Seperation of Duties Framework op Codeplex: http://sqlserversod.codeplex.com/
• SQL Server Seperation of Duties Whitepaper: http://sqlblog.com/blogs/lara_rubbelke/archive/2011/05/06/sql-server-separation-of-duties-whitepaper-released.aspx
• SQL Server Label Security Toolkit: http://sqlserverlst.codeplex.com/

Voor het Security in Denali gedeelte kun je hier op je gemak het hele verhaal nog eens bekijken, daar staat ook een link naar de slides: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI401

Als laatste een link naar de SQL Server Denali Productguide. Hier staat allerlei informatie over SQL Server Denali gebundeld en zijn er ook labs toegevoegd die je zelf kunt volgen. Het is even een download maar dan heb je wel alles bij elkaar.

http://www.microsoft.com/download/en/details.aspx?id=27069

Ik heb een fijne avond gehad, weer wat nieuwe inzichten opgedaan en hoop iedereen terug te zien bij SQL Zaterdag op 12 november!

Every now and then I get questions from DBA’s if I can tell why application Y needs sysadmin permissions. Or why it needs to be local admin on the SQL box, or why the database has to be named XYZ, or why no other databases are allowed on the instance, or why SQL 2000 is the only supported database.

These questions are all related to installation most of the time. In some cases you can remove sysadmin privileges after installation, you can add other databases and you can move the database to SQL 2008 R2. Sometimes I get questions that are different. More like; why does application Y want to do it’s own index maintenance or according to the manual we should have 50% free space in the database. Here is a list of these questions and my view on it.

• The application needs sysadmin (most of the times combined with securityadmin, backupadmin and other admin privileges): That’s a no brainer. And that means you too! As a DBA you don’t need to be sysadmin. CONTROL SERVER will give you pretty much the same powers and you can always elevate to a sysadmin account if needed. And if even you are not sysadmin why should anyone else be!
• The application need local administrator privileges: I’ve seen this happen where an application wanted to attach a database on the remote SQL box during installation. To do this it would first copy the files to the SQL directory. In order to do this it used the administrative shares (c$ etc.). Administrative shares can only be accessed by.. yes, Administrators. Every DBA should be able to attach a database. Use that!
• No other databases are allowed on the instance: There is no excuse for that. I’m a big fan of consolidation. By consolidating your SQL databases you have less instances to manage and less licenses. Why on earth do application check on this?
• The database has to be named XYZ: Ok. I’m not a real developer but how hard can it be to put the database name in a configuration file? I’ve seen applications with hardcoded database names or applications that would resolve the machine name to the IP-address. Just making sure the application works when DNS doesn’t. Until you change the IP-address of course.
• SQL 200x is the only supported version: Of course it is. Because you put it in your code: “If db-version not equal to 2000 FAIL application.”. The only purpose is too make sure you upgrade to the next version. Ok, that depends, but most of the times there is no need to stay on older versions.
• There is no need to defrag the indexes, the application is taking care of it: That’s a tricky one. I don’t like applications that check if indexes are dropped or created and change that back to their defaults. The need for an index can change over time or depending on the actual workload. For defragmentation of indexes it can be true that the application knows best when to do that. Rebuilding or reorganizing indexes will cause use resources. This can be locks, be it short or long, but it can be. Maybe there are tables that cannot be locked or have a severe performance impact when locked. This would be a situation where the application knows best. On the other hand, if the application is not performing and you can blame the fragmented indexes I’m sure the other admins will let you defrag the indexes. But always try to find out why decisions are made, don’t just follow the manual.
• Databases should not be set to Autogrow: There are manuals that just state that. No context whatsoever. And agreed, as a best practice you should never let your databases autogrow. You as a DBA should identify trends and grow database files ahead of time during off hours.But Autogrow can also be a last resort. What if a database is growing faster than expected? At least make sure that you are aware of auto growths and correct these if needed. Especially for database logfiles. Ideally these should never grow.
• The databases files need to have 50% free space: Ok. Why? 50% can be anything, 50MB, 50GB or even 50TB. If you haven an operation that incidentally needs more space, just calculate the space needed or show the calculation. Keeping 50GB free space is not efficient and will keep most SAN admins screaming everybody is taking up space and never using it. There is no unlimited disk space and it should be used wisely.

I’m not an Exchange person, so I will not tell an Exchange administrator how to configure Exchange. And if SQL would require some setting on Exchange I would validate this with an Exchange administrator. And if he’s not comfortable with the setting, neither am I.

Most of the times the DBA knows best. And a DBA has take care of more databases than just one. And they all have to be manageable with standardized procedures. A SharePoint database best practice or a System Center database best practice are not always a DBA best practice. It should be an open discussions where everyone should have a clear point of view of what is needed and why. “Because the manual says so” is not a valid reason!

The answer; “It depends.”. As always with SQL Server there is no right or wrong (unless it’s shrinking your database files, that’s wrong). There are best practices and these should be followed as much as possible. But if you know what you are doing you can take the road less travelled.

One of the new features of SQL Server 2012 is AlwaysOn Availability Groups. You can think of Availability Groups as Database Mirroring on steroids. Database mirroring is only limited to one database, one secondary and if you wanted to read off it you had to create a snapshot. And in the case of a failover the application has to reconnect to another machine.

AlwaysOn Availability Groups provide much more flexibility for high availability and disaster recovery. With AlwaysOn you can:

• Use multiple databases within an availability group. The group becomes the unit of failover and not a single database.
• Use up to four secondary's.
• Secondary’s can be readable without using a snapshot.
• Backups can be taken off secondary’s.
• A Virtual Network Name can be used to redirect clients.

Another great feature is the support for Windows server core editions and the growing adaption of PowerShell. In this post I will guide you through a gui-less installation of AlwaysOn availability groups. Let’s get started.

If you want to use availability groups you will need a Windows Failover Cluster. No need to run off. This is not the cluster you are used too. Besides, cluster installations have become much easier with Windows Server 2008 and SQL Server 2008. You can still cluster a SQL instance with SQL Server 2012, in fact it has much improved as well. What would you say about SMB support, local storage support for TempDB, multi-subnet clustering and better diagnostics and failover policy. But let’s not get sidetracked, we are doing availability groups.

To prove that SQL isn’t clustered I will install SQL first on all machines and configure Windows Failover Clustering later. All machines are Windows Server 2008 R2 Sp1 Core editions with all updates installed including .NET 4.0. Every machine has one disk, the OS disk. No shared cluster disks or iSCSI drives here. The machines have one NIC and are all bound to a local Contoso domain. I’m using one domain account for all installations and I also added the account to the SYSADMIN server role afterwards. Installation is easy, pop in the disk or whatever you are using and fire away:
setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER
/SQLSVCACCOUNT=CONTOSO\sqlsvc /SQLSVCPASSWORD=p@ssw0rd
/AGTSVCACCOUNT=CONTOSO\sqlsvc /AGTSVCPASSWORD=p@ssw0rd
/IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
/AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins
/TCPENABLED=1

This will install the SQL Server engine on the box, and just SQL Server. That’s all I need, but you are not limited to only installing the engine on core. Second thing is to open up the firewall; netsh advfirewall firewall add rule name="SQL Server Default TCP Port" dir=in action=allow protocol=tcp localport=1433 profile=domain. To prepare thing for the Availability Groups I will open port 5022 as well. This is the port I will use for the mirroring endpoint. I’m using this command; netsh advfirewall firewall add rule name="SQL Server HADR Endpoint" dir=in action=allow protocol=tcp localport=5022 profile=domain.

That’s all. I repeated this on all my four machines and checked the connection between all of them. Next step is getting Windows Failover Clustering working.

For all machines we need to enable Failover Clustering. For this we use the trusty DISM command line tool; DISM /online /enable-feature /featurename:FailoverCluster-Core.

Now we got everything setup to get the cluster going. Installing a cluster on core couldn’t be easier. On the shell type: cluster /cluster:cls-sql-01 /create /nodes:"SQL-02 SQL-03 SQL-04 SQL-05" /ipaddr:192.168.200.8/24 and you’re done. Wait for it to finish and you’ve got yourself a nice cluster, a very basic cluster, but it’s enough to get AlwaysOn going. And you can use the cluster.exe to make it as complicated as you like.

The next step is to enable AlwaysOn. Remember that if you destroy a cluster and rebuild it you also have to disable and enable AlwaysOn. Enabling AlwaysOn has to be done with PowerShell or the SQL Configuration Manager. In my case it will be PowerShell because I don’t have a GUI. There are two ways to do this, start SQLPS, the SQL Server PowerShell host or import the SQLPS module in PowerShell. I prefer the second method. So I first start PowerShell, then import SQLPS using Import-Module SqlPS. This will give a warning which you can ignore. To enable AlwaysOn we use one of the new SQL Server cmdlets, Enable-SqlHadrService. This function accepts a parameter to tell which instance you want to enable and optionally you can force it to restart the instance. I’m using Enable-SqlHadrService –serverinstance SQL-02 –force to enable AlwaysOn and force a restart. If you don’t supply –force you will be prompted to restart the instance. This is repeated for all instances.

Now we have to make a sidestep. In CTP3 we have to enable a traceflag to get all the functionality I wanted in my little demonstration. For this I once again restorted to PowerShell and used New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -Name "SQLArg3" -Value "-T9532" -PropertyType "String". Now this it limited to my setup and machine. The general idea is to add an extra value to the startup arguments. If this will work in your environment depends on your configuration. So, it is the registry you are dealing with, if you are not comfortable or don’t know what’s going on in this statement don’t do it.

Now the big moment has arrived. The following long line of PowerShell will setup the availability groups for me, and again for me. Tweak and alter it to satisfy your needs. This script is based on the one provided in Books Online (http://msdn.microsoft.com/en-us/library/gg492181(v=SQL.110).aspx).

 $Primary = "SQL-02" 
 $SecondaryList = "SQL-03","SQL-04","SQL-05" 
 $AgName = "AG_PS_DEMO" 
 $Database = "SAMPLE" 
 $BackupDirectory = "\\DC-01\temp\" 
 $ENDPOINTPORT = 5022 
 pushd
 
 # Backup databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Backup $db (and log) on $Primary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn" 
 
 Backup-SqlDatabase $db $fullbackupFile -ServerInstance $Primary 
 Backup-SqlDatabase $db $logBackupFile -ServerInstance $Primary -BackupAction "Log" 
 }
 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default\Endpoints\HadrEndpoint -State Started
 
 # Create availability replicas as templates 
 $replica1 = New-SqlAvailabilityReplica -Name $Primary -EndpointURL "TCP://SQL-02:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica2 = New-SqlAvailabilityReplica -Name $SecondaryList[0] -EndpointURL "TCP://SQL-03:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica3 = New-SqlAvailabilityReplica -Name $SecondaryList[1] -EndpointURL "TCP://SQL-04:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -Version 11 
 $replica4 = New-SqlAvailabilityReplica -Name $SecondaryList[2] -EndpointURL "TCP://SQL-05:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -ConnectionModeInSecondaryRole 'AllowAllConnections' -Version 11
 
 # Create AG on primary 
 cd SQLSERVER:\SQL\$Primary\default 
 Write-Host -ForegroundColor Green "Create availability group $AgName on $Primary" 
 New-SqlAvailabilityGroup $AgName -AvailabilityReplica ($replica1,$replica2,$replica3,$replica4) -Database $Database
 
 foreach($Secondary in $SecondaryList) 
 { 
 # Join AG on secondary 
 Write-Host -ForegroundColor Green "Join $Secondary to availability group $AgName" 
 cd SQLSERVER:\SQL\$Secondary\default 
 Join-SqlAvailabilityGroup -Name $AgName
 
 # Restore databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Restore $db (and log) on $Secondary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn"
 
 Restore-SqlDatabase $db $fullbackupFile -ServerInstance $Secondary -NoRecovery 
 Restore-SqlDatabase $db $logBackupFile -ServerInstance $Secondary -RestoreAction "Log" -NoRecovery
 
 trap { Write-Warning "Error while restoring, but continuing." ; continue } 
 }
 
 # Join databases on secondary 
 Write-Host -ForegroundColor Green "Join databases to availability group $AgName" 
 $availabilityGroup = get-item SQLSERVER:\SQL\$Secondary\DEFAULT\AvailabilityGroups\$AgName
 
 foreach($db in $Database) 
 { 
 Add-SqlAvailabilityDatabase -InputObject $availabilityGroup -Database $db 
 } 
 } 
 popd 
 
 

To finish it off a fancy screenshot of the hard work.

To get things going on Windows Server 2008 Core is not hard. Up to point where you setup the availability groups it’s much faster, easier and repeatable. Setting up the availability groups can be done from a remote machine using the wizard if you like. The script I demonstrated here worked on my setup, you have to change it to get things going on your side.