posts - 230, comments - 423, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server

Saturday, December 31, 2016

Configuring MSDTC Across Domains

One of my clients has applications that use MSDTC.  We’re spinning up a SQL Server 2016 Availability Group to take advantage of the MSDTC support it introduced.  Most of these distributed transactions take place across linked servers.  And of course the new servers are in a new domain with a very limited trust with the old domain. 

I found lots of articles and checklists for configuring MSDTC but none had everything I needed.  This is the checklist I came up with to configure cross-domain MSDTC with a limited trust in place.  (I don’t know what kind of trust it is.  I just know I have to put accounts into Domain Local groups in order to use them across the trust.  And it’s one way.)


I found LOTS of articles on configuring MSDTC.  These are the ones I found to be the most comprehensive. 

  1. Troubleshooting MSDTC Communication Checklist – This is one of the better checklists I found.  Has a good list of required ports.
  2. Troubleshooting Problems with MSDTC – Another good resource
  3. DTCPing – A VERY useful utility to confirm basic connectivity.
  4. DTCTester – Utility to perform a distributed transaction.


There are the steps that future Bill will need to get this configured next time.

Name Resolution

We were crossing a domain boundary and weren’t getting good NetBIOS name resolution.  I had to put a host file entry on both servers pointing to each other with just the machine name.  I eventually had to put a second name in for the Availability Group Listener.  You need to be able to have a ping –a resolve the names on both sides using only the name and not a FQDN.  And if you’re editing the hosts file don’t forget to run NOTEPAD as an Administrator.

Fix the Matching CIDs

I’m not sure exactly how this came to happen but both these machines had the same CID values for MSDTC.  (Note: I still don’t really know what a CID is but I know they can’t have the same one – see the second document above.)  The only way to change the CID value is reinstall MSDTC.  If you’re building machines from images you’ll probably have this problem.  You reinstall by running:

msdtc –uninstall

msdtc –install

I suggest a reboot after each step.  Until I rebooted I didn’t see the service installed.

Open the Ports

I had the following ports open in the firewall:

  1. Port 135 both ways (for RPC)
  2. The dynamic ports 49152-65535

MSDTC starts talking on 135 and then jumps to a dynamic port.  I think the firewall people may have done something fancier but that’s what I told them.

I also had to:

  1. Enable the three inbound Windows Firewall rules for Distributed Transaction Coordinator
  2. Enable the one outbound Windows Firewall rule for Distributed Transaction Coordinator

Those rules exist but they were disabled by default in my environment.

Configure MSDTC Permissions

Using DCOMCNFG.EXE I had to enable the following permissions in MSDTC.

  1. Network DTC Access
  2. Allow Inbound
  3. Allow Outbound
  4. No Authentication Required – This one was a little frustrating.  I’d prefer to have them authenticate but I haven’t worked on that enough yet.

There are screenshots of this all over the web that you’ve probably already found.


At this point you should be able to get DTCPing to work.  You run it on both sides and they talk to each other simulating a transaction.  It give good error messages in the application.  It also writes a text file with more detail diagnostic logging.  It’s a very handy utility.  If you get any errors you’ll need to work through those.


This little command-line utility actually runs a distributed transaction.  It creates a temporary table and and inserts a row into it.  You’ll need to configure a 32-bit ODBC entry.  Look for C:\Windows\SysWOW64\odbcad32.exe to make that entry.   The 64-bit utility is in C:\Windows\System32 and has the same name.  That’s some confusing naming right there.  You’ll also need a SQL Server login and password.  If you’re going to test against multiple machines your ODBC source should point to a hosts file entry.  It makes testing much easier.

posted @ Saturday, December 31, 2016 9:11 AM | Feedback (0) |

Monday, December 19, 2016

Is It SQL now shows Availability Groups

The latest release of Is It SQL is out and includes a variety of features.  I’d like to start with some screen shots of previous features.  First up is database mirroring.  You can see the mirroring status of a database when looking at the databases for a particular server.


For each database it will show the role, whether it’s synchronized or not, and whether the safety is on or off (synchronous vs. asynchronous).  It will also show the send and redo queue for the database. 

