Elfriede lebe hoch!

Szenario.

Sie sind DBA einer IT-Firma, die ein Tickettool einsetzt, um Geschäftsprozesse abzubilden. Nach Jahren ist das Datenvolumen der Datenbank auf mehr als 250 GB angewachsen. Elfriede vom Controlling hat mit den SQL Server Reporting Services mehrere komplexe Berichte implementiert. Sie benötigt täglich zu Beginn der Tagschicht und am Nachmittag Reports bezüglich der KPIs. Am Monatsanfang werden jeweils KPIs summiert auf die letzten 12 Monate benötigt.

Die User, die das Tickettool zum täglichen Arbeiten benötigen, beschweren sich in letzter Zeit häufig darüber, dass es “langsam” geworden ist. Manchmal sei das Arbeiten mit dem Tickettool kaum möglich. Nach eingehender Analysen stellen Sie fest, dass die größten Performanceeinbußen sich mit den Zeiten decken, in denen die monatlichen Reports laufen.

Ihre Umgebung: Ein Multi-Instanz Failover Cluster mit SQL Server 2012 SP 3 Standard Edition. Die produktive Datenbank läuft in einer eigenen SQL Server Instanz, außerdem gibt es noch eine weitere SQL Server Instanz, die ebenfalls dem Tickettool zugeordnet ist, in der lediglich  hin und wieder Funktionstests durchgeführt werden.

Stellen Sie sicher, dass mit möglichst wenig administrativem Aufwand und so kostengünstig wie möglich das Reporting durchgeführt werden kann, ohne die produktive Datenbank in der Performance zu beeinträchtigen. Stellen Sie sicher, dass Sie über Fehlfunktionen der implementierten Lösung informiert werden.

Wählen Sie die bestmögliche Antwort aus den vier folgenden Möglichkeiten.

Antworten

(1)
Sie führen ein Upgrade der produktiven SQL Server Instanz auf Version 2016 in der Enterprise Edition durch und implementieren Columnstore Indexes. Elfriede soll auch vom Fortschritt profitieren.

(2)
Sie führen ein Upgrade der zweiten SQL Server Instanz auf die Enterprise Edition durch. Anschließend stellen Sie die Datenbank in der zweiten SQL Server Instanz im NORECOVERY Modus her, etablieren Database Mirroring und richten in der zweiten SQL Server Instanz einen Datenbank Snapshot ein, der monatlich am Monatsersten kurz nach Mitternacht automatisch unter Zuhilfenahme eines SQL Server Agent Jobs erstellt wird. Über den Erfolg des Jobs lassen Sie sich per E-Mail informieren. Sie entziehen Elfriede die Lese-Berechtigung in der produktiven SQL Server Instanz und erteilen ihr Lese-Berechtigung in der zweiten SQL Server Instanz.
Danach trinken Sie einen auf Dirk Angermann. 🙂

(3)
Sie weisen Elfriede an, sich die monatlichen Reports als CSV abzuspeichern und nur noch Reports einen Monat rückwärts anzufragen.

(4)
Sie schlagen die Hände über dem Kopf zusammen und denken sich: “Nicht schon wieder diese Elfriede!”.

#Elfriede4Ever

Vielen Dank für’s Lesen und bis zum nächsten Mal in Rotenburg a.d. Fulda zur SQL Server Zertifizierung

CMS

Advertisements
Posted in #Elfriede, SQL Server, Uncategorized | Leave a comment

Shrink an SAP database “online”.

Shrinking is bad

First of all: shrinking a database is nothing that you should do on a regular basis, like using this step in a Maintenance Plan or setting it up yourself via SQL Server Agent Jobs.

But if the customers asks for it, especially after having implemented Page Compression to save a lot of disk space – you have to obey 😉

First Approach

Of course we did not start with the production system, we already shrinked the DEV and QA system(s) of many other SAP systems without impact using the following approach:

Step 0: Document the original size and Space Usage.

shrinksap_0

Step 1: SHRINKDATABASE with NOTRUNCATE

-- Shrink database <DatabaseName> with NOTRUNCATE to move all database pages to the first most place in the physical file.
DBCC SHRINKDATABASE (SID, NOTRUNCATE);

shrinksap_1

Step 2: SHRINKDATABASE with TRUNCATEONLY

