An approach to SQL Server Rough Tuning

Looking for a way to address SQL Server database performance in a production, virtualized environment?  There are many sources of expert advice from very smart people in the SQL Server world.  But often the most well-thought, well-intentioned advice is not easily or quickly implemented in a complex organization.  The reasons could be technical, political or simply availability of time and people. 

This diagram is my current approach to "rough" tuning a SQL Server: The idea that a server administrator or database administrator (DBA) can turn various knobs and flip switches to assigned scarce resources to a database server, but as a practical matter, the inner workings of a given application and database may not be changed… or at least not changed quickly. 

Example situations:

• A third party vendor application with a proprietary schema, the application might have updates, but there are dependencies or license costs that take time.
• An internal application has received an influx of new activity, but the development team is fully off on another high-priority project
• A legacy application with original developers long gone; no known test environment to experiment

Method

Took some best practices, including some selections from the guidance on the SQL Server perfmon counters of interest poster available from Dell/Quest, and added some of the basic steps available to Server administrators and Database Administrators.

Document available as a PDF

SQL Server Data Tools - An Installation Adventure

With SQL Server 2012, your old friend "Business Intelligence Developer Studio" or "BIDS" has been replaced by a component called "SQL Server Data Tools".  SQL Server Data Tools, or SSDT is the primary authoring environment for SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS) projects.  In addition, a new way of working with SQL Server Databases is available.  The new method treats a SQL Server database as a Visual Studio project, enabling adaption of developer concepts like version control and deployment.  

While SSDT is a very innovative product, the steps to arrive at a working installation of SSDT can be a bit of an adventure.  If you have installed SQL Server 2012 from installation media, selecting all features, or at least selected all client tools, your adventure begins here.  

An entry for SQL Server Data Tools will appear in your Start menu, under the folder for "Microsoft SQL Server 2012"

Click on SQL Server Data Tools and you will soon see that the "Microsoft Visual Studio 2010 Shell" is starting up.

You will be promoted to select environment settings.  If you used SQL Server 200X's Business Intelligence Developer Studio or plan to focus this machine on SQL Server Business Intelligence projects, select "Business Intelligence Settings".  Click 'Start Visual Studio'.

After a potentially brief pause, and this loading screen...

… and you are brought into the 'Microsoft Visual Studio 2010 Shell"

Click on New Project…

Now you can actually create SSAS, SSIS, or SSRS projects at this point.  You are good to go as far as that feature is concerned.  Then check out the new option "SQL Server"…

Note the text "Provides instructions for installing Microsoft SQL Server Data Tools from the web".  Clicking this link presents this message...

Clicking "Install" opens this web page...

If you click on 'Download SQL Server Data Tools', as of the time of this writing, you're taken to a page about downloading Visual Studio 2013 trial editions.  I went this route on another machine with Visual Studio 2013 installed, so far, haven't quite figured out how to get SSDT enabled, so...

Click back…

And click SSDT Visual Studio 2012

On the next page, scroll about halfway down to step 2 and click 'Download SQL Server Data Tools for Visual Studio 2012'

For a single installation, go ahead and click 'Run'.  (To save the file for use on other workstations, click 'Save')

Then thoroughly read the License Terms, and if they are amenable, click 'I agree…' and 'Install'

The Microsoft .NET Framework 4.5.  Your environment may vary.

Restart is required if the .NET Framework 4.5 is installed

After the restart, Setup will continue

After this, the SQL Server Data project will be available under 'SQL Server Data Tools', right?  No, to access the SQL Server tools, they're under 'Microsoft Visual Studio 2012'

The 'SQL' menu  with the 'Schema Compare' and 'Data Compare' are now available.

The SQL Server Data Tools look very compelling.  As I'm help my organization migrate to SQL Server 2012, the new features will replace some work that currently requires manual processes or intricate scripting.  Perhaps the install story will tighten up as SQL Server 2014 is released or the Visual Studio 2013 version becomes a bit more clear to me  It is a bit of an adventure to install - nothing difficult, just unclear at some steps the progress towards a working installation.  Hopefully, this blog post helps you be more confident when you decide to start using SQL Server Data Tools.

IT Project Staffing for Emerging and Legacy applications

I've been catching up in the past week or on the Oregon Health Exchange ("Covered Oregon") issues.  This apparently started popping off in November 2013.  There was a recent spate of articles on GovTech regarding the oversight.

The Cover Oregon Website Implementation Assessment by First Data contained an interesting nugget in their recommendations regarding IT Project Staffing:

IT Project Staffing - The exchange project was a large, complex IT project. Complex IT projects introduce an innate resource risk that can only be mitigated through careful staff planning. First Data recommends the State reconsider how IT projects are staffed in the State. The exchange project filled many of its staffing needs using temporary positions, which are difficult to fill due to their lack of employment security. Additionally, qualified staff hired into temporary positions are likely to continue to search for alternate permanent state positions. Consequently, the exchange project regularly struggled to sustain the anticipated project team size and skills. As a result, a large number of staff members were acquired through contracts. Where possible, introducing temporary positions or consultant positions to an organization to backfill or support the systems that will be replaced would naturally align staff attrition with the technology and application lifecycles. Reserving the permanent or long-term positions for the ‘go-forward’ technologies will also provide the state with the capability to develop stronger, more cohesive IT support teams. 