If you sign up for the newsletter (on the Is It SQL page) it will send instructions on enabling the Enterprise features.  That includes a page showing all mirrored databases across all servers you’re monitoring.  That page includes a “Priority” column that brings any database with issues to the top.  If it’s disconnected or has a send or redo backlog it will bring it to the top of the list.  If you have instances under multiple names, for example static DNS entries pointing to mirroring partners, it only shows each database once.  That gives you a quick way to see all mirrored databases across your enterprise.

All tables are sortable by clicking on their heading.  So you can sort these pages by the send queue or redo queue or the priority value I calculate or the database size or server or any other column.  The size of the log also moved to its own column so it’s sortable now.


All the data is polled every minute from the servers and then displayed back on the web page.  The pages auto-refresh every minute.  Some data is polled in real-time when you refresh the page.  This is indicated with a cool little lightning bolt by the section.


The availability group monitoring is still pretty basic at this point.  After you add the nodes to be monitored it discovers any availability groups and displays them all together on a page.  The data comes from the AG DMV’s.  It is nice to have them all in one place and as easy to access as a web page.


The list of servers was just showing the bytes per second read and written for disk I/O.  I’ve expanded this to include the IOPS, average I/O size, and the average latency.  It includes that for both reads and writes.  318ms reads.  Yuck.  I hope yours are better!


At the bottom of each list of servers there’s a summary.  It shows the total disk I/O, batches per second, RAM used, data file size and log files size.  If instances are included multiple times it only includes it once.  This also works for various tags.  For example, I’ve tagged servers based on their data center so it’s easy to see how much traffic each data center is taking.

And that’s where we are so far.  It’s a handy little utility all in a single 12MB executable.  It’s easy to run as a service so it’s always available.  I encourage you to sign up for the newsletter.  It includes information on enabling the Enterprise features, tips and tricks, and new releases.  You can download it from the Is It SQL page. 

posted @ Monday, December 19, 2016 8:02 AM | Feedback (2) | Filed Under [ Utilities ]

Thursday, November 10, 2016

Database Mirroring in Is It SQL

Release 1.0.22 is out and available at the Is It SQL home page.  The biggest feature here is database mirroring. 

There are two ways to see database mirroring.  The first is on a server page it will show the databases that are mirrored.  There is also a global database mirroring page that shows all mirrored databases.  There’s an added column named “priority” that highlights databases that aren’t connected and synchronized.  It also shows shows databases that have send or receive queues.  Since the column is sortable it should always show databases with “stuff” going on at the top.

Please note that you’ll need to sign up for the newsletter for instructions on how to enable the beta features.  You can find the link on Is It SQL page.

Data that is pulled from servers when the page is refreshed is now identified with a little lightning bolt.  Most data is cached every minute but some data refreshes with each page load.  This helps identify which is which.  The basic rule of thumb is that everything is cached except what has the lightning bolt.

posted @ Thursday, November 10, 2016 3:40 PM | Feedback (1) | Filed Under [ Utilities ]

Tuesday, September 20, 2016

Tagging Servers in Is It SQL

Tag-pageRelease 1.0.20 is out.  You can download it from my corporate site at This release adds a beta feature to allow you tag servers and then filter the list by tag.

Instructions for enabling beta features are available when you sign up for the newsletter.  The newsletter is limited to Is It SQL features and releases.

The current release automatically tags servers based on their version.  This release also tags servers based on their domain too.  It allows you to enter user-defined tags for each server.  Common tags I’ve been using include development, test, production, disaster recovery, physical location, virtual vs. physical, application support, reporting, SSAS, SSRS, etc.

When I first starting writing this tool and using it I mostly focused on the key production servers.  While testing this feature I’ve gradually added more servers to it.  It’s been very helpful keeping an eye on servers I rarely look at but seem to have weird issues (cough, dev boxes, cough).  Tagging allows me to filter those out of my main list but still be able to find them when I need them.

posted @ Tuesday, September 20, 2016 8:40 AM | Feedback (0) | Filed Under [ Utilities ]

Tuesday, August 30, 2016

IsItSql Shows Databases

You can download the latest build of Is It SQL from my consulting site.  You can now view the databases on a server.  And you can click on a column header to sort that column.


And no, my fantasy baseball team in 2015 didn’t do very well.  Unfortunately this year is much the same.   

