posts - 227, comments - 420, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

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 http://www.scalesql.com/isitsql/. 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.

database-page

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

IsItSql–1.0.18

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 www.scalesql.com/downloads/isitsql.1.0.18.zip

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.

 index-page

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.

server-page

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 http://www.scalesql.com/downloads/isitsql.1.0.17.zip.  There’s a README document that will help you get started.

posted @ Tuesday, August 02, 2016 8:22 AM | Feedback (6) | 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:

CASE
    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: 
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.

Other

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 feedback@sqlpass.org

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 ]

Thursday, June 21, 2012

PASS: International Travels

Nihao!  One of the largest changes PASS is going through is the the expansion outside the US and Canada.  We’ve had international chapters and events in Europe since the early 2000’s.  But nothing on the scale we’re seeing now.  Since January 1st there have been 18 SQL Saturday events outside North America and 19 events in North America.  We hope to have three international SQLRally events outside the US in FY13 (budget willing).  I don’t know the exact percentage of chapters outside the US but it’s got be 50% or higher.

We recently started an effort to remake the Board to better reflect the growing global face of PASS.  This involves assigning some Board seats to geographic regions.  You can ask questions about this in our feedback forum, participate in a Twitter chat or ask questions directly of Board members.  You can email me at if you’d like to ask a question directly.  We’re doing this very slowly and deliberately in hopes that a long communication cycle gives us a chance to address all the issues that our members will raise.

Bill Graziano at SQL Saturday Silicon Valley 2012After the Summit we passed a budget exception allocating an extra $20,000 for Board members to travel to local events.  I think it’s important for Board members to visit new areas and talk to more of our members.  I sent out an email asking where people had attended events outside their home city.  Here’s the list I got back: Albuquerque, Amsterdam, Boston, Brisbane, Chicago, Colorado Springs, Columbus, Dallas, Houston, Jacksonville, Las Vegas, London, Louisville, Minneapolis, New York City, Orange County, Orlando, Pensacola, Perth, Philadelphia, Phoenix, Redmond, Seattle, Silicon Valley, Sydney, Tampa Bay, Vancouver, Washington DC and Wellington.  (Disclaimer: Some of this travel was paid for by employers or Board members themselves.  Some of this travel may have been completed before the Summit.  That’s still one heck of a list!)

The last SQL Saturday event this fiscal year is SQL Saturday Shanghai.  And that’s one I’m attending.  This is our first event in China and is being put on in cooperation with the local Microsoft office.  Hopefully this event will be the start of a growing community in China that includes chapters, SQL Saturdays and maybe a SQLRally or two in the future.  I’m excited to speak with people that are just starting down this path and watching this community grow.

I encourage you to visit the PASS Global Growth site and read through the material there.  This is the biggest change we’ve made to our governance since I’ve been on the Board.  You need to understand how it affects you and how it affects the organization.

And wish me luck on the 15 hour flight to Shanghai on Friday afternoon.  Rob Farley flies from Australia to the US for PASS events multiple times per year and I don’t know how he does it so often.  I think one of these is going to wipe me out.  (And Nihao (knee-how) is Chinese for Hello.)

posted @ Thursday, June 21, 2012 3:16 PM | Feedback (0) | Filed Under [ PASS ]

Wednesday, June 20, 2012

Kansas City SQL Saturday 2012: BBQ Crawl

The next Kansas City SQL Saturday is coming up on August 4th.  We’ll have the usual SQL Saturday goodness: lots of technical sessions, great networking events and a fantastic speaker dinner.  And we’ll have the Third Annual Kansas City SQL Saturday BBQ Crawl. 

IMG_0264On Friday afternoon we’ll visit a few BBQ places in town.  We tend to order big sampler plates and just share everything around.  It’s a great way to try a variety of styles.  This year we’ll be hitting an all new selection of BBQ joints.

You don’t need to be a speaker to attend.  However the call for speakers is open until June 28th (hint, hint).  Locals and out-of-towners are all welcome.

If you’re interested in attending send me an email and I’ll get you added to the list.

We finish in plenty of time to get you to the speaker dinner – as if you could eat any more.

posted @ Wednesday, June 20, 2012 7:43 PM | Feedback (0) | Filed Under [ Events ]

Tuesday, June 12, 2012

ClearTrace for SQL Server 2012

I’ve updated the beta for ClearTrace that support SQL Server 2012.  This requires SQL Server 2012 to be installed on the computer where ClearTrace is running.  It will read traces from SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005.

It includes some minor improvements in performance and handling large SQL statements. It should also give better errors.

If you do find any of those errors, please report them in the support forum.

posted @ Tuesday, June 12, 2012 7:23 AM | Feedback (1) | Filed Under [ ClearTrace ]

Powered by:
Powered By Subtext Powered By ASP.NET