Blog

Feb 11
Attend a SQL Server 2016 webcast - Win a Lenovo Yoga 3 Pro

​Passing this on!  Microsoft is presenting a webcast to cover some cool new stuff in SQL Server 2016.  Even if you don't win a free computer, you still get to hear about SQL 2016, which doesn't suck.

In this webcast you will learn how:

  • SQL Server 2016's blazing fast performance is the biggest leap forward in Microsoft's data platform history with real-time operational analytics
  • Advanced Analytics provides deeper insights for better sales forecasting, fraud detection, and other data-driven business decisions
  • Enhanced AlwaysOn Availability makes lost data recovery a breeze
  • Always Encrypted technology protects your most valuable asset – your data – at rest or in motion.

SQL Server 2005 Support ends in just a few months, which means it's probably a good time to educate yourself on SQL Server 2016's exciting capabilities and get ready to migrate in order to protect your data.

Register: https://clicktoattend.microsoft.com/en-us/Pages/EventDetails.aspx?CR_CC=200759962&EventId=215879

 

Dec 04
5-12-16: Advanced Restore Techniques with Jes Borland

Topic for the May 2016 Meeting:

 

Minimize Data Loss with Advanced Restore Techniques

Data loss can strike any server at any time. Are you prepared for the inevitable? A good DBA takes regular backups and practices regular restores; a great DBA has advanced restore techniques planned and tested. In this session, I will demonstrate three advanced restore methods that can make you a great DBA - point-in-time restores, page restores, and piecemeal restores, and will discuss the appropriate situations when each should be considered. Attend this session to better protect your business and your job.

Click here to register (Free!)

About Jes Borland:
JesBorland.jpgJes Borland is a Senior SQL Server Engineer with Concurrency and a Microsoft SQL Server MVP. She holds an AAS – Programmer/Analyst degree, is a Microsoft Certified Professional in SQL Server 2012, and has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. Her credits including founding three user groups, blogging at LessThanDot.com, and frequently presenting at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.

Where: 

3000 C Street, Suite 210, Anchorage, AK  99503    

When: 

  • May 12th 2016, at 12pm Alaska Time
  • Recurrence: The 2nd Thursday of every month, (Add to Calendar)

Click here to register (Free!)  This will give you access to the WebEx link. 

Dec 02
4-14-16: Performance Troubleshooting Using Wait Stats with Paul Randal

​Topic for the February 2016 Meeting:

Performance Troubleshooting Using Wait Statistics

One of the first things you should check when investigating performance issues are wait statistics - as these can often point you in the direction for further analysis. Unfortunately many people misinterpret what SQL Server is telling them and jump to conclusions about how to solve the problem - what is often called 'knee-jerk performance tuning'. In this session, you will learn what waits are, how to analyze them, and potential solutions to common problem patterns.

Click here to register (free)!

Note: this is a 90-minute session, including Q&A, so be sure to make arrangements for a long lunch!

About Paul S. Randal (CEO / Owner of SQLskills.com)

PaulRandal.jpgPaul is a Microsoft SQL Server MVP and a Microsoft Regional Director. He spent 9 years working on the SQL Server team, writing DBCC CHECKDB, and ultimately responsible for the entire Storage Engine. In 2007 Paul left Microsoft to co-own and run SQLskills.com, and is a world-renowned author, consultant, and top-rated speaker on SQL Server performance tuning, administration, internals, and HA/DR. When he's not tweeting, blogging, or helping someone recover from a disaster, he's likely to be underwater somewhere in the world with his wife, Kimberly L. Tripp. 

Email: Paul@SQLskills.com 
Website: http://www.SQLskills.com 

Full Bio: http://www.sqlskills.com/about/paul-s-randal/
Blog: http://www.SQLskills.com/blogs/Paul 
Twitter: http://twitter.com/PaulRandal 
SQL Server MVP since 2008

 

Where: 

3000 C Street, Suite 210, Anchorage, AK  99503    

When: 

  • April 14th, 2016, at 12pm Alaska Time
  • Recurrence: The 2nd Thursday of every month, (Add to Calendar)

Paul will be presenting remotely via WebEx; this session will also be recorded and posted to the AlaskaSQL YouTube Channel.

Click here to register (free)!

Dec 02
2-11-16: Stored Procedure Optimization with Kimberly Tripp

Topic for the February 2016 Meeting:

 

Stored Procedure Optimization Techniques

Kimberly Tripp will discuss stored procedure caching and the potential for performance problems due to a problem known as parameter sniffing. There are some common solutions and while they can drastically improve performance, they can also cause other problems. Sometimes a hybrid solution is needs, from this session, you'll know the best way to write procedures so that stable requests are cached and unstable requests are not (giving you the most optimal performance without wasting resources).

This event has ended.  See below for slides, code download, and on demand recording.

 