Here are a few other improvements:

  • The Active Task page for a server no longer displays tasks waiting on BROKER_RECEIVE_WAITFOR.
  • The menu bar stays on top when scrolling down.  And I’m really surprised how much this little change has made the whole application so much more usable.
  • There’s better error reporting if you launch the application and the port is already in use.  I mainly see this when I launch the application and it’s already running as a service.
  • It prioritizes ODBC 13 over ODBC 11.  And it will gracefully fail back to earlier drivers until it finds one it can use.

If you’re running as a service, just stop the service, copy over IsItSql.EXE, and restart the service.  There’s never any installation needed.  And my email is at the bottom of the README if you have any questions or concerns.

posted @ Tuesday, August 30, 2016 10:58 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Thursday, August 04, 2016


This release includes a few minor bug fixes and smaller enhancements.  Thanks to everyone that tried it out and reported back.  At the bottom of the README.html I include my email address if you’d like to provide feedback.  I’d love to hear what you like or what needs work.

One of my goals for this application was to know if a server was unreachable or it returned an error running any of the polling queries.  These servers show up in red in the list on the main page.  They are also displayed at the top of every page in a nice red box.  Previously some pages didn’t display the servers with errors at the top.  One page even displayed them twice.  I guess it really, really wanted you to fix whatever the issue was.

I also excluded sessions running a WAITFOR command from the active sessions that are displayed when you look at the detail for a server.  A number of system SPIDs seem to use it.  And it seems a bit of my code does too.  I’m finding I don’t want to see it more than I want to see it.  I’m not sure this is the right answer and I’ll revisit it one of these days. 

The database size was a simple sum of sys.master_files which included snapshots.  Which ballooned the size reported if you had a lot of snapshots.  For now I’ve excluded snapshots from the size total.  I’m hoping in the next release to add back the actual size on the disk from the snapshot.  That’s what I want to see anyway.

You might not have noticed but the columns in the main page are sortable.  Just click on a header and they’ll sort.  They also remember their sort order across refreshes.  I changed a few of the columns to sort in descending order by default.  So CPU percentage and database size will show the largest values first.  I also need some type of icon indicating that these columns sort and what the order is but that’s going to be a future enhancement.

That’s it for this release.  The install should be as simple as stopping the service, copying over the executable and restarting the service.  If you have any issues, email me at the address at the bottom of the README.  You can download the latest build at

posted @ Thursday, August 04, 2016 4:44 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Tuesday, August 02, 2016

Is It SQL?

I’ve been working on a simple monitoring tool for SQL Server over the last few months.  I often find myself asking “What just happened?” and “Was it SQL Server?”.  I wanted a small utility I could leave running at clients that would answer that question.  It needed to be simple enough that a non-SQL Server person could look at it and get an idea if there was an issue with SQL Server.  But also sophisticated enough that it would point me in the right direction.


It will capture the following information for each server every minute:

  • CPU Usage for SQL Server and non-SQL Server tasks
  • Batch requests per second
  • Disk Read and Write volume and Page Life Expectancy
  • The largest waits grouped together.  For example, the PAGEIO* waits are all grouped together as Disk IO waits.  You can customize this mapping for your environment.
  • It also captures some basic information about the server including the version, restart date and aggregate database size.


When you bring up a server page it will show the currently running queries and the last 60 minutes of the metrics above.  When someone reports an issue I can look at the last hour for any server with just two clicks.  And quickly answer: “Is It SQL Server?”

I also wanted this to be very simple to use and install.  It can run as a simple command line executable.  It doesn’t require .NET or Java or even SQL Server.  The only requirement is some flavor of ODBC driver for SQL Server which nearly every server has.  It hosts a self-contained web server that displays the information you’re looking for.  It includes an option to install itself as a service so it runs in the background and is always available.  Adding servers is as simple as putting their names into a text file.

All the big servers I monitor already have a fancy monitoring solution.  But I’ve never been happy with the solutions for the second tier of servers or the smaller clients that won’t buy a dedicated monitoring solution.  So this is what I’ve been using. 

You can download this at  There’s a README document that will help you get started.

posted @ Tuesday, August 02, 2016 8:22 AM | Feedback (7) | Filed Under [ SQL Server Stuff Utilities ]

Tuesday, July 12, 2016

ClearTrace Build 51 Available

