Tuesday, 21 March 2017

Enable SQL Server 2016 AlwaysOn Availability Groups Using Windows PowerShell

Always On Clusters are perhaps one of the best things in SQL Server but they take time to Setup unless you have it scripted out.
Here is one of the problems going to each node and enabling always on, now for default named instance this can be easy using the SQLPS and Enable-SqlwaysOn cmdlets.

Using the node name and cycling throw each node of the cluster we can enable always on without entering the names of the nodes one at a time.

foreach ($node in Get-ClusterNode) {Enable-SqlAlwaysOn -ServerInstance $node -Force}

Now that might work for default named instance but what if you have named instance on your server?  Well here is a just as easy way to deal with it using one extra trick from the SQLPS the SQLSERVER:\SQL directory.  Using this we can Get-ChildItem to retrieve the instance name.

foreach ($node in Get-ClusterNode) {$loop = Get-ChildItem SQLSERVER:\SQL\$node\
Enable-SqlAlwaysOn -ServerInstance $loop.name -Force}


This simple trick allows me to build an always on cluster with named instance just as easy as default named instance without even needing the instance names to run.

Saturday, 18 March 2017

XML Templating

Working with XML to do complicated things quickly is great, but creating the XML files can be a pain if you don't work with excel.


So here is how to do that much easier, first of all you need to create an xml file, and they you need to create at least two sets of code so that excel can see that the structure is consistent, once that is done you can import the file to excel to add the data.

So step one create an XML file.
Here is my example file,

<?xml version="1.0" encoding="utf-8"?>
<Computers>
    <Servers>
<Price>350</Price>
<Brand>Hewlet-Packard</Brand>
<Model>ML350</Model>
<Color>Silver</Color>
    </Servers>
    <Servers>
<Price>300</Price>
<Brand>Dell inc</Brand>
<Model>PowerEdge R730</Model>
<Color>Carbon Black</Color>
    </Servers>
    <Servers>
<Price>400</Price>
<Brand>IBM</Brand>
<Model>IBM Lenovo x3650</Model>
<Color>Carbon Black</Color>
    </Servers>
</Computers>

Once you create and save the template, using whatever editor you like, personally it was notepad for me, you'll need to open the data.xml in excel.
Be sure to change the extension type to XML or you won't find it.
Another option is to right-click on the file and choice open with excel.

Once you open it your going to get asked a few questions, first one being, how do you want to open this file, the answer you want is As an XML table.

Next up it will tell you is that the XML file source doesn't refer to a schema so it will create one based on the example in the file.


This is all fine and the file once open will have whatever data you entered into your example XML, however, only columns with data will show in excel so don't freak out if the other coding elements don't show as they are still there.


Last but not least when you save the file make sure you use the XML extension as excel by default will try to save it as an excel file.


And that's it, you can now create very complex data sheets using excel in minutes, without the effort you would have in coding them.

Tuesday, 14 March 2017

SQL how not to cursor

While many of us know we should not cursors we often do as quick loops, and this is not a good practice, so I'm going to show you a very quick example of how to create an SQL statement that will create a command list into a temp table and then run it without a cursor to loop them.

First here is how it might look using a cursor.


IF OBJECT_ID('tempdb..#query') IS NOT NULL
DROP TABLE #query;
CREATE TABLE #query
(
ID INT IDENTITY(1, 1) ,
query nvarchar(4000) ,
);

INSERT INTO #query
(query)
select
'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT;
ALTER DATABASE [' + name + '] SET MULTI_USER;'
from sys.databases where database_id > 4

DECLARE @Sql nvarchar(4000)
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
Select Query FROM #query -- table where sql is stored
OPEN Cur
FETCH NEXT FROM Cur INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM Cur INTO @Sql
END
CLOSE Cur
DEALLOCATE Cur;
DROP TABLE #query;


And now here is the same process without using a Cursor to achieve the same results.
Not only does it mean that you are not using a Cursor, but if you look closely it's even a few lines shorter.

IF OBJECT_ID('tempdb..#query') IS NOT NULL
DROP TABLE #query;
CREATE TABLE #query
(
ID INT IDENTITY(1, 1) ,
query nvarchar(4000) ,
);

INSERT INTO #query
(query)
select
'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT;
ALTER DATABASE [' + name + '] SET MULTI_USER;'
from sys.databases where database_id > 4