About Kimberly Tripp (President and Founder of SQLskills.com):
KimberlyTripp.jpgKimberly has been a Microsoft SQL Server MVP and a Microsoft Regional Director for over 10 years; she's an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. She has over 20 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics, and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal.

Email: Kimberly@SQLskills.com 
Website: http://www.SQLskills.com 

Full Bio: http://www.sqlskills.com/about/kimberly-l-tripp/
Blog: http://www.SQLskills.com/blogs/Kimberly 
Twitter: http://twitter.com/KimberlyLTripp 
SQL Server MVP since 2002

 Click here to download Kimberly's slides and code samples: SQLskills_Tripp_AKUG_StoredProcOpt.zip

Watch now on YouTube!

 

 
Dec 02
12-10-15: SQL Lunch

​Join us at the NBS office for an informal get-together to have lunch, talk about SQL Server, and watch videos from the SQL PASS Summit!  Food will be available from the Bethel Cafe.  This is an in-person event only. 

Where: 

3000 C Street, Suite 210, Anchorage, AK  99503    

When: 

  • December 10th, 2015 at noon Alaska time.
  • Recurrence: The 2nd Thursday of every month, (Add to Calendar)

 

 

Oct 29
11-12-15: What's New in SQL 2016

Topic for the November 2015 Meeting:

What's New in SQL 2016 and the Cloud 

SQL Server 2016 is still in its "Community Technology Preview" phase, but there's a lot of really amazing stuff coming our way in this latest version.  Since we only have an hour, I will cover some of the more important changes coming our way, and I'll have the latest ISO to give you so you can try it out for yourself.

Speaker: Greg Burns, GCI Network Business Systems

Greg Burns has been working as a SQL Server DBA (and sometime developer) since 1997.  He has worked with SQL Server in some way nearly every day since then, from server builds and migrations to database design and reporting.  He is the chapter leader for the Alaska SQL User Group (and also the Alaska SharePoint User Group).  He currently works as a senior consultant for GCI / Network Business Systems in Anchorage, AK.      

 

Download the PowerPoint slides

Watch Now on YouTube

 
Oct 13
SQL 2005 End of Service and SQL Standard Virtualization

In case you were not aware, SQL Server 2005 is reaching its end of life today. [Correction: SQL 2005 end of life is in April 2016.]

 Dave Gollab, a friend of mine who works in the Government sector at Microsoft, sent out this email about what you should do if you're still running SQL 2005, and gave me permission to share it with you here. (If Dave's name sounds familiar, it's because he presented a cool demo to AlaskaSQL back in April 2015: PowerBI and Azure Machine Learning.)

 

 

Hello, hopefully this is a relevant and important topic for you. Skip to the end if you know the story and want the the "How To" :)

In my year's spent working with commercial customers and the past few years with government customers, I have noticed common themes when it comes to a CIO's tactical priorities around SQL Server.  I am not talking about the super cool strategic initiatives, but more a CIO's table stakes when it comes to SQL Server. 

One theme is to control sprawl, another theme is to ensure a safety net exists if something were to break, in other words, ensure the product is supportable, and a final theme is to ensure SQL has been deployed with proper accounting. 

These themes aren't especially exciting. They sure aren't about designing a proactive decision driven business using machine learning. Or, ensuring a single authoritative record can be established for mission critical master data. However, these tactical themes are especially relevant right now in the context of two things:

  1. SQL Server 2005 End of Support (security and sprawl)
  2. SQL Server Standard Edition Virtualization (sprawl and accounting)