-- Shrink database <DatabaseName> with TRUNCATEONLY to cut the free space at the end of the physical file.
DBCC SHRINKDATABASE (SID, TRUNCATEONLY);

shrinksap_2

Here we can see a huge saving.

Step 3: Check Fragmentation

/*
    STEP 3 and 5

    Check Fragmentation of Database for documentation purposes.
*/

USE <DatabaseName>

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
ORDER BY AvgFragmentationInPercent DESC

shrinksap_3

You can see that the Fragmentation is as high as you can imagine.

Step 4: Index Maintenance

We use Ola Hallengrens Maintenance Solution for Index Optimizing, so this is simply executing the very Job in SQL Server Agent.

Situation afterwards:

shrinksap_4

Now with new unallocated space as the Indexes had to be rebuilt and reorganized. That increases the size of the data file, but far away from the original size.

Step 5: Check Fragmentation again

After the Index Maintenace is done, the Fragmentation of the Indexes look like they should.

Locking, Blocking, Deadlocking

After quite a few systems, where the approach shown above worked out pretty well, we faced problems with the most important production system of the customer. We experienced Deadlocks where the customer’s employees had to sit in front of their SAP and wait for their Transactions to complete.

So we had to killl the Shrink-Job and think of another solution.

We came across the blog post written by Jürgen Thomas

Database Volume is compressed nicely – but now we want to shrink the data files

with all the good recommendations, but we definitely were not allowed to have a downtime for the Shrink process as the project team assumed that it would take to long to run.

So with some additional hints from Clas Hortien (thanks, Clas 🙂  ) we were able to invent a process of shrinking the files, truncating them and so step by step releasing the unused disk space back to the file system

Final Solution

SAP Database

In this special scenario we had to deal with:

  • 2.25 TB production database with 7 equally sized datafiles
  • SAP ECC System (main ERP System)
  • Global Customer, operates 24 x 7
  • No downtime accepted for Shrinking Process
  • No performance impact accepted

Phew…

Shrinking Plot

  • Run on a daily basis between 7 pm and 11 pm. This is the timeslot where the customer accesses the system not too much.
  • On occuring Deadlocks “owned” by the Shrink process: Kill the Shrink Process.

The solution contains three SQL Server Agent Jobs per Data File.

  1. FIT DBA SID Shrink File SIDDATA1
  2. FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE
  3. FIT DBA SID Shrink File SIDDATA1 STOP KILL and TRUNCATE JOB

and one single “Prevent” Job, that had to find out, if deadlocks occur and kill the Jobs:

FIT DBA SID Shrink File Prevent Deadlocks

shrinksap_5

Following is the description of what the Job Steps really do. I will explain them for the first data file, the otheres are just the same.

FIT DBA SID Shrink File SIDDATA1

Try to shrink the Datafile 1 to the smallest size possible.

DBCC SHRINKFILE (SIDDATA1, 1);

FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE

This job consists of two steps:

KILL

Kill the Shrink Job.

DECLARE @SessionID INT
DECLARE @SQLStatement NVARCHAR(20)
-- select current session
SELECT		@SessionID = r.session_id
			-- , r.*, t.*
FROM		sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE		t.text LIKE '%DBCC SHRINKFILE (SIDDATA1,%'
AND			r.command LIKE 'Dbcc%'
PRINT 'SessionID to be killed: ' + CONVERT(NVARCHAR(14),@SessionID)

SET @SQLStatement = 'KILL ' + CONVERT(NVARCHAR(14),@SessionID) + ';'
PRINT @SQLStatement
EXEC sp_executesql @SQLStatement

TRUNCATE

Afterwards execute the TRUNCATEONLY to release the free space at the end of the data file.

DBCC SHRINKFILE (SIDDATA1, TRUNCATEONLY);

FIT DBA SID Shrink File SIDDATA1 STOP KILL and TRUNCATE JOB

This job now only got one step. It is necessary to also kill the “TRUNCATEONLY” job, because too many “running” jobs would also lead to locking and blocking.

EXEC dbo.sp_stop_job N'FIT DBA SID Shrink File SIDDATA1 KILL and TRUNCATE'

FIT DBA SID Shrink File Prevent Deadlocks

Now this is the watch dog of all Shrink jobs:

DECLARE @WhoIsActive TABLE (
	SessionID INT
	, BlockingSessionID INT
	, SQLStatement NVARCHAR(max)
)

