Thursday, December 26, 2013

SQL Server 2012 AlwaysOn Availability Group - My experience with a proof-of-concept

Just had my experience building our organization's first SQL Server 2012 AlwaysOn Availability Group.  This is a proof-of-concept environment that will be used to demonstrate if we should build out this environment across physical locations.  This post summarizes the resources used in this initial build.
 
The white papers from Microsoft were the more comprehensive resource.   Used these when planning, and justifying the proof-of-concept.
 
Once it got time to actually build, these step-by-steps guides or checklists were used:
 
Here were some of the issues I ran into, and lessons learned
 
Separation of Duties - Server Administration and Database Administration
In my organization, there is a separate Server team controlling virtualization and Server OS while the 'Database' team has full rights to the database software.  Our first build required some back-and-forth collaboration to resolve issues that went above and beyond a standard database server
Lessons learned:
Setup up through the Windows Server Failover Cluster step mostly involves the Server Administration role.  Expect some collaboration needed between Server Administration and Database Administration in new environments
 
AlwaysOn Availability Group configuration
The AlwaysOn Availability Group configuration itself was extremely straight-forward.  The issues I ran into turned out to be items that, either by habit or due the environment, were overlooked when creating a test.  Might not run into these in production with the stricter controls on individual SQL Server instances
 
Note that the database to be made 'AlwaysOn' should be attached or restored to the 'Primary' SQL Server instance in the cluster.  During the AlwaysOn group creation, it can be restored to any secondary instances. 
Lessons learned:
Assess the SQL Server instances to be included in an AlwaysOn group against the standard build for a standalone SQL Server in production.
 

Monday, July 29, 2013

Fixup Script for conflict tables on a Transactional Replication

This script fixes up the conflict tables generated by a transactional replication subscription, and marks them as system tables.

In SQL Server replication, a subscription to a publication of the type 'Transactional replication with updatable subscriptions' allow edits on the subscriber. Conflicts in those edits (e.g. a record modified at the subscriber and publisher) have to be resolved. The default setting is 'Keep the publisher change'. If a given subscriber is on the losing side of the change, conflicts are stored on the subscribers and can be viewed through the Conflict Viewer on the subscriber. They are stored in conflict tables per publication, per table in the format 'dbo.conflict_{PublicationName}_{TableName}. These tables are system tables.

In the scenario that led to the need for this script, both of the servers was patched to SQL Service Pack 2, and replication stopped and restarted. At some point during the process, the conflict tables showed as User Tables. While replication did work continue successfully, having the tables as User Tables was problematic. For starters, permission grants or scripts that target all user tables risked allowing edits to these replication tables, and the Object Explorer view in SSMS became quite messy.

Before

After

Limited testing for this has occurred - basically confirmed successful insert, update, and delete transactions before and after running this script.

Please note much credit for assisting in finding the underlying symptoms and general brainstorming should go to all the people at @SacSQLUG, and @SQLSoldier

SQL

Edit|Remove

/*

FixupConflictTables

 

Will examine the conflict tables generated by a transactional replication subscription, and mark them 

as system tables if for some reason they are showing up as User Tables

*/

--set this constant to have this script perform the action, otherwise it will output the resulting script only

DECLARE @ExecuteNow bit

SET @ExecuteNow = 0

 

--find the name of the associated publication

DECLARE @Publication sysname

SELECT @Publication = publication FROM dbo.MSreplication_subscriptions

 

PRINT '--Fix up conflict tables for the publication ' + @Publication

 

--buffers to hold SQL statements

DECLARE @SQL nvarchar(4000)

SET @SQL =''

DECLARE @tablename sysname

 

--loop through the tables that look like 'conflict_{Publication}_{tablename) and aren't marked as MS shipped (i.e. system objects)

DECLARE curConflictTables CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.tables WHERE name LIKE 'conflict_' + @Publication + '_%'

    AND is_ms_shipped = 0

 

OPEN curConflictTables

FETCH NEXT FROM curConflictTables INTO @tablename

 

WHILE @@FETCH_STATUS = 0

BEGIN

    --build the sql to convert this to a system table

    SET @SQL = 'sys.sp_MS_marksystemobject ''' + @tablename + ''''

     

    PRINT @SQL

    IF @ExecuteNow = 1 

        EXECUTE sp_executesql @SQL

 

    FETCH NEXT FROM curConflictTables INTO @tablename

END

 

-- close a cursor

CLOSE curConflictTables

DEALLOCATE curConflictTables

 

Tuesday, April 23, 2013

WinDirStat for SQL Server DBAs

WinDirStat can be used to quickly assess the drive usage by focusing on the relative usage of the drive.   As a SQL Server DBA, disk volume and usage may not be your direct purview, but undoubtedly it can affect you.  Usually it will affect you at the worst time: The disk is full and SQL Server database and supporting files are to blame.  The immediate task affecting the DBA will be to get the disk usage under control - this requires you to assess how to quickly recover disk space in low risk areas.   A follow-on task to such an issue will be to then assess capacity and make recommendations.  WinDirStat is a free, open source tool that can help in these situations.

The three pane layout of WinDirStat allows you to simultaneously see and compare the following aspects: 
  • Relative Size per folder
  • Relative Size per file type
  • Proportional graphical view of each file

For example, in the below screen shot, a drive was nearly 99% full.  This is a simple example because the drive is organized very well already and has a very specific use.  It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue.  And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.

Example #1:

This is a simple example because the drive is organized very well already and has a very specific use.  It becomes clear quickly that the folder 'SQLLogs' uses the most space, that the 'LDF File' file type or 'SQL Server Log File' is the file type at issue.  And through the graphical view, you can quickly click and assess that the largest file is a particular log file that has grown to 118 GB.

In this example, a more multipurpose volume on a development server, seeing the three aspects (by folder, by file type, and the graphical view) showed a more nuanced picture. 

Example #2:

The largest folder is the 'BackupsMonthly'.  The largest file type was 'BAK File' - SQL Server Backups.  Clicking the large blue squares revealed the largest SQL Server Backups.  But in this case, those files were relatively stable in size, that is, a certain amount of backup files were necessary. 

Secondarily, this revealed a large usage of the Recycle Bin (@RECYCLE.BIN) which was easily cleaned.  Also a few large disk images ('ISO' shown as associated with 'Virtual CloneDrive' in the image above) related to software that was a candidate to be relocated to another server.

How to get WinDirStat
WinDirStat is a free, open source tool.  It can be downloaded from WinDirStat.info.  Since it is open source, the program can be modified or extended and through the folks at PortableApps.com, there is a portable version available.

I recommend acquiring the portable version of WinDirStat.  Portable WinDirStat requires no installation, so the files can simply be copied - on your workstation, a network shared folder, or onto your SQL Servers themselves