The opposite tact is common, historically, of course.  The emerging project is established with temporary positions or consultants.  Existing, permanent staff remain with the legacy application.  This very commonly leads to a brain drain as soon as it becomes clear the emerging project will be the new normal.  Absent extraordinary efforts to retain those experienced staff - pay, working environment, chances for new projects - they will simply start searching for other employment.

First Data's recommendation is the opposite.  Place the temporary positions with the legacy system immediately, where it will naturally tail down.  If the legacy system is needed longer, extending a temporary position is relatively easy, the person in that position may be relieved not to have to start a job search soon.  The experienced staff immediately start adding value to the emerging system based on the familiarity with the organization and working environment.

A five-year Resume (2008-2013) reviewed

A five year resume is a visionary exercise in career planning: Visualize the type of professional you would like to be in five year and write the resume for that person; The education, the skills, the type of positions and projects that person would have on their resume.  Suffice to say, compare and contrast with the Five year Plan… presumably a plan starting from today forward 5 years.  The five-year resume shows the forward 5 years, allowing you to create a plan to get there.

 

http://www.careers-advice-online.com/five-year-career-development-plan.html

http://jme.sagepub.com/content/31/1/128.abstract

 

In 2008, I wrote a five-year resume - the time period ending in 2013.   This post will grade (those not interested in my navel gazing can stop here, but I'm committed to publishing this to hold myself accountable)  My memory and documentation of the process are incomplete, but roughly speaking,  to find material for a five year resume , I identified senior leaders in my organization or that I had met through various professional groups.  I may have included some biographies of leaders in the business space that I read through periodicals such as Information Week or GovTech.  I viewed their resumes, articles, and LinkedIn profiles.  Through that, I assembled some of the projects or positions I would need to pursue to have similar experience.  I listed the education that overlapped my interest with my model's education.  From all that, boiled it down to things that could reasonably be accomplished within 5 years.

 

Objective   Results-oriented technical project manager with hands on ability in application development and reporting and experience leading technical teams seeking to take on a large enterprise project	Comments: Basically have done all of those, have the ability to do so on an ongoing basis, and am continuing to do so; What's next?
Education Further Training               Related Courses         Project Management - Advanced ITIL/ITSM, etc        MBA-level	Comment: Partial: Earned the PMP in 2008 and am into my second cycle maintaining the credential; have spent next to no time on ITIL or ITSM; Not sure what I was getting at about MBA-level, but didn't
Skills Organizational Project management professional Certified in ITSM/ITIL technical management processes Participating in and leading groups to accomplish project goals	Comment: Basically accomplished; but for the aforementioned ITSM or ITIL; Earned PMP and lead many project groups
Communication Assess client needs in information systems and offering appropriate solutions Proactively offering solutions and training to customers of varied skill levels Delivered training to classes Published technical author – blog – magazine articles – etc.	Comment: Have pretty muchreached these goals.  I now commonly work with clients at various skill levels and authority; Very often I am the technical lead responsible for proposing a solution vs. just working against a requirements.  Most of my class room training has been ad hoc or small group; Publishing is the one I'm wobbling on.  I am decently active on my blog and social media, but I think I envisioned publishing articles to industry publications or sites
Technical Microsoft Certified IT Professional; Microsoft Solutions Framework Architectures – n-tier, web-based Platforms – Business Intelligence platforms. Microsoft (.Net, COM, DCOM, SQL Server); Oracle, PHP/mySQL	Comment: Pretty much a slam dunk on these; Achieved and MCTS (the evolution of MCITP), could probably do more; Am comfortable with the architectures and platforms I listed, and even ones I couldn't have envisioned 5 years ago
Work Experience Consultant        Non-profit, etc. small firm        1/2009 – •        Board-level consulting on IT strategy, etc. •        Web or application design or selection •        Fund-raising, etc	Comment: Going to generously give myself a complete on this one; For at least, 3 groups my children are involved in, I have at times been the primary web or technology person; In 2008, I probably envisioned spending more time with professional groups or consulting for other non-profits, however the opportunity to help groups that my sons and other children enjoy so much has been very rewarding
Project Manager        Medium to large organization        1/2009 – •        Lead Business Intelligence adoption, ideally organization-wide •        End user component •        Technical project team, and hand-off to operations project team	Comment: I can see I hedged my organization with the term 'medium to large organization'.  Ended up staying with one large organization - the California Dept. of Corrections - the entire time, starting my 10th year with the organization now.  But moving around within the organization has given me a chance to get these kind of opportunities.  For this experience, really completed this in the past year as I moved to a new position - database architect - but was deeply involved in handing off the old position - business intelligence to some new team members.
Project Manager - 2        Medium to large organization        1/2009 – •        Manage full-blown, department or large unit-wide project from near initation to completion •        Apply PMBOK best practices •        Cross functional teams Project Manager - 1        Medium to large organization        1/2009 – •        Manage full-blown, department or large unit-wide project from near initation to completion •        Apply PMBOK best practices •        Cross functional teams	Comment: I envisioned managing at least 2 big projects, and although I didn't change organization, I probably ended up managing at least 4-5 in this manner for a single unit - COMPSTAT - of the Dept. of Corrections.  Didn't hold the working title of 'Project Manager' at most points, but nonetheless, was doing PMBOK in an appropriately tailored way; Nearly every project involved some type of cross-functional team; usually a mix of technologists and business program areas
Technical Author        Publishing House        1/2009 – •        Author technical articles – blog- article - book •        Companion teaching, courses, speaking	Comments: Had much higher hopes here; early on I had submitted articles to a few IT trade magazines such as MSDN or Code; nothing accepted; Of course the world changed at the same time, with much more emphasis on community and open articles; so my blog and other social media represents at least some effort;  Have arranged many workshops and presentations internal to the organization, not nearly enough public stuff

 

Next steps:

From this exercise, I would say I didn't end up exactly at plan, but it was a good check on a yearly basis or so to see if there were any big areas . I probably could've set more stretched goals, or documented in more detail the types of project which I wanted to undertake. Ultimately, very happy with where I've landed along this journey.

 

Well, clearly the next step is to research and start a new five year resume, to set a target for 2019!  Rough ideas include a new game plan for publishing and speaking.  As my commitments to my sons' youth groups change shape as they age, I hope to spend more time in professional groups or perhaps find another non-profit in my community to assist.  The next level of leaders I hope to emulate are hyper-involved in networking, their community, and the development of the staff around them.  Education is always necessary, and I'll need to define a focus to the menagerie of online courses, webcasts, and videos that are available more than ever before, but competing for time with other activities.

 

This has been my experience with a five-year resume.  I hope that if you've constructed one or are in the process, in what you see here, it will guide your to creating something that you can review at the end of the five year duration and measure yourself, identify next steps… and celebrate what you've accomplished.

 

Tortoise SVN - Update Failed! - Adapting an SSIS development approach for Window Server 2008

Trained some new developers on a SQL Server BI solution that required them to edit various source files - including SQL Server Integration Services (SSIS) packages.  Well immediate after the orientation to the Version Control repository a new developer received an error attempting to update the local repository.

Turned out a couple of assumptions or constraints were in play

1.       Development of SQL Server Integration Services packages is easier from a server console.  The connection rules aren't an issue.  The connection to source systems is usually - not always - but usually faster.  Running a long-running package doesn't interfere with the developer's ability to do other programs on their laptop… or close it up and go home while the long-running process churns on.

2.       Using TortoiseSVN to edit-merge-commit from a remote repository running on a file server was a simple and elegant version control approach.

3.       Having the source code on a shared folder on the server's local file system was convenient

4.       When there were two developers and a Server running Windows Server 2003, this generally wasn't a problem - we weren’t stepping on each other's folders and/or 2003 security was fairly lenient.

After migrating this technique to Windows Server 2008, these error became apparent quickly.

1.       It was still convenient to develop SSIS packages from the server's console.

2.       The shared folder on the local file system became problematic.  The 2008 security (it behaves like Windows Vista or Windows 7 does) became more finicky about file ownership and ability to edit and delete files with another's ownership.

3.       Multiple developers currently in edit or merge, but not yet commit steps would collide with each other if one another's work overlapped in the file system.

The solution I took was to have each developer create their own 'SourceCode' folder under their 'Documents' folder (i.e. C:\Users\%USERNAME%\Documents\SourceCode). 

Tradeoffs are it is a bit trickier location to which to navigate.  And the redundant copies of the code for multiple developers have a minimal impact on storage.  The advantage, of course, is a truer work process with SVN - each developer is forced to develop SSIS packages separately editing and merging; mitigating most conflicts through collaboration.

Pencil Project as an alternative to Visio for IT professionals

Pencil Project advertises itself as an open source Graphical User Interface (GUI) prototyping tool.  The project stresses it's available for "All" platforms.  "All" platforms includes Windows, Mac, and Linux (Fedora package).

 

(Installed version | Portable Version)

 

As my career has progress in roles such as developer, database administrator, project manager, or analyst, I have always used the software Microsoft Visio.  My first experience with it was in college when Visio was made by a separate company, before being acquired by Microsoft.  I have always carried it with me, buying my own license when working for small companies, and using an enterprise license more recently. 

 

As much as Microsoft Visio has been help, it does have limitations.  The software is not always 'with you', or may be unattainable or inaccessible to other team members due to licensing issues.

 

Pencil Project in a portable version can be carried on your portable drive or cloud drive.

 

Business Analysis

Pencil Project has a basic, but complete Flowcharting shapes available.  Connectors allow basic connections between shapes and stay connected as the diagram is arranged.

 

 

There are three simple connector choices:

 

Database

Pencil Project contains no templates or Shapes for representing Data or Database objects.  Another diagramming tool is necessary.

 

UI prototyping

Extensive templates and shapes for popular Uis.