I’m pleased to announce that ClearTrace Build 51 is now available for download.  If you’re using a previous build (especially build 50) I encourage you to download this version.

This build should load the latest version of the SMO libraries installed on your machine.  If you’re using SQL Server 2016, you need the SQL Server engine AND SQL Server Management Studio installed.  I believe this is the only way to get the complete SMO library.  This also means that when SQL Server 2036 is released it will automatically support it – and hopefully all the releases between now and then.

This version has a new sort named “CPU & Reads”.  It sorts based on the rank of each statement for CPU and Reads.  If you choose to display the top 15 statements and choose this sort you will get the top 7 of CPU and the top 7 of Reads.  Assuming there will be some overlap it will then fill up to 15 with using the highest ranking statements.  Or to put it in terms you might better understand, the ORDER BY looks like this:

    WHEN [CpuRank] < [ReadsRank] THEN [CpuRank]
    ELSE [ReadsRank]
END , [CpuRank], [ReadsRank]

I’ve found this brings the most interesting statements to the top.

The build also fixes a small parsing bug for statements that look like this: EXEC sp_executesql @statement=N'SELECT 1'.  The combination of EXEC and sp_executesql was making the actual statement disappear.  That’s fixed.

If you find any issues, please post them in the ClearTrace Support Forums.

Thanks and Happy Tracing!

posted @ Tuesday, July 12, 2016 5:44 PM | Feedback (0) | Filed Under [ ClearTrace ]

Thursday, December 10, 2015

Installing SQL Server 2014 yields Unauthorized Operation

We were experiencing trouble installing SQL Server 2014 onto a Windows 2012 R2 VM.  These are new production boxes so I was being very careful.  The problem manifest itself in two ways:

  • A clean installation using our service account got almost all the way through and then failed with the Unauthorized Operation message.  It was painful to get out of the installer.  I would just hang on a cancel and then eventually exit.
  • Installing using Local System worked but then we couldn’t switch to use our service account.  We received the same error.  This error included an 0x80070005 error code.

I have a number of other SQL Server 2014 installations on Windows 2012 R2 so this surprised me.  The detailed error log included this:

Slp: Sco: Attempting to open service handle for service MSSQLSERVER
Slp: Prompting user if they want to retry this action due to the following failure:
Slp: ----------------------------------------
Slp: The following is an exception stack listing the exceptions in outermost to innermost order
Slp: Inner exceptions are being indented
Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
Slp:     Message: 
Slp:         Attempted to perform an unauthorized operation.
Slp:     HResult : 0x84bb0001
Slp:         FacilityCode : 1211 (4bb)
Slp:         ErrorCode : 1 (0001)
Slp:     Data: 
Slp:       DisableRetry = true
Slp:     Inner exception type: System.UnauthorizedAccessException
Slp:         Message: 
Slp:                 Attempted to perform an unauthorized operation.
Slp:         HResult : 0x80070005
Slp:         Stack: 
Slp:                 at Microsoft.SqlServer.Configuration.Sco.Service.StartService(String[] startParams)
Slp: ----------------------------------------
Slp: User has chosen to retry this action


A search through the Internet revealed a number of possibilities.  I tried to run the installation as Administrator.  I wasn’t very hopeful on that since the first thing the installer does is pop up the UAC prompt.  Someone also suggested disabling the UAC functionality.  I didn’t try that.  We also copied the installation media of the mounted DVD into a regular directory.  That didn’t help either.

We finally discovered that the team that preps the VMs was now removing both “ABC\Domain Users” and “Authenticated Users” from the local “Users” group.  And that was the issue.  Putting the service account back in that group fixed the issue. 

posted @ Thursday, December 10, 2015 12:32 PM | Feedback (3) | Filed Under [ SQL Server Stuff ]

Sunday, July 05, 2015

ClearTrace Build 47 Available

You can download the latest version of ClearTrace from my consulting site: scaleSQL Consulting

I’ve decided to stop using the year as part of the version.  I did that to match SQL Server branding but it doesn’t work for ClearTrace.  The current build supports SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.  It tries to load the most recent SMO and goes backwards until it successfully loads an SMO library.  Always use the most recent version of ClearTrace and it should support any current SQL Server.