INSERT INTO @WhoIsActive (SessionID, BlockingSessionID, SQLStatement)
SELECT S.session_id
, ISNULL(R.blocking_session_id, 0)
, ST.text
FROM sys.dm_exec_sessions S
INNER JOIN sys.dm_exec_connections C ON S.session_id = C.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS ST
LEFT JOIN sys.dm_exec_requests R ON S.session_id = R.session_id
WHERE S.is_user_process = 1
AND S.session_id <> @@spid -- exclude itself

-- ##########################################################
-- ### SELECT
DECLARE @BlockingShrinkSessionID INT
SELECT	@BlockingShrinkSessionID = SessionID FROM @WhoIsActive
WHERE	SQLStatement LIKE '%DBCC SHRINKFILE (SIDDATA%'

IF EXISTS(SELECT 1 FROM @WhoIsActive WHERE BlockingSessionID = @BlockingShrinkSessionID)
BEGIN

	DECLARE @JobName sysname	 

	DECLARE TheCursor CURSOR FOR
	SELECT name from msdb.dbo.sysjobs jobs WHERE jobs.category_id = 102 AND name like 'FIT DBA SID Shrink File SIDDATA%'

	OPEN TheCursor
	FETCH NEXT FROM TheCursor INTO @JobName   

	WHILE @@FETCH_STATUS = 0
	BEGIN
		   DECLARE @SQLStatement NVARCHAR(256);
		   SET @SQLStatement = 'EXEC msdb.dbo.sp_stop_job N''' + @JobName + ''';'
		   PRINT @SQLStatement;
		   EXEC(@SQLStatement);

		   FETCH NEXT FROM TheCursor INTO @JobName
	END   

	CLOSE TheCursor
	DEALLOCATE TheCursor

	execute msdb.dbo.sp_send_dbmail @profile_name='<PutYourMailProfileHere>',	@recipients='<PutYourEMailRecipientHere>', @subject='FIT DBA SID Shrink PREVENTED FROM BEING DEADLOCKED'

END

Schedule of the Jobs

So how were the Jobs scheduled?

shrinksap_6

  1. We started with the first job on the full hour and scheduled each job hourly.
  2. The “SHRINK” jobs were scheduled to run.
  3. The “KILL and TRUNCATE” jobs were scheduled to run 5 minutes after the “SHRINK” job.
  4. The “STOP KILL and TRUNCATE JOB” jobs were scheduled to run 5 minutes after the “KILL AND TRUNCATE” job.
  5. Between each data file we put 2 minutes “delay”.
  6. The “Watch Dog Job” was scheduled to run throughout the whole timewindow every 10 seconds. This is the time the customer allowed the employees to sit in front of the SAP screen.

Outcome

The customer did not experience any performance impact on the production system, just a small bunch of “Prevent from being deadlocked” emails hit our inbox.

This is the historical decreasement of the size of the database:

shrinksap_7

 

Thank you for reading and best of luck shrinking your databases (if absolutely necessary)

Christoph

 

 

Posted in Uncategorized | 1 Comment

SQL Server 2008 R2 Installation Failed -2068052081, 1212, 1935

Today:

Server Team provided Windows Server 2008 Service Pack 2, Build 6002

Installation of SQL Server 2008 R2 failed with this message:

Overall summary:
 Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
 Exit code (Decimal): -2068052081
 Exit facility code: 1212
 Exit error code: 1935
 Exit message: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
 Start time: 2016-12-09 17:16:34
 End time: 2016-12-09 17:26:37
 Requested action: Install
 Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20161209_171446\SqlSupport_KatmaiRTM_Cpu64_1.log
 Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1

Google found:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/72f27c30-5307-49e3-85dc-12a9c4704296/sql-server-2008-setup-failure-win-7-x64-msi-error-1935-an-error-occurred-during-the-installation

Downloaded and installed the missing component on Target Server:

http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=90548130-4468-4BBC-9673-D6ACABD5D13B&displaylang=en

Now have a look at programs and features:

2008r2_vc80

… note that the marked one has a size of 3,84 MB while the one with the exact same (display) name just has got 566 kb.

Retried the SQL Server Installation and guess what: It worked 🙂

 

Thanks for reading,
CMS

Posted in Uncategorized | Leave a comment

0x851c0001 – the credentials you provided for the ‘SQLSERVERAGENT’ service is invalid.

We always install SQL Server with configuration.ini files to be sure that each and every server looks like the other. Today we received the error message:

(01) 2016-11-29 14:23:47 Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
(01) 2016-11-29 14:23:47 Slp: Message: 
(01) 2016-11-29 14:23:47 Slp: The credentials you provided for the 'SQLSERVERAGENT' service is invalid.
(01) 2016-11-29 14:23:47 Slp: HResult : 0x851c0001
(01) 2016-11-29 14:23:47 Slp: FacilityCode : 1308 (51c)
(01) 2016-11-29 14:23:47 Slp: ErrorCode : 1 (0001)

#1 – Special characters

Sometimes it’s just this simple:
The password generator added a “&” somewhere in the password for the Service Account:

a1B2c3D&4e5F6gH

Following the Password Policy in MSDN, you will find that “&” is not on the whitelist, just

  • ! – exclamation point
  • $ – dollar sign
  • # – number sign
  • % – percent

is allowed.

#2 – Leading character

You maybe will find that even following the rule mentioned under the first heading still is not enough. Then please doublecheck if you have a leading character e.g. “a” in front of your password. SQL Server does not accept passwords beginning with an integer.

#3 – Active Directory Account locked

Even if you followed the rules mentioned under the first two headings, you still might get the error message. Then consult your domain admins and ask if your service user’s account might be locked. Let them unlock the account and try it again.

#Works4me

Happy pa$$w0rding to all of you 🙂

Thank you for reading,
CMS

Posted in Uncategorized | Leave a comment

Log chain broken? How to deal with backups from the future.

Today (2015-10-05) we had to deal with an interesting phenomenon. Backups from the future breaking the log chain!

Problem

Our third party software solution that is responsible for backup and restore tried to take a transaction log backup of a database but always converted the transaction log backup into a full backup as it detected the backup chain being broken.

You might also notice this informational message somewhere in your backup software:

Broken chain detected for database [<PlaceYourDatabaseNameHere>]. Automatically converting the backup to Full. This could happen due to a) Performing backups outside our software. b) Running log backups without running full after database restore of previous cycle. c) May have failed to record previous backup information of the database in <PlaceYourBackupSoftwareNameHere>.

The Backup chain is very important when your database is in recovery model FULL, because you would have lost the ability to restore to a certain point-in-time. The software detects this and automatically tries to fix this by taking a full backup. (A differential would have done the job, says Paul Randal: A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup)

So what exactly happend to our SQL Server Instance?

Analysis

Let’s have a look at the backup history for the database:

;WITH backup_cte
 AS ( SELECT database_name ,
 backup_type = CASE type
 WHEN 'D' THEN 'database'
 WHEN 'L' THEN 'log'
 WHEN 'I' THEN 'differential'
 ELSE 'other'
 END ,
 backup_finish_date ,
 rownum = ROW_NUMBER() OVER ( PARTITION BY database_name,
 type ORDER BY backup_finish_date DESC )
 FROM msdb.dbo.backupset
 )
SELECT database_name ,
 backup_type ,
 backup_finish_date
FROM backup_cte
WHERE database_name = DB_NAME(DB_ID())
 AND rownum = 1
ORDER BY database_name;

This results in:

backupdatefromthefuture

Wow – a transaction log backup from the future!

That of course could be the problem for the backup software.

But how would that effect the software to think that the backup chain is broken? The backup chain is intact when the last_lsn of the previous transaction log backup always is the first_lsn of the following transaction log backup. This is how the transaction logs are “chained”.

So let’s take a look at the LSNs:

-- ##################
SELECT bs.database_name
 , bs.backup_start_date
 , bs.backup_finish_date
 , CASE bs.type
 WHEN 'D' THEN 'DATABASE'
 WHEN 'I' THEN 'DIFFERENTIAL'
 WHEN 'L' THEN 'LOG'
 END AS backup_type
 , first_lsn
 , last_lsn
FROM msdb.dbo.backupset bs
WHERE database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_start_date DESC

This results to:

lsns

where you can see that the first_lsn from the transaction log backup having finished at 2016-10-01 03:48:49.000 has a LSN in between the first_lsn and last_lsn of the full backup.

The following transaction log backups now fulfills the chain. Each last_lsn is the new first_lsn.

SQL Server has another source where to find the relevant last_lsn for a given database, the DMV “database_recovery_status“:

select last_log_backup_lsn
from sys.database_recovery_status
where database_id = db_id()

For our special database the last_log_backup_lsn resulted as:

last_log_backup_lsn

but wait, this is exactly the last_lsn from the screenshot above, so everything should be fine, right?

Now this is a shot in the dark, but i assume that the backup software also consideres the date to order the backups, not only the ascending LSNs.

Executing the following T-SQL statement:

SELECT bs.database_name
 , bs.backup_start_date
 , bs.backup_finish_date
 , CASE bs.type
 WHEN 'D' THEN 'DATABASE'
 WHEN 'I' THEN 'DIFFERENTIAL'
 WHEN 'L' THEN 'LOG'
 END AS backup_type
 , first_lsn
 , last_lsn
 , srs.last_log_backup_lsn AS [database_recovery_status.last_log_backup_lsn]
FROM msdb.dbo.backupset bs
 INNER JOIN sys.database_recovery_status srs ON srs.database_guid = bs.database_guid
WHERE database_id = DB_ID()
ORDER BY bs.backup_start_date DESC

results to:

lsns_differ

Here you can see that the LSN from the DMV sys.database_recovery_status differs to the one from msdb.dbo.backupset. This is just because of the column backup_start_date being taken into consideration.

… and in case you want a simple query that tells you, if you have a broken log chain, you can use this:

SELECT  	TOP 1 last_lsn as [msdb.dbo.backupset.last_lsn]
			, srs.last_log_backup_lsn AS [sys.database_recovery_status.last_log_backup_lsn]
			, CASE WHEN bs.last_lsn = srs.last_log_backup_lsn THEN 'log chain intact' ELSE 'LOG CHAIN BROKEN !!!' END AS [Log Chain Status]
FROM		msdb.dbo.backupset bs
			INNER JOIN sys.database_recovery_status srs ON srs.database_guid = bs.database_guid
WHERE		srs.database_id = DB_ID()
AND bs.type = 'L'
ORDER BY	bs.backup_start_date DESC

This results as:

logchainbroken

Solution

Just delete the row from the future from the msdb.

I used the code, that is implemented in the system stored procedure msdb.dbo.sp_delete_backuphistory and slightly changed just two things:

as i had to delete records from the future, i had to change

WHERE backup_finish_date < @oldest_date

into

WHERE backup_finish_date > GETDATE()

So the T-SQL to delete information about backups from the future looks like this:

 DECLARE @backup_set_id TABLE (backup_set_id INT)
DECLARE @media_set_id TABLE (media_set_id INT)
DECLARE @restore_history_id TABLE (restore_history_id INT)

INSERT INTO @backup_set_id (backup_set_id)
SELECT DISTINCT backup_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date > GETDATE() @oldest_date

INSERT INTO @media_set_id (media_set_id)
SELECT DISTINCT media_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date > GETDATE() @oldest_date

INSERT INTO @restore_history_id (restore_history_id)
SELECT DISTINCT restore_history_id
FROM msdb.dbo.restorehistory
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)

BEGIN TRANSACTION

DELETE FROM msdb.dbo.backupfile
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.backupfilegroup
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorefile
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorefilegroup
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.restorehistory
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit

DELETE FROM msdb.dbo.backupset
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit

DELETE msdb.dbo.backupmediafamily
FROM msdb.dbo.backupmediafamily bmf
WHERE bmf.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bmf.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit

DELETE msdb.dbo.backupmediaset
FROM msdb.dbo.backupmediaset bms
WHERE bms.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bms.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit

COMMIT TRANSACTION
RETURN

Quit:
ROLLBACK TRANSACTION

 

Thank you for reading,

Christoph

 

Posted in Backup, SQL Server | Leave a comment

Create failed for Availability Group Listener

Problem

Today I tried to create an AlwaysOn Availability Group. Everything went fine until I configured the Availability Group Listener.

It failed with the error message:

Create failed for Availability Group Listener <ListenerName>.
Microsoft SQL Server, Error 19471.

AvailabilityGroups_CreateListenerError

The Windows Application Event Log is a little bit more helpful on that:

Cluster network name resource <ClusterName> failed to create its
associated computer object in domain. [...]
verify that the Cluster Identity <ClusterName> has 'Full Control' permission
to that computer object using the Active Directory Users and Computers tool.

AvailabilityGroups_CreateListener_EventLog

There we go.

Solution

If you have the rights in your domain to do so, just help yourself out of this, otherwise you have to contact your Domain Administrator to help you.

On your Domain Controller:

  • Open “Active Directory Users and Computers”
  • In menu “View” check “Advanced Features” to be able to find the OU where your Cluster object is located in.
  • On the root of your domain right click and choose “Find…”
  • In drop down meny “Find” select “Computers”
  • In the text box for “Computer name” type your Cluster name and click button [ Find Now ]
  • View the “Properties” of your Cluster object that was found.
  • On tab “Object” you will find the location (OU) where your Cluster object is located in.
  • Close all popups.
  • Navigate to the location you just figured out, perform a right click and choose “Properties”.
  • In tab “Security” click the button [ Add… ]
  • Click the button [ Object Types… ] , mark the checkbox next to “Computers” and leave the popup with a click on [ OK ].
  • In the textbox enter the name of your Cluster, check the name and leave the popup with a click on button [ OK ].
  • In the field for the permissions mark the check box for “Create all child objects” and click Apply.
  • Leave the Dialog open.

 

Go to SQL Server Management Studio and repeat the attempt to create the Availability Group Listener.

Go back to the Domain Controller (or ask your Domain Admin to do so) and remove the “Create all child objects” permission for the Cluster.

 

Happy listening to your Availability Groups 🙂

Christoph

Posted in AlwaysOn Availability Groups, SQL Server, Uncategorized | Leave a comment

redgate SQL Monitor – The RPC Server is unavailable

Lucky me, I am setting up redgate SQL Monitor to monitor SQL Server Instances. First to say – as ever – redgate is developing ingeniously simple tools. So the installation just took minutes, the services on one VM, the repository database on another.

After having this successfully finished i logged into the Web GUI and configured my first target Host where the SQL Server Instance is running that i want to monitor.

As expected everything went fine and the windows metrics appeared like “Disk avg. write time”.

Problem

But i was not able to have a look at the SQL Server metrics. So something must have gone wrong. I had a look at Configuration -> Monitoring: “Monitored servers” and found an unconnected machine.

But wait – the first few seconds there was a “Monitoring connected” message, so something must have gone wrong a couple of seconds later on. In column “Actions” i chose the link “Show log”.

redgate_SQLMonitor_Errorlog

Of course i googled “The RPC server is unavailable” along with “0x800706BA”. As I am currently dealing with a Windows Server Failover Cluster Instance I tried the  workaround mentioned in Warning Event ID 5605 is Logged in Application log when querying MSCluster namespace through WMI and edited the ClusWMI.mof adding the value FALSE to [RequiresEncryption(FALSE)]. But that did not help.

I also followed the advice from redgate support to check all methods that SQL Monitor uses to connect to the target Host and also the SQL Server Instance.

I also checked the firewall settings allowing tcp communication on port 135 and > 1024. Everything open.

When it came to WMI test using WbemTest I tried to connect to my target Host, but also received the well known error:

redgate_SQLMonitor_Wbem

So I consulted a colleague of mine to double check if my target Host was available from another VM.

Guess what: It was. So it must have had something to do with the configuration of the two VMs we were trying to connect to the target Host.

She mentioned that she had configured the DNS suffixes in the local search list.

That did the trick. With all my unsuccessful attempts to connect to my target Host I always defined it fqdn in SQL Monitor. But i guess that the WMI security settings on my target Host prevented me from getting the right response.

Solution

As the target Host is not in the same domain that my monitoring VM is in, i had to put the IP Address of the very target Host into the hosts file on the monitoring VM in location “C:\Windows\System32\drivers\etc\hosts”


123.45.67.89    SERVERNAME

Please note that I just put in the NetBIOS Name of the Server.

That did the trick.

Going back to SQL Monitor, deleting my unsuccessfully configured taret Host and configuring it once again, but this time not fqdn but only with the SERVERNAME it all worked well and finally i got everything up and running.

Happy monitoring to all of you 🙂

Thank you for reading,

Christoph

 

 

 

Posted in SQL Server | Leave a comment