Declare @sql nvarchar(max)
while exists (select top (1) [query] from #query)
Begin
select top (1) @sql= [query] from #query
exec (@sql)
DELETE TOP(1) FROM #query
END
DROP TABLE #query;


Hopefully, this will help keep your systems as cursor free as possible.

Tuesday, 7 March 2017

Missing TLS 1.2

Most days you get to use existing knowledge and then just sometimes something cools comes your way.

This week we hit on a problem where an application server and client couldn't communicate, you could ping between them and interact with file shares, almost everything looked normal however the application could not connect.

After looking at the event log I found this error:

Log Name: System
Source: Schannel
Date: 11.02.2017 16:37:44
Event ID: 36888
Task Category: None
Level: Error
Keywords:
User: SYSTEM
Computer: FR11.CONSENTO.COM
Description:
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.

This error shows that communication between them that was trying to take place on the SSL was failing.

Closer look at the registry of both the client and the server the problem becomes clear, as the registry keys are not the same.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Configuration\Local\SSL\00010003
(Default) REG_SZ NCRYPT_SCHANNEL_SIGNATURE_INTERFACE
Functions REG_MULTI_SZ RSA/SHA256\0RSA/SHA384\0RSA/SHA1\0ECDSA/SHA256\0ECDSA/SHA384\0ECDSA/SHA1\0DSA/SHA1

On the other servers:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Configuration\Local\SSL\00010003
(Default) REG_SZ NCRYPT_SCHANNEL_SIGNATURE_INTERFACE
Functions REG_MULTI_SZ RSA/SHA512\0ECDSA/SHA512\0RSA/SHA256\0RSA/SHA384\0RSA/SHA1\0ECDSA/SHA256\0ECDSA/SHA384\0ECDSA/SHA1\0DSA/SHA1

This turns out be a known issue that is addressed with KB2975719, or a manual registry tweak.

Thursday, 2 March 2017

T-SQL with Powershell

If you're a DBA or developer and you need to run T-SQL statements to get you results then this is for you.

Now for me I like to keep a healthy happy SQL Server so I have the best practice building up over the years of my own T-SQL and that was great but became quite hard to connect to ever growing farms and resulted in me creating reporting database, and that then mean checking it adding columns when I created new reports etc.

Nightmare, so here is another way to do it, Powershell to the rescue.
Invoke-Sqlcmd allows me to run the T-SQL against the server for example.


SELECT CONVERT(INT, ISNULL(value, value_in_use))
 AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell'

If I want to run that check in Powershell it looks something like this.

#$instance = Get-Content -Path "C:\instances.txt"

#Check xp_cmdshell
foreach ($server in $instance){
try { 
    $xp_sqlcmd = (Invoke-Sqlcmd -QueryTimeout 200 -Query "SELECT CONVERT(INT, ISNULL(value, value_in_use))
     AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell';" -ServerInstance $server)

write-host "My out put is" $xp_sqlcmd.config_value

 }
Catch { write-host "Neo Broke the Matrix"
        write-host $_  
       break
       }
}

Now I know that looks way longer right? but think about it that can run on 200 servers, and if you want to output to file with let's say some good vs bad values it can look like this.

$logout = "C:\Users\Administrator\Desktop\Results.csv"
#clean up log
$logout | Remove-Item -force -ErrorAction SilentlyContinue

$instance = Get-Content -Path "C:\instances.txt"

#Formating report
"Check,Finding,Server,Rating" | Add-Content $logout

#Check xp_cmdshell
foreach ($server in $instance){
try { 
    $xp_sqlcmd = (Invoke-Sqlcmd -QueryTimeout 200 -Query "SELECT CONVERT(INT, ISNULL(value, value_in_use))
     AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell';" -ServerInstance $server)

$check = $xp_sqlcmd.config_value
"xp_cmdshell check should be return 0,$check,$server,High" | Add-Content $logout

    if ($xp_sqlcmd.config_value -eq "1" ) 
        {
        write-host -BackgroundColor Yellow -ForegroundColor Red "XP_CMDSHELL Is Enabled This is Not Desired On $server"
        }
        else {
             write-host -BackgroundColor Green -ForegroundColor Blue "XP_CMDSHELL Is Disabled On $server"
             }
 }
Catch { write-host "Neo Broke the Matrix"
        write-host $_  
       break
       }
}

Now I have a powerful reporting script and it can get longer better as I can add to it over time without needing a database or manually connecting to each server so in the end.

Note, if you have SQL Enterprise server you could do this my registering each server one at a time into  management studio however this is quicker and more dynamic in the long run.