If you’re having issues with ClearTrace you can visit the new ClearTrace Support Forums.  That’s the best place to ask questions or report bugs.

imageThe latest build adds a number of features.  The first is an option that  doesn’t reprocess trace files.  I regularly generate large traces that have many files and I’m too impatient to wait for them all to finish.  The script I use generates a unique name for each trace file.  This option allows me to skip the trace files that have already been loaded.

I also fixed a pseudo-memory leak that had been hanging around for a LONG time.  One of my early uses of ClearTrace was for a client that had lots of prepared SQL.  In the trace file I would find lots of sp_execute 32423445 or some such.  That number identifies the prepared SQL statement.  ClearTrace captures the statement that prepares that SQL and reports back the actual SQL executed.  This was all being stored in memory.  Either I wasn’t clearing it properly or I didn’t understand how it got cleared but this structure was ballooning up.  I’d regularly fill up memory to just under 2GB and watch the application crash.  Now the application clears this structure after each trace file.  If you want better handling of prepared SQL just increase the trace file size.  That will capture more of the prepared SQL statements.

Trace Groups allow you to group traces together.  They are especially helpful to view changes over time.  I typically have one trace group per server.  I’ve seen users that have dozens and dozens of trace groups with all kinds of fancy names.  So I made the Trace Group Name drop down wider and hopefully easier to work with.

imageIf you capture statement level traces and you have lots of nested stored procedures you’ll see lots of EXEC statements in your list of statements.  If you end up a few layers deep you can see LOTS of EXEC statements.  So I added an entry to the Events drop down filter to exclude statements that start with EXEC.  All it does is eliminate anything that starts with “EXEC”.  So far it’s been helpful. 

I also fixed the history tab to work better with statements.  And by “better” I mean it actually works now.  You should get a statements history without the application blowing up.

(And if you downloaded build 46 please update to build 47.  It’s just minor bug fixes around how statements are displayed in the grid.  Or not displayed as was happening in some situations.)

And that’s it.  Give it a try and let me know any issues in the ClearTrace forums.

posted @ Sunday, July 05, 2015 11:06 PM | Feedback (0) | Filed Under [ ClearTrace ]

Wednesday, October 08, 2014

Error: 18456, Severity: 14, State: 11

I’ve seen this error off and on in our environment for a while.  The full error looks like this:

Login failed for user DOMAIN\login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: X.X.X.X]
Error: 18456, Severity: 14, State: 11.

Aaron Bertrand wrote the post I always start with on troubleshooting 18456 errors.  I’ve always struggled tracking down the root cause of state 11 though.  I’ve seen it intermittently occur and then clean itself up.  One of the reported causes is difficultly connecting to a domain controller so that kind of fits. 

But this time it was occurring and not going away.  People have suggested deleting and recreating the login, running as Administrator, disabling UAC, etc.

In our case, the login had been given permission through a Windows Group.  That group had been removed, thus the user no longer had permission.  But rather than give a standard login failure, it gave the state 11 error.  That left me heading down a rabbit hole until xp_logininfo cleared up that they just didn’t have access.  My only guess is that because they’d logged in before there was some artifact left on the system and the result was a slightly different login failure error message.

I’m hoping this might help the next person tracking down the dreaded state 11.

posted @ Wednesday, October 08, 2014 2:20 PM | Feedback (0) | Filed Under [ SQL Server Stuff ]

Monday, July 14, 2014

SQL Saturday 300 BBQ Crawl

SQL Saturday #300 is coming up right here in Kansas City on September 13th, 2014.  This is our fifth SQL Saturday which means it's the fifth anniversary of our now infamous BBQ Crawl.  We get together on Friday afternoon before the event and visit a few local joints.  We've done nice places and we've done dives.  We haven’t picked the venues yet but I promise you’ll be well fed!

And if you’re thinking about the BBQ crawl you should think about submitting a session.  Our call for speakers closes Tuesday, July 15th so you just have time! 

If you’re going to be at the event, contact me and I’ll get you added to the list.

posted @ Monday, July 14, 2014 6:52 PM | Feedback (1) | Filed Under [ Events PASS ]

Thursday, January 10, 2013

PASS: Bylaw Change 2013

PASS Board MeetingPASS launched a Global Growth Initiative in the Summer of 2011 with the appointment of three international Board advisors.  Since then we’ve thought and talked extensively about how we make PASS more relevant to our members outside the US and Canada.  We’ve collected much of that discussion in our Global Growth site.  You can find vision documents, plans, governance proposals, feedback sites, and transcripts of Twitter chats and town hall meetings.  We also address these plans at the Board Q&A during the 2012 Summit.

One of the biggest changes coming out of this process is around how we elect Board members.  And that requires a change to the bylaws.  We published the proposed bylaw changes as a red-lined document so you can clearly see the changes. 

Our goal in these bylaw changes was to address the changes required by the global growth initiatives, conduct a legal review of the document and address other minor issues in the document.  There are numerous small wording changes throughout the document.  For example, we replaced every reference of “The Corporation” with the word “PASS” so it now reads “PASS is organized…”.

Board Composition

The biggest change in these bylaw changes is how the Board is composed and elected.  This discussion starts in section VI.2.  This section now says that some elected directors will come from geographic regions.  I think this is the best way to make sure we give all of our members a voice in the leadership of the organization.  The key parts of this section are:

The remaining Directors (i.e. the non-Officer Directors and non-Vendor Appointed Directors) shall be elected by the voting membership (“Elected Directors”). Elected Directors shall include representatives of defined PASS regions (“Regions”) as set forth below (“Regional Directors”) and at minimum one (1) additional Director-at-Large whose selection is not limited by region. Regional Directors shall include, but are not limited to, two (2) seats for the Region covering Canada and the United States of America.

Additional Regions for the purpose of electing additional Regional Directors and additional Director-at-Large seats for the purpose of expanding the Board shall be defined by a majority vote of the current Board of Directors and must be established prior to the public call for nominations in the general election. Previously defined Regions and seats approved by the Board of Directors shall remain in effect and can only be modified by a 2/3 majority vote by the then current Board of Directors.

Currently PASS has six At-Large Directors elected by the members.  These changes allow for a Regional Director position that is elected by the members but must come from a particular region.  It also stipulates that there must always be at least one Director-at-Large who can come from any region.

We also understand that PASS is currently a very US-centric organization.  Our Summit is held in America, roughly half our chapters are in the US and Canada and most of the Board members over the last ten years have come from America.  We wanted to reflect that by making sure that our US and Canadian volunteers would continue to play a significant role by ensuring that two Regional seats are reserved specifically for Canada and the US.

Other than that, the bylaws don’t create any specific regional seats.  These rules allow us to create Regional Director seats but don’t require it.  We haven’t fully discussed what the criteria will be in order for a region to have a seat designated for it or how many regions there will be.  In our discussions we’ve broadly discussed regions for

  • United States and Canada
  • Europe, Middle East, and Africa (EMEA)
  • Australia, New Zealand and Asia (also known as Asia Pacific or APAC)
  • Mexico, South America, and Central America (LATAM)

As you can see, our thinking is that there will be a few large regions.  I’ve also considered a non-North America region that we can gradually split into the regions above as our membership grows in those areas. 

The regions will be defined by a policy document that will be published prior to the elections. I’m hoping that over the next year we can begin to publish more of what we do as Board-approved policy documents.

While the bylaws only require a single non-region specific At-large Director, I would expect we would always have two.  That way we can have one in each election.  I think it’s important that we always have one seat open that anyone who is eligible to run for the Board can contest.  The Board is required to have any regions defined prior to the start of the election process.

Board Elections – Regional Seats

We spent a lot of time discussing how the elections would work for these Regional Director seats.  Ultimately we decided that the simplest solution is that every PASS member should vote for every open seat.  Section VIII.3 reads:

Candidates who are eligible (i.e. eligible to serve in such capacity subject to the criteria set forth herein or adopted by the Board of Directors) shall be designated to fill open Board seats in the following order of priority on the basis of total votes received: (i) full term Regional Director seats, (ii) full term Director-at-Large seats, (iii) not full term (vacated) Regional Director seats, (iv) not full term (vacated) Director-at-Large seats. For the purposes of clarity, because of eligibility requirements, it is contemplated that the candidates designated to the open Board seats may not receive more votes than certain other candidates who are not selected to the Board.

