THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

  • Free Azure HDInsight ebook from Microsoft

    Microsoft has released a free ebook on Azure HDInsight. Full details are available here. HDInsight is Microsoft’s implementation of Hadoop. Azure HDInsight is Hadoop in the cloud. The ebook gives you a quick overview of what Big Data is and what you can do with it.

  • Windows Not Sleeping All Night

    Having a computer wake up when you don’t want it to wastes electricity and drains the battery on mobile devices. My desktop had been waking up at night, so I assumed it was some network traffic on my home network. I unchecked Allow this device to wake the computer on my network adapters.

    image

    Figure 1. Network adapter Power Management tab.

    That didn’t solve the problem. I included the screen capture in Figure 1 because it could be part of the solution for someone else.

    To identify the root cause instead of guessing, the System log was examined. As you can see, pressing the sleep button was putting the machine to sleep.

    image

    Figure 2. Timestamp of when the computer was put to sleep.

    To figure out what was waking the machine up, the System log was checked to review all events following the sleep event.

    image

    Figure 3. System log showing what woke the computer up.

    This is what requested the computer to wake up:

    Wake Source: Timer - Windows will execute 'NT TASK\Microsoft\Windows\TaskScheduler\Regular Maintenance' scheduled task that requested waking the computer.

    A Bing search provided more information: http://support.microsoft.com/kb/2799178

    To change this behavior, go to the Action Center and click on Change maintenance settings.

    image

    Figure 4. Automatic Maintenance section in the Action Center.

    There are two options for modifying the Automatic Maintenance schedule. You can change the time or disable the ability for automatic maintenance to wake your device.

    image

    Figure 5. Options for controlling maintenance events waking the computer.

  • Web-based data generator

    One of my coworkers told me about Mockaroo, a web-based data generator. I needed some test data for upcoming blog posts, so I decided to give it a try. It’s pretty good. I had to use Firefox because of problems running Mockaroo on Internet Explorer 11. Using the defaults except for changing the format to SQL, it generated output that looked something like the following. Mockaroo is so good that it generates fake data that could accidentally be real, such as email addresses. Consequently, I edited the values shown below so that they are hopefully actually fake.

    create table MOCK_DATA (
        id INT,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(50),
        country VARCHAR(50),
        ip_address VARCHAR(20)
    );
    insert into MOCK_DATA (id, first_name, last_name, email, country, ip_address) values (1, 'John', 'Deaux', 'jdeaux@fakeDomain.fakeDomain', 'Made', '255.255.255.255');

    It has lots of options, but some edits to the generated output may be necessary for Unicode compatibility in SQL Server. Look at the following output (which my always helpful friend Greg Low says is politically correct):

    create table TEST_DATA (
        chinese VARCHAR(50)
    );
    insert into TEST_DATA (chinese) values ('空调);

    That’s not what is needed for SQL Server to properly handle Unicode characters. The preceding example needs to be changed to the following:

    create table TEST_DATA (
        chinese NVARCHAR(50)
    );
    insert into TEST_DATA (chinese) values (N’空调);

    Mockaroo is extensible because it can use regular expressions to generate data.

  • TechEd 2014 Day 4

    Many people visiting the SQL Server booth wanted to know how to improve performance. With so much attention being given to COLUMNSTORE and in-memory tables and stored procedures, it is easy to overlook how important tempdb is to performance. Speeding up tempdb I/O improves performance. The best way to do this is to not do the I/O in the first place. With SQL Server 2014, tempdb page management is smarter. Pages are more likely to be released before being unnecessarily flushed to disk. Read more about it here.

    Of course, not all tempdb I/O can be eliminated. Performance can be improved by moving database files to flash memory storage. To maximize the performance benefits of flash storage, buffer pool extensions should be enabled.

  • TechEd 2014 Day 3

    There is some confusion about durability of data stored in SQL Server in-memory tables, so some review of the concepts is appropriate. The in-memory option is enabled at the database level. Enabling it at the database level only gives you the option to specify the in-memory feature on a table by table basis. No existing tables or new tables will by default become in-memory tables when you enable the feature at the database level.

    If you choose to make a table an in-memory table, by default it is durable with changes being recorded in the transaction log. You do not have to worry about data loss. However, you have an additional option of making an in-memory table not durable. If you actively choose to do this, you will have data loss if, for example, the server crashes. There are legitimate use cases of choosing to override the default behavior and create in-memory tables that are not durable. If you need staging tables for ETL, non-durable in-memory tables will provide high performance. If you need temporary tables for a particular processing need, non-durable in-memory tables can outperform tables in tempdb.

  • TechEd 2014 Day 2

    Today people asked me about backing up older versions of SQL Server to Azure. Older versions back to SQL Server 2005 can be easily backed up to Azure Storage by installing Microsoft SQL Server Backup to Windows Azure Tool. It installs a service of the same name that applies rules to SQL Server backups. You can tell the tool to backup or encrypt your SQL Server backups. You can have it automatically upload your backups to Azure Storage. Even if you don’t want to upload your backups to Azure, you might want to use the tool just because it can compress or encrypt your backups. Download the tool from here.

    At the Ask the Experts dinner, I heard a joke about DBAs. A group of lions is called a pride or a sault, a group of crows a murder or a parcel, a group of cats a clutter or a nuisance, a group of bison a herd or an obstinancy. What is a group of DBAs called? An obstinancy.

  • TechEd 2014 Day 1

    Today at TechEd 2014, many people had questions about the in-memory database features in SQL Server 2014. A common question is how an in-memory database is different from having a database on a SQL Server with an amount of ram far greater than the size of the database. In-memory or memory optimized tables have different data structures and are accessed differently using a latch free and lock free approach that greatly improves performance. This provides part of the performance improvement.

    The rest of the performance improvement comes from natively compiled stored procedures that can only access memory optimized tables. Conventional stored procedures can access either conventional or memory optimized tables. While it is true that conventional stored procedures are compiled, they do not compile all of the way down to native machine code. Natively compiled stored procedures are faster than conventional stored procedures.

    For obtaining an in-depth understanding of in-memory database features, I recommend that you read the excellent whitepaper written by my friend Kalen Delaney which can be downloaded from Microsoft here.

  • TechEd 2014 Day 0

    Microsoft’s TechEd 2014 conference opened today. It doesn’t fully open until tomorrow, but was open for doing hands on labs and other side activities. The labs provide you with a convenient, guided tour of new features. To make the best use of your time, I recommend reading the instructions carefully and thoughtfully. Try to understand why and what you are doing instead of just following the instructions. I did three SQL Server 2014 labs today and found them to provide a good introduction to new features. Working with new features provides a level of understanding that you can’t obtain from just reading about them.

    Not at TechEd? No problem. You can find virtual labs online here. SQL Server 2014 labs from TechEd will be added to the online library labs sometime after TechEd. Running the labs on your own machine requires that you install an application for which local administrator rights and a reboot are required.

  • Links and resources for understanding windowing functions

    Today at SQL Saturday in Houston I gave a presentation on SQL Server 2012/2014 windowing functions. The focus was on analytic functions. I used several different resources that I want to share with you.

    First, purchasing Itzik Ben Gan’s excellent book on windowing functions is a must. I used some of his examples because they are clear and useful. You should be able to refactor his queries to solve real world problems.

    Red Gate’s Simple Talk website has several good articles on windowing functions. I used this one. It’s worth a read and has good sample code.

    Itzik has some very good articles on windowing functions you can find on the SQL Server Pro website, such as this one. He wrote a quite advanced article about a problem I encountered as a graduate nursing student at the Veterans Administration Hospital. The problem is calculating when to reorder drugs for patients. He came up with an elegant solution he wrote about here.

  • Changes to Azure SQL service tiers and pricing

    Today Microsoft announced changes to Azure SQL Database service tiers. I’ll simplify it for you: You’ll get larger databases for less money and a better uptime SLA. The Web and Business editions of Azure SQL Database are going away with retirement in 12 months. New service tiers of Basic and Standard are now available.

    Full details on the new tiers are available here and pricing details are here.

  • SQL Server 2014 Backup to the Cloud

    Backing up SQL Server to the cloud makes a lot of sense, particularly for small businesses. Managing the physical media takes up too much time and effort for a small shop. Azure storage simplifies the backup process allowing a small business to allocate its limited resources more effectively.

    As I was preparing this post and editing the screen captures showing how to back up SQL Server 2014 to Windows Azure blob storage, I discovered that earlier today Microsoft released a whitepaper covering all of this and more in depth. Better to have discovered that early in the writing process instead of later! You can download the SQLServer 2014 and Windows Azure Blob Storage Service: Better Together whitepaper to get the whole story.

  • SQL Server 2012 and 2014 Management Differences

    Whenever a new version of SQL Server is released, I compare what is in SQL Server Management Studio to get an overall, visual representation of the differences. There are of course differences that aren’t readily apparent by comparing what’s in SSMS. The scope of this post is limited to the differences that are apparent in putting the different versions of SSMS side by side. In the comparison of 2012 to 2014, the SSMS differences appear under the Management node, which is why other SSMS nodes are not shown.

     

    image

    Figure 1. Composite screen capture of SSMS with SQL Server 2012 Enterprise on the left and SQL Server 2014 Enterprise on the right.

    Notice that all of the differences are additions to SSMS in SQL Server 2014. Whatever appears in SQL Server 2012 SSMS also appears in SQL Server 2014 SSMS. The additions are in the list below:

    Management
         Policy Management
              Policies
                   System Policies
                        SmartAdminSystemHealthPolicy
                        SmartAdminUserActionsHealthPolicy
              Conditions
                   System Conditions
                        SmartAdminSystemHealthCondition
                        SmartAdminUserActionsHealthCondition
              Facets
                   Smart Admin
                   SmartAdmin State
         Managed Backup

    Smart Admin provides an automated and simplified approach to SQL Server administration. It also supports SQL Server Managed Backup to Windows Azure. Backing up SQL Server to the cloud is a great way to keep backups safe and secure while minimizing the cost of personnel and infrastructure.

  • Security in the Cloud including HIPAA

    I’m increasingly recommending cloud based strategies to both drive down costs and simplify things. Cloud technology is now at a point there there are very clear guidelines and frameworks for addressing security concerns. Take a look at the Microsoft Azure Trust Center for a list of all of the security certifications Microsoft has earned.

    Notice that Microsoft has a P-ATO (Provisional Authority to Operate) from FedRAMP, the United States federal government cloud computing watchdog agency. FedRAMP addresses IaaS, PaaS, and SaaS. Of particular interest to those in healthcare is Microsoft is the HIPAA Business Associate Agreement or BAA. The Microsoft HIPAA/HITECH Act Implementation Guidance whitepaper is found here. A list of FedRAMP compliant cloud providers is found here.

    Small businesses can in many cases be better off by eliminating their server rooms and moving them off premises to the cloud. I did some consulting for a law firm a few years ago after a catastrophic failure of a SQL Server. SQL Azure wasn’t available back then, but it would be my recommendation to that firm today.

  • Performance and Security Implications of 8.3 File Names

    While testing exports of SQL Server tables to files, one thing led to another and I started investigating the file server where the files are saved. Finding files on the server seemed slow. Further investigation revealed several MSDN and TechNet posts on how 8.3 file names adversely affect both file enumeration and creation performance. This post provides a detailed description of how slow file server performance was found to be caused by having 8.3 files names enabled. This post explains how 8.3 file names can be exploited to sneak a malicious exe onto your server by hiding it in safe file name such as a txt file.

    This post explains how to check for and disable 8.3 file names. Pay particular attention to how you also need to do 8.3 name stripping to realize the maximum benefit of disabling 8.3 file names. I’m going to stop short of recommending that you do 8.3 name stripping. Here’s some edited output from my desktop that might make you want to proceed with extreme caution:

    C:\Windows\system32>fsutil 8dot3name set C: 1
    Successfully disabled 8dot3name generation on C:

    C:\Windows\system32>fsutil 8dot3name strip /s /v C:\

    ...snip...

    @C:\PROGRA~2\WIC4A1~1\Writer\WI68BE~1.DLL,-1001        HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E

    Total affected registry keys:                2148

    The operation failed because registry entries refer to 8dot3 names in the
    specified path.
    For details on the affected registry keys please see the log:
      "C:\Users\John\AppData\Local\Temp\8dot3_removal_log @(GMT 2014-04-05 02-42-36).log"

    C:\Windows\system32>

     

    Let’s take a look at the log file.

     

    Registry Data                                                                     Registry Key Path
    -------------------------------------------------------------------------------   ------------------------------------------
    C:\PROGRA~1\MICROS~2\Office15\1033\ACCESS12.ACC                                   HKCR\.accdb\Access.Application.15\ShellNew
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,0                                      HKCR\Access\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\PROTOC~1.EXE "%1"                                   HKCR\Access\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP "%1"                        HKCR\Access.ACCDAExtension.15\shell\open\command
    "C:\PROGRA~1\MICROS~2\Office15\MSOHTMED.EXE" "%1"                                 HKCR\Access.Application.15\HTML Handler
    "C:\PROGRA~1\MICROS~2\Office15\MSOHTMED.EXE" /o3 "%1"                             HKCR\Access.Application.15\HTML Handler\shell\edit\command
    "C:\PROGRA~1\MICROS~2\Office15\EXCEL.EXE" /dde                                    HKCR\Access.Application.15\search\AnalyzeInExcel\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP "%1"                        HKCR\Access.Extension.15\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,42                                     HKCR\Access.LockFile.15\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Form.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Form.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [OpenQuery "%1"]  HKCR\Access.Shortcut.Query.1\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Query.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Query.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Report.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Report.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [OpenTable "%1"]  HKCR\Access.Shortcut.Table.1\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Table.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Table.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,0                                      HKCR\Access.UriLink.15\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\PROTOC~1.EXE "%1"                                   HKCR\Access.UriLink.15\shell\open\command
    C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE15\MSOICONS.EXE,6                             HKCR\ACLFile\DefaultIcon
    C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE15\MSOICONS.EXE,6                             HKCR\AWFile\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\Lync.exe,0                                          HKCR\callto\DefaultIcon
    "C:\PROGRA~1\MICROS~2\Office15\Lync.exe" "%1"                                     HKCR\callto\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\OUTLRPC.DLL                                         HKCR\CLSID\{0002034C-0000-0000-C000-000000000046}\InprocServer32

    C:\Program Files\WindowsApps\Microsoft.BingFinance_2014.221.1803.4346_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingFinance_2014.221.1803.4346_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingFoodAndDrink_2014.228.447.1992_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingFoodAndDrink_2014.228.447.1992_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingHealthAndFitness_2014.221.713.446_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingHealthAndFitness_2014.221.713.446_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingMaps_2014.130.2132.1189_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingMaps_2014.130.2132.1189_neutral_~_8wekyb3d8bbwe

    @C:\PROGRA~2\MICROS~3.0\VC\bin\vcmui.dll,-101                                     HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E
    @C:\PROGRA~2\WI3CF2~1\8.1\WINDOW~1\WINDOW~1.DLL,-101                              HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E
    @C:\PROGRA~2\WIC4A1~1\Writer\WI68BE~1.DLL,-1001                                   HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E

    Total affected registry keys:                2148

     

    Things were much simpler on my E drive and didn’t cause me to worry or wonder if there were unintended consequences.

     

    C:\Windows\system32>fsutil 8dot3name strip /s /v E:\
    Scanning registry...
    Registry Data                                            Registry Key Path
    ------------------------------------------------------   ------------------------------------------

    Total affected registry keys:                   0

    Stripping 8dot3 names...

    8dot3 Name      FileId                Full Path
    -------------   -------------------   -------------------------------------------------------------

    Total files and directories scanned:         4936
    Total 8dot3 names found:                        0
    Total 8dot3 names stripped:                     0

    For details on the operations performed please see the log:
      "C:\Users\John\AppData\Local\Temp\8dot3_removal_log @(GMT 2014-04-05 02-58-52).log"

    C:\Windows\system32>

     

    My recommendations:

    1. On installation of a new volume, disable 8.3 file name creation before putting any files on it.

    2. On a new server build, disable 8.3 file name creation in the registry.

    3. Don’t trust my recommendations. Educate yourself and test thoroughly. Then test some more.

    If you think this might only be a file server issue, think again. I’m preparing a SQL Server for FILESTREAM access. I can see a FILESTREAM enabled SQL Server having enough files that 8.3 file names might affect performance.

  • BI Beginner: Power Query and OData

    I’ve observed that not very many data professionals are familiar with OData. It’s an open data access protocol built on AtomPub and JSON. It provides a RESTful means of retrieving data, which is what this post is about.

    One of the things that makes OData important is that it is recommended by the Open Government Data Initiative. Although this post uses the familiar SQL Server AdventureWorks database, remember, OData is open. It’s not limited to SQL Server or even SQL databases in general.

    A small subset of AdventureWorks is published at http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

    image

    Figure 1. AdventureWorks subset published at OData.org

    Open Excel that has the Power Query add-in installed and select the POWER QUERY tab. Select From Other Sources and then choose From OData Feed.

    image

    Figure 2. Selecting From OData Feed.

    Enter the URL for the AdventureWorks OData source and click OK.

    image

    Figure 3. Specifying an OData source.

    Notice that the Power Query Navigator has a popup for peeking at the data.

    image

    Figure 4. Peeking at the data.

    Double-click CompanySales to open the Query Editor. You can rename and delete columns as well as several other changes you might want to check out.

    image

    Figure 5. Query Editor

    After clicking Apply & Close, the data was loaded into the spreadsheet.

    image

    Figure 6. CompanySales from OData loaded into Excel.

    Notice how the query indicates when it was last updated. It’s important to understand this isn’t just a static one time download. This is a data model in Excel that is connected to a live data source. Go to the DATA tab and select Refresh All.

    image

    Figure 7. Notice that the last updated time has changed to reflect the update.

    By taking advantage of new data management features in Excel, you’re no longer stuck with static, stale spreadsheets.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement