Search This Blog

Tuesday, May 30, 2017

Check Backup\Restore status

When running the AG wizard it is not clear what the percentage completion figure is of each stage.

Use the query below to examine the status of backup and restore operations.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

 

 

Ref:https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/


Share/Bookmark

Tuesday, May 23, 2017

Central Management Servers - HA

 

Recently setup a new Central management Server for SQL Server, whilst doing this I was hoping to use HA, however CMS stores its config in the msdb database, and as such is not able to be HA’d… Sad smile

So… I started looking at a way for me to utilise HA to achieve this.

In the end I came up with using Always On listeners and powershell script to sync between two servers.

Because you can’t add a CMS server to the list, I decided to create a very small Always on environment to just deal with the CMS. I am not going to go through how to setup Always on or how to setup named instances.

Two servers and one instance.

  • DEV-DB01\CMS
  • DEV-DB02\CMS.

The listener was set on

  • DEV-DBCMS

So by doing this DEV-DBCMS would point at whatever server was the primary server. Connecting SSMS to the DEV-DBCMS, would point it at either DEV-DB01 and DEV-DB02 changes made in CMS would only appear on the servers DEV-DBCMS was pointing at.

So I came up with a PowerShell script that will use the DEV-DBCMS as the source of truth (and this would be whatever the primary server was in the HA), and then sync to the other server (in fact it syncs to itself as while, but makes no changes as it will always match). The PowerShell script is run every 5 minutes and from what I can see is working very well. This is not ideal as there is the possibility that changes can be lost if a failover occurs before a change to CMS has sync’d, but that should not occur that often and the benefit of having the CMS always online in some manner is more important.

This script is a modification on some CMS scripts developed by Chrissy LeMaire

https://blog.netnerds.net/smo-recipes/central-management-server/

I used the following command in SQl Server job to run the powershell script on a schedule of 5 minutes. I had the same job running on both servers in the HA, with jobs staggered by 2 minutes.

SQL Server Job Command

powershell –ExecutionPolicy Bypass -Command C:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db01\cms; powershell –ExecutionPolicy Bypass -Command W:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db02\cms;

Powershell Script

param([string]$fromcms, [string]$tocms)

$cr = "`r`n"

Write-Host "######################################$cr" -ForegroundColor Red
Write-Host "Starting Sync for $fromcms to $tocms $cr" -ForegroundColor Red
Write-Host "######################################$cr" -ForegroundColor Red

#For SQL 2012 Microsoft.SqlServer.Smo.dll Client Tools SDK is needed
#For SQL 2012 Servers, Microsoft.SqlServer.Management.RegisteredServers.dll must be copied from an newer version of SQL Server (2016 etc)

#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
#Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.RegisteredServers.dll'

#For older versions of powershell.
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers")

$fromserver = New-Object Microsoft.SqlServer.Management.Smo.Server $fromcms
$toserver = New-Object Microsoft.SqlServer.Management.Smo.Server $tocms
 
try {
    $fromcmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($fromserver.ConnectionContext.SqlConnectionObject)
    $tocmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($toserver.ConnectionContext.SqlConnectionObject)
    }
catch { throw "Cannot access Central Management Servers$cr" }
 
Function Parse-ServerGroup($fromserverGroup, $toservergroup)
{
    # Remove Servers
    foreach ($instance in $toservergroup.RegisteredServers)
    {
        $toservergroup.RegisteredServers.refresh()
        $instancename = $instance.name
        if($fromserverGroup.RegisteredServers.name -notcontains $instancename)
        {
            Write-Host "Deleting Server $instancename$cr" -ForegroundColor Red
            $oldserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $oldserver.ServerName = $instance.ServerName
            $oldserver.SecureConnectionString = $instance.SecureConnectionString
            $oldserver.ConnectionString = $instance.ConnectionString
            $oldserver.Description = $instance.Description
            $instance.drop()
            Write-Host "Deleted Server $servername$cr" -ForegroundColor Red
        }
    }
   
    # Add Servers
    foreach ($instance in $fromserverGroup.RegisteredServers)
    {
        $instancename = $instance.name
        if($toservergroup.RegisteredServers.name -notcontains $instancename)
        {

            if ($instance.ServerName.ToLower() -eq $tocmstore.DomainInstanceName.ToLower())
            {
                Write-Warning "Server name is Central Management Server name. Add prohibited. Skipping.$cr"
                continue
            }

            Write-Host "Adding Server $instancename$cr"
            $newserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $newserver.ServerName = $instance.ServerName
            $newserver.SecureConnectionString = $instance.SecureConnectionString
            $newserver.ConnectionString = $instance.ConnectionString
            $newserver.Description = $instance.Description
            $newserver.Create()
            Write-Host "Added Server $servername$cr" -ForegroundColor Green
        }
        else
        {
            Write-Warning "Server $instancename already exists. Skipped$cr"
        }
    }

    # Add Groups
    foreach($fromsubgroup in $fromserverGroup.ServerGroups)
    {
        $tosubgroup = $toservergroup.ServerGroups[$fromsubgroup.name]
        if ($tosubgroup -eq $null)
        {
            Write-Host "Creating group $($fromsubgroup.name)$cr" -ForegroundColor Green
            $tosubgroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($toservergroup,$fromsubgroup.name)
            $tosubgroup.create()
        }
        Parse-ServerGroup -fromserverGroup $fromsubgroup -toservergroup $tosubgroup
    }
}


foreach ($element in $array) {
    $element
}

foreach ($serverGroup in $fromcmstore.DatabaseEngineServerGroup)
{
    Parse-ServerGroup -fromserverGroup $serverGroup -toservergroup $tocmstore.ServerGroups[$serverGroup.name]
}


Share/Bookmark

Sunday, May 21, 2017

SQL Server - Use specific IP/Ports not all

Hi All,

Just something that threw me this morning and took a while to understand what was going on.

note: to disable dynamic ports, just delete the value in the field. To reenable just add 0 and restart the sql instance.

If you need\want your SQL server instance to only listen on specific IPs (and not all IPs in the machine), then you must set “Listen All” on the protocol tab to “No”, whilst this seems intuitive now there is an IP All section on the IP Addresses tab, that when you look at articles on the interwebs, this is the setting you change.

clip_image001

IPAll section on the IP Addresses tab

clip_image002

Then in the IP section you want to apply to, set the Enabled setting to Yes, restart the SQL server instance and the server should now be listening on only the server IP.

This does not affect the listeners which are on different IPs, and handled by the cluster and alwayson services.

image


Share/Bookmark

SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Had an issue where I was unable to get the “New Availability Group” wizard to successfully complete.

It would continually error with a 9692 Error, indicating that the endpoint couldn't be setup on port 5022.

I had expected this and on the endpoint tab I had changed to a different port (the servers already had other instances running on port 5022), however even though I changed the wizard to an unused port (i.e 5025), the error kept coming back with could not create end point on port 5022.

I had not come across this before and I cannot find anything about it on the interwebs..

The way I got around this was to manually setup the endpoints, before running the wizard (Changing the port to the desired value).

-- Create a new Database Mirroring Endpoint on Replica 1
CREATE ENDPOINT Hadr_Endpoint
AS TCP
(
   LISTENER_PORT = 5023
)
FOR DATA_MIRRORING
(
   ROLE = ALL,
   ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

-- Start the Endpoint on Replica 1
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED

DROP ENDPOINT Hadr_Endpoint

When the wizard got to the endpoint it then listed these endpoints and I was able to continue through (the ports are greyed out and can no longer be changed)

image

Would love to know why on this occasion the wizard failed, as I have happily changed the port in the past but this work around got me through.


Share/Bookmark

Monday, May 15, 2017

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.

servername\instancename

Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

“The target principal name is incorrect.  Cannot generate SSPI context. “

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue

 

 

References

https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

https://support.microsoft.com/en-in/help/2985455/kerberos-configuration-manager-for-sql-server-is-available

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

https://social.technet.microsoft.com/Forums/systemcenter/en-US/b879b1a7-c996-4a3f-8e9d-a70ebdffca44/spns-for-named-sql-instances?forum=operationsmanagergeneral

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65bcf7a3-ae42-43ae-958e-11dccb123e80/setspn-kerberos-and-named-instances?forum=sqlsecurity

https://social.technet.microsoft.com/wiki/contents/articles/18996.active-directory-powershell-script-to-list-all-spns-used.aspx


Share/Bookmark

Friday, April 28, 2017

AzureRM - Powershell Notes

Run POWERSHELL as admin

Get Verison of PowerShellGet

Get-Module PowerShellGet -list | Select-Object Name,Version,Path

What is PowerShellGet
https://blogs.msdn.microsoft.com/mvpawardprogram/2014/10/06/package-management-for-powershell-modules-with-powershellget/

I had to use –allowclobber to force the install.

Install-Module AzureRM (–allowclobber)

Load the module

Import-Module AzureRM

Login to azure

Login-AzureRmAccount

Get-AzureRmSubscription

Set-AzureRmContext -SubscriptionName "Example Subscription Two"

New-AzureRmResourceGroup -Name TestRG1 -Location "South Central US"

Get-AzureRmResourceGroup -ResourceGroupName TestRG1

Get-AzureRmResourceGroup
(returns all ResourceGroups)

References:

https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-3.8.0

https://docs.microsoft.com/en-us/powershell/azure/overview?view=azurermps-3.8.0

https://docs.microsoft.com/en-us/azure/azure-resource-manager/powershell-azure-resource-manager


Share/Bookmark

Tuesday, April 25, 2017

Setting up DNS on openwrt on TL-WDR4300

Found a couple of articles that helped, referenced below. Had to connect to router via SSH, as luci does not have the interface for the added packages.

Note: I was unable to install the dhcp server  in the article, although I did manage to install another dhcp package. This said I do not use dhcp and I didnot test whether dhcp worked.

This did not install

opkg install isc-dhcp-server-ipv4

I was able to install the following package from the openwrt site.

opkg install https://downloads.openwrt.org/chaos_calmer/15.05/ar71xx/generic/packages/packages/dhcpcd_6.4.3-1_ar71xx.ipk

We remove the dnsmasq service which is a combined DHCP and DNS (forwarder) server and replace this with separate DNS and DHCP services.

I then followed the article

https://maroskukan.wordpress.com/2015/02/24/openwrt-spinning-up-authoritative-dns-server/

Changing values where needed (examples below)

I used the following acl in the named.conf file, note two ip ranges as my router is setup as a bridging client.

acl "trusted" {
     192.168.75.0/24;
     172.75.75.0/24;
     localhost;
     localnets;
};

options {
        directory "/tmp";
        recursion yes;
        allow-recursion { trusted; };
        allow-transfer { trusted; };
        allow-query-cache { trusted; };
        // If your ISP provided one or more IP addresses for stable
        // nameservers, you probably want to use them as forwarders.
        // Uncomment the following block, and insert the addresses replacing
        // the all-0's placeholder.

        forwarders {
                192.168.75.1;
        };

        auth-nxdomain no;    # conform to RFC1035
};

Created folder to hold the zone files

mkdir /etc/zones
using existing zone files as templates.
cp /etc/bind/db.local /etc/bind/zones/db.homedomain.local
for reverse lookup (two zones as I have created two subnets)
cp /etc/bind/db.127 /etc/bind/zones/db.75.168.192
cp /etc/bind/db.127 /etc/bind/zones/db.75.75.172
Then edited the files for my needs

;
; BIND data file for reverse look up of 192.168.75/24
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@      IN      NS      router.homedomain.local.
11     IN      PTR     machine1.homedomain.local.
18     IN      PTR     router.homedomain.local.
110    IN      PTR     machine3.homedomain.local.

;
; BIND data file for reverse look up of 172.75.75/24
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@       IN      NS      router.homedomain.local.
1       IN      PTR     router.homedomain.local.
2       IN      PTR     machine4.homedomain.local.
10      IN      PTR     machine5.homedomain.local.

 

;
; BIND data file for homedomain.local zone
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@       IN      NS      router.homedomain.local.
@       IN      A       192.168.75.18
router  IN      A       192.168.75.18
machine4        IN      A       172.75.75.2
machine5        IN      A       172.75.75.10
machine3        IN      A       192.168.75.110
machine1        IN      A       192.168.75.11


https://wiki.openwrt.org/doc/howto/dns.bind

https://maroskukan.wordpress.com/2015/02/24/openwrt-spinning-up-authoritative-dns-server/

http://blog.differentpla.net/blog/2013/10/30/replacing-dnsmasq-on-openwrt

https://kb.isc.org/article/AA-00269/0/What-has-changed-in-the-behavior-of-allow-recursion-and-allow-query-cache.html


Share/Bookmark