none
Best Practice: Connecting to SQL Server from Excel

    Question

  • Hi,

    From my experience, MS Excel is one of the most widely used tools in most companies and because of its power and familiarity, I've run into situations where there's a substantial cost savings from pulling information from MS SQL Servers directly to Excel where it can be easily manipulated.  Most DBAs I've worked with cringe at the thought of allowing Excel to connect to SQL, so I'd like to discuss what risks there are with doing so, and for each risk, are there ways to mitigate it.

    The specific scenario I'd like to discuss is using Excel with a read-only SQL user where only select queries will be ran.

    Example risks:

    RISK: Excel causes locks

    POTENTIAL SOLUTION A:  Use WITH (NOLOCK) in the queries

    POTENTIAL SOLUTION B: Set Isolation Level to READ UNCOMMITTED

    Additional Thoughts: Is there a way for the DBA To control this instead of relying upon Excel to be coded correctly?

    Monday, October 14, 2013 3:37 PM

Answers

  • Either solution will work to the locking problem. 

    However, your bigger problem is an Excel user might bring the SQL Server to its knees by running an invalid query or join.  You would be better off creating a "reporting" database on a different instance using transactional replication or mirroring/AlwaysOn and allowing the Excel users only access to the reporting instance.  This will at least seperate their usage and only affect Excel users, instead of all OLTP.


    No.  There is no way for the DBA to enforce or control how users write the query in Excel.
    Monday, October 14, 2013 4:25 PM
  • Long excel queries in default read-committed isolation level would place shared locks that would block writers to change the data. Shared locks are normally placed on rows or pages, but if many rows are locked, lock escalation will occur and whole table will be locked, blocking all the writers for a long time (as long as that long query runs).

    NOLOCK hint or READ UNCOMMITTED isolation level makes SELECTs not placing shared locks, therefore not waiting any other locks and reading dirty (unocmmitted and possibly soon rolled-back) data. It is not recommended, because results are incorrect. There is a far better solution.

    Turn on READ COMMITTED SNAPSHOT ISOLATION (RCSI) on the database. That makes readers not to place shared or any other locks, reducing blocking dramatically, because readers now do not block writers. But, unlike NOLOCK hint, you get correct, consistent results! That is achieved by row versioning (storing previous row data in tempdb part called row version store, and placing 14-byte pointer in original row to that tempdb old row version), instead of locking/blocking technique. This is how you enable it:

    alter database test_db set read_committed_snapshot on
    That will not only introduce less blocking, but will also give you more accurate results than default READ COMMITTED isolation. Here is one example.
    Monday, October 14, 2013 8:03 PM

All replies

  • Either solution will work to the locking problem. 

    However, your bigger problem is an Excel user might bring the SQL Server to its knees by running an invalid query or join.  You would be better off creating a "reporting" database on a different instance using transactional replication or mirroring/AlwaysOn and allowing the Excel users only access to the reporting instance.  This will at least seperate their usage and only affect Excel users, instead of all OLTP.


    No.  There is no way for the DBA to enforce or control how users write the query in Excel.
    Monday, October 14, 2013 4:25 PM
  • Let's say then that the DBA only allows users to write their queries on a test environment. Then, the DBA creates views on production and only gives Excel select query access to these views.

    In that scenario, are there any potential risks from Excel connecting to the DB and running a select statement on a view?

    Monday, October 14, 2013 7:23 PM
  • There is still the possiblity a user would not use a WHERE clause or something and pull the entire data set to their Excel, causing a problem with the OLTP system.

    Monday, October 14, 2013 7:33 PM
  • Long excel queries in default read-committed isolation level would place shared locks that would block writers to change the data. Shared locks are normally placed on rows or pages, but if many rows are locked, lock escalation will occur and whole table will be locked, blocking all the writers for a long time (as long as that long query runs).

    NOLOCK hint or READ UNCOMMITTED isolation level makes SELECTs not placing shared locks, therefore not waiting any other locks and reading dirty (unocmmitted and possibly soon rolled-back) data. It is not recommended, because results are incorrect. There is a far better solution.

    Turn on READ COMMITTED SNAPSHOT ISOLATION (RCSI) on the database. That makes readers not to place shared or any other locks, reducing blocking dramatically, because readers now do not block writers. But, unlike NOLOCK hint, you get correct, consistent results! That is achieved by row versioning (storing previous row data in tempdb part called row version store, and placing 14-byte pointer in original row to that tempdb old row version), instead of locking/blocking technique. This is how you enable it:

    alter database test_db set read_committed_snapshot on
    That will not only introduce less blocking, but will also give you more accurate results than default READ COMMITTED isolation. Here is one example.
    Monday, October 14, 2013 8:03 PM
  • There is still the possiblity a user would not use a WHERE clause or something and pull the entire data set to their Excel, causing a problem with the OLTP system.


    This risk exists with all reporting tools.  So maybe a better way for me to have framed up this discussion is, "Are there any known risks that exist with using Excel to run queries using a read-only (select) SQL User on a database that would not exist with tools like Crystal Reports that are typically supported by IT?"
    Wednesday, October 16, 2013 12:32 PM
  • There is no more risk using direct access from Excel than any other tool which allows the user to enter anything they want.

    Thursday, October 17, 2013 1:52 PM