We debated whether to have multiple ballots or one single ballot.  Multiple ballot elections get complicated quickly.  Let’s say we have a ballot for US/Canada and one for Region 2.  After that we’d need a mechanism to merge those two together and come up with the winner of the at-large seat or have another election for the at-large position. 

We think the best way to do this is a single ballot and putting the highest vote getters into the most restrictive seats.  Let’s look at an example:

There are seats open for Region 1, Region 2 and at-large.  The election results are as follows:

  • Candidate A (eligible for Region 1) – 550 votes
  • Candidate B (eligible for Region 1) – 525 votes
  • Candidate C (eligible for Region 1) – 475 votes
  • Candidate D (eligible for Region 2) – 125 votes
  • Candidate E (eligible for Region 2) – 75 votes

In this case, Candidate A is the winner for Region 1 and is assigned that seat.  Candidate D is the winner for Region 2 and is assigned that seat.  The at-large seat is filled by the high remaining vote getter which is Candidate B.

The key point to understand is that we may have a situation where a person with a lower vote total is elected to a regional seat and a person with a higher vote total is excluded.  This will be true whether we had multiple ballots or a single ballot. 

Board Elections – Vacant Seats

The other change to the election process is for vacant Board seats.  The actual changes are sprinkled throughout the document.

Previously we didn’t have a mechanism that allowed for an election of a Board seat that we knew would be vacant in the future.  The most common case is when a Board members moves to an Officer role in the middle of their term.  One of the key changes is to allow the number of votes members have to match the number of open seats.  This allows each voter to express their preference on all open seats.  This only applies when we know about the opening prior to the call for nominations.  This all means that if there’s a seat will be open at the start of the next Board term, and we know about it prior to the call for nominations, we can include that seat in the elections.  Ultimately, the aim is to have PASS members decide who sits on the Board in as many situations as possible.

We discussed the option of changing the bylaws to just take next highest vote-getter in all other cases.  I think that’s wrong for the following reasons:

  • All voters aren’t able to express an opinion on all candidates.  If there are five people running for three seats, you can only vote for three.  You have no way to express your preference between #4 and #5.
  • Different candidates may have different information about the number of seats available.  A person may learn that a Board member plans to resign at the end of the year prior to that information being made public. They may understand that the top four vote getters will end up on the Board while the rest of the members believe there are only three openings.  This may affect someone’s decision to run.  I don’t think this creates a transparent, fair election.
  • Board members may use their knowledge of the election results to decide whether to remain on the Board or not.  Admittedly this one is unlikely but I don’t want to create a situation where this accusation can be leveled.

I think the majority of vacancies in the future will be handled through elections.  The bylaw section quoted above also indicates that partial term vacancies will be filled after the full term seats are filled.

Removing Directors

Section VI.7 on removing directors has always had a clause that allowed members to remove an elected director.  We also had a clause that allowed appointed directors to be removed.  We added a clause that allows the Board to remove for cause any director with a 2/3 majority vote.  The updated text reads:

Any Director may be removed for cause by a 2/3 majority vote of the Board of Directors whenever in its judgment the best interests of PASS would be served thereby.

Notwithstanding the foregoing, the authority of any Director to act as in an official capacity as a Director or Officer of PASS may be suspended by the Board of Directors for cause.

Cause for suspension or removal of a Director shall include but not be limited to failure to meet any Board-approved performance expectations or the presence of a reason for suspension or dismissal as listed in Addendum B of these Bylaws.

The first paragraph is updated and the second and third are unchanged (except cleaning up language).  If you scroll down and look at Addendum B of these bylaws you find the following:

Cause for suspension or dismissal of a member of the Board of Directors may include:

  • Inability to attend Board meetings on a regular basis.
  • Inability or unwillingness to act in a capacity designated by the Board of Directors.
  • Failure to fulfill the responsibilities of the office.
  • Inability to represent the Region elected to represent
  • Failure to act in a manner consistent with PASS's Bylaws and/or policies.
  • Misrepresentation of responsibility and/or authority.
  • Misrepresentation of PASS.
  • Unresolved conflict of interests with Board responsibilities.
  • Breach of confidentiality.

The bold line about your inability to represent your region is what we added to the bylaws in this revision.  We also added a clause to section VII.3 allowing the Board to remove an officer.  That clause is much less restrictive.  It doesn’t require cause and only requires a simple majority.