The first is time sensitive and the second is anecdotally what we have been seeing and working through with many of our customers. Here is more information:

  • SQL Server 2005 End of Support (security and sprawl):
    • If a customer has SQL 2005 they will be facing End of Support (EOS) in under a year (I think April 2016). We are proactively get the word out, urging customers to upgrade to a supported version of SQL.
  • SQL Server Standard Edition Virtualization (sprawl and accounting):
    • For customers that have a large number of SQL Server Standard Edition and have moved from physically hosting to virtualizing, particularly for SQL Standard instances not covered by SA, we have found a number of customers freely floating those VM's in their VMWare or HyperV farm.  It's understandable. The DBA works with system admin, they P2V the SQL workload and then the system admin drops the SQL VM into their VMWare farm. And, most system admins float all VM's across the farm. From a licensing perspective this is not supported. This applies to SQL Enterprise as well, but most commonly if you find SQL not covered by SA, it is Standard Edition.
    • Customers licensing lots of sprawling SQL Standard (Native or VM's) may want to consider a Private Cloud SQL Licensing strategy which could be financially advantageous. Yes, this comes with the cost of SQL Enterprise plus SA, but that cost could be absorbed (CAPEX and OPEX), or close anyway, if the SQL Standard population is large enough.  The cost can also be absorbed when you leverage Enterprise features that may have been satisfied via expensive third party solutions, i.e. encryption at rest, which is included in SQL Enterprise.

 

How-To:

With tactical initiatives, everyone likes to see the steps needed to address them. At a high level here are the steps to take:

  • Run the MAPS tool (here).

SQL 2005:

  • Take the MAPS inventory report (Excel workbook) and sort by SQL version.
    • One at a time, choose the least mission critical <=SQL 05 instances first and start upgrading them (assumes SA). This will develop your upgrade skills, making more complex upgrades easier.
  • First run upgrade advisor (here) on the SQL instance to clear any issues.
    • Use SQL Upgrade Guide (here) to help with the "How To"

SQL Standard Edition:

  • Sort the MAPS Excel sheet by SQL Standard Edition and investigate to see if those SQL Standard Instances are VM's that float. I can help provide pointers for what to look for; not a precise science.
    • If they are floating (likely the case), are they covered by SA?
    • If they are not covered by SA, then they would either need L and SA, or a Private Cloud licensing strategy.
    • Note: the above applies to SQL Enterprise as well
  • If the SQL Standard (or Enterprise) instances from the MAPS inventory are native instances or if they run in a haphazard VM environment (co-located with non-SQL VM's), consider consolidating through a common virtualization framework with physical hosts dedicated to SQL VM's. Refer to this private cloud/consolidation white paper (here) – Note: that this white paper was written a couple of years ago and it assumes System Center/Windows Server hosts; you can easily enough focus exclusively on the SQL Server aspects.

 

Thanks, Dave

 

GovMSE OneDrive

GovMSE youtube videos (or search "GovMSE SQL" from youtube

Sep 17
How to Report against a SQLite Database

Recently I was working with a customer who uses SpiceWorks to manage their IT department, for everything from inventory to tracking their trouble tickets. The customer asked if I could write a report that showed how much time each tech spent on each task (expressed in dollars), grouped by month, with subtotals and totals. SpiceWorks does have a web-based reporting feature (and many canned reports), but I quickly determined that this was way beyond its capabilities. All the SpiceWorks reports are just tabular.

I figured something like Excel or SSRS might be the best tool for the job. But then I found that SpiceWorks doesn't use SQL Server, it uses a flat-file database I'd never heard of called SQLite. (Note: several people told me they thought there was an option to use SQL Server when you are installing SpiceWorks, but I haven't seen any evidence of this).

So I spent some time figuring out how to connect Excel to SQLite and create a report. Turns out you can do it (which is why I'm writing this post), but it does require a little preparation. Here's my approach.

 

     Requirements

      

   

  1. An ODBC provider.
    1. SpiceWorks uses a database format called "SQLite". In order to report against it, you need to install an ODBC driver. There is an open source (i.e. unsupported) SQLite provider that I was able to use.
    2. Download: http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
    3. Reference: http://www.ch-werner.de/sqliteodbc/
  2. MS Excel.
    1. I used version 2013 but 2010 should do fine. You need to have the ability to connect to remote data.
    2. Optional: PowerPivot add-in. I did not end up needing this, but it's handy if you plan to do any data modeling.
  3. The SpiceWorks database.
    1. By default, the live database is located here: C:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db
    2. But you may wish to work from a copy of the database. Copy / paste it to a new location and be aware that the data will be stale.

   

You might also want to install a tool you can use to browse the SpiceWorks database schema and run some test queries against. I found a handy (free) tool called SQLite Expert.

 

     Install the ODBC provider on your computer.

        

For Win32 operating systems a binary package is available as an NSIS installer in sqliteodbc.exe. It was made with SQLite 2.8.17/3.8.9 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver. 

   

For Win64 operating systems a binary package is available as an NSIS installer in sqliteodbc_w64.exe. It was made with SQLite 3.8.9 and a MinGW cross compiler, and contains the driver DLLs and programs for installation and uninstallation of the ODBC driver. Note that this is a 64 bit only driver. If you're using 32 bit software on Win64 you should install the Win32 driver, too. 

   

From <http://www.ch-werner.de/sqliteodbc/>

   

My advice: stick with 32-bit driver unless you're using 64-bit MS Office.

   

This walkthrough assumes you are using the 32-bit driver.

   

   

Be sure to read Online Documentation. It is a simple install but you should be aware of any known issues or limitations.

   

After install, a provider for SQLite will be added to ODBC 32-bit drivers:

   

     Create a Data Source Name (DSN) for the SpiceWorks database

  •  Go to Control Panel --> Administrative Tools --> Data Sources (32-bit)

       

  • Click on System DSN --> Add
    • This will create a DSN available to all users. If you do not have rights to add a System DSN, then just create one for yourself using the User DSN tab.
  • Select the SQLite ODBC driver and click Finish.

  • A configuration dialog will pop up. Specify the database file and give the DSN a name. I left all the other options blank.

  • Click OK and close out of ODBC Data Sources applet.

       

   

     In Excel, create a connection to SpiceWorks production database (or your copy of it) and import to a table.

        

   

  1. In Excel, create a new workbook.
  2. Click on Data Tab --> Get External Data --> From Other Data Sources --> Data Connection Wizard

  3. Specify "ODBC DSN" and click Next.

  4. Select the DSN you just created and click Next.
  5. The database has an internal name of "MAIN". You can choose to specify a particular table or view, or uncheck the box to open a generic connection and specify tables later using Excel.
    1. In my case, I had already created and saved a view in the SpiceWorks database, containing the data I was interested in, so I picked that.

    2. Click Next.
  6. Now you need to create an Office Data Connection (ODC) file. I accepted the defaults and clicked Finish.

    1. You may wish to specify the authentication method for Excel to use when refreshing the data. I found that the default (use the current login's credentials) worked for me.

  7. Excel will then ask where you want to import the data.
    1. For this walkthrough, I imported the data to a table, in a new worksheet.

  8. The new worksheet will open and your selected data appears in a table. Note in the top left of the worksheet is the name of the table.

       

       

Tech Tip:

  1. I was having trouble with grouped sums in the "Labor" column. I read that this can happen when the underlying datatype for the column is "text", and when I tried to roll up sum values for this column, all the values were zero. Lame!
  2. My workaround was to insert a column in the table and use a formula to extract the value of the Labor column. =VALUE([@labor])
  3. This is actually a dollar amount, so I specified to format the column as Currency.

   

     Create a PivotTable from the table

  •      In the ribbon, click "Summarize with PivotTable"

  • In the Create PivotTable dialog, verify that your table is selected, and choose "New Worksheet".

  • Add fields to PivotTable as follows:

    • Note: the LaborValue field is a dollar amount, so you may have to specify Value Field Settings --> Number Format and change it to Currency.

         

    Result:

  • On the PivotTable worksheet, select the closed_at field and then in the Ribbon click Analyze --> Group Field

       

  • Select to group by Year, Quarter, and Month. Click OK.

       

    Result:

    Labor is rolled up by year, quarter, and month, broken out by tech

       

    I then added slicers to the worksheet and showed the customer, who was very happy. We like happy customers.

       

       

       

   

        

Aug 18
8-20-15: Encore - Windows Clustering for the DBA (Part 2)

Topic for the August 2015 Meeting:

Encore Presentation: Windows Clustering for the DBA (Part 2)

The recording for the July meeting didn't turn out, so Ryan has offered to present this session again!  Thanks Ryan! 

You need to implement AlwaysOn Failover Clustering or Availability Groups, but need help with the Windows Clustering part.  This two-part session will guide you to success.  We'll talk about how to plan your network, DNS, Active Directory, and storage. 

I'll provide you with a check list you can use to plan your implementation.  We'll see how to choose the right quorum model for a robust failover configuration that ensures your database is always available.

This is part 2 of a 2-part series.  Part 1 was held on June 11th, 2015.  Both sessions will be recorded and available on the AlaskaSQL YouTube channel.

Speaker: Ryan J. Adams (SQL Server MVP)

Ryan Adams is a SQL Server MVP and has worked for Verizon for 17 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure.  He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He is also a PASS Regional Mentor and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP  

 Download Ryan's slides for Part 2​

Watch now on YouTube​!
 

Jul 14
7-23-15 - 8 Free Hours of Performance Training

​My friend Ryan J. Adams is organizing a "Performance Palooza" coming up on July 23rd, 2015.  

How could you possibly argue with getting 8 straight hours of SQL Server Performance training for FREE?

You can’t!  Go check out the annual Performance Palooza hosted by the Performance Virtual Chapter on July 23rd, 2015.  This is a virtual online event open to the public and it’s absolutely free.  We’ll have 8 back to back sessions with start times from 10am to 5pm Central Time.

​Central Time​Speaker​Topic
10am​​John SterrettMake Your SQL Server Queries Go Faster
​11am​Warner ChavesBuilding High Performance SQL Servers Virtual Machines on AWS
​12pm​Andy GalbraithPerforming a SQL Server Health Check
​1pm​Matan YungmanColumstore Indexes – Questions and Answers
​2pm​Neil HamblyPerformance Troubleshooting Using DMVs
​3pm​Tim MitchellMaximizing SSIS Package Performance
​4pm​Trayce JordanTroubleshooting Seconday Replica Latency
​5pm​Mike FalSQL Server Benchmarking: The Powershell Speedometer

 

More info here:

http://www.ryanjadams.com/2015/07/8-hours-of-free-performance-training/

 

1 - 10Next