The Board of Directors may remove any Officer whenever in their judgment the best interests of PASS shall be served by such removal.


There are numerous other small changes throughout the document.

Proxy voting.  The laws around how members and Board members proxy votes are specific in Illinois law.  PASS is an Illinois corporation and is subject to Illinois laws.  We changed section IV.5 to come into compliance with those laws.  Specifically this says you can only vote through a proxy if you have a written proxy through your authorized attorney. 

English language proficiency.  As we increase our global footprint we come across more members that aren’t native English speakers.  The business of PASS is conducted in English and it’s important that our Board members speak English.  If we get big enough to afford translators, we may be able to relax this but right now we need English language skills for effective Board members.

Committees.  The language around committees in section IX is old and dated.  Our lawyers advised us to clean it up.  This section specifically applies to any committees that the Board may form outside of portfolios.  We removed the term limits, quorum and vacancies clause.  We don’t currently have any committees that this would apply to.  The Nominating Committee is covered elsewhere in the bylaws.

Electronic Votes.  The change allows the Board to vote via email but the results must be unanimous.  This is to conform with Illinois state law.

Immediate Past President.  There was no mechanism to fill the IPP role if an outgoing President chose not to participate.  We changed section VII.8 to allow the Board to invite any previous President to fill the role by majority vote.

Nominations Committee.  We’ve opened the language to allow for the transparent election of the Nominations Committee as outlined by the 2011 Election Review Committee.

Revocation of Charters. The language surrounding the revocation of charters for local groups was flagged by the lawyers. We have allowed for the local user group to make all necessary payment before considering returning of items to PASS if required.

Bylaw notification. We’ve spent countless meetings working on these bylaws with the intent to not open them again any time in the near future. Should the bylaws be opened again, we have included a clause ensuring that the PASS membership is involved. I’m proud that the Board has remained committed to transparency and accountability to members. This clause will require that same level of commitment in the future even when all the current Board members have rolled off.

I think that covers everything.  I’d encourage you to look through the red-line document and see the changes.  It’s helpful to look at the language that’s being removed and the language that’s being added.  I’m happy to answer any questions here or you can email them to

posted @ Thursday, January 10, 2013 1:20 PM | Feedback (1) | Filed Under [ PASS ]

Tuesday, October 09, 2012

SQL Server Configuration Scripting Utility Release 9

There’s another update to my little utility to script a SQL Server’s configuration.  I use this for two purposes.  First, I use it to keep my database mirroring servers up to date.  Second, I capture the output in a version control system and keep that for historical reference.

In release 3.0.9 I made the following changes:

  • Rewrote the encrypted trigger scripting.  It will now list the encrypted triggers in a comment in the table script but can’t actually script them.
  • It now scripts any server event notifications.
  • You can script a single database using the /scriptdb flag.  Please note that it will also script the instance and system databases when it does this.
  • It will script any user-defined endpoints.  This will capture your mirroring endpoints and more importantly any service broker endpoints.
  • It will gracefully skip database mail on the Express Edition.

It still doesn’t support SQL Server 2012.  I think that’s the next feature to add though.

posted @ Tuesday, October 09, 2012 5:59 PM | Feedback (0) | Filed Under [ Utilities ]

Tuesday, July 10, 2012

Another Update to SQL Server Configuration Scripting Utility

I’ve been gradually adding features to my utility that scripts the configuration of a SQL Server.  Since my last post I’ve added the following features:

  • Skip any encrypted object in a database
  • Script alerts, alert notifications and operators
  • Script audits
  • Always script model, master and msdb to capture any user-defined objects in those databases
  • Logins are now scripted so that everything for a login is grouped together.
  • There’s a second section in the logins that handles default databases.  In many cases a login’s default database is a mirror target and can’t be set.  This is now handled gracefully.  It also includes a separate section for all default databases so those can be quickly set in the event of a disaster.
  • Script credentials
  • Script proxy accounts
  • Script database mail

My goal is still to get everything outside a database scripted.  This release is enough that I can keep my mirror target servers in sync with their principals.

posted @ Tuesday, July 10, 2012 4:37 PM | Feedback (0) | Filed Under [ Utilities ]

Powered by:
Powered By Subtext Powered By ASP.NET