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

  • SQL Server Backup to Azure

    Because so many people have asked me for specific details on backing up a SQL Server database to Azure, this post shows the necessary steps to accomplish that. The original database name is MIMIC II, which is a clinical database that is available to researchers. It will be used in subsequent posts on data migration and machine learning. The original copy of MIMIC II is in a PostgreSQL database in an Ubuntu virtual machine. I used the SQL Server Import/Export Wizard to import the data into SQL Server, which I find more convenient to work with. I gave the database the name MIMIC2 in SQL Server 2014. When I completed the migration to SQL Server, of course I wanted a backup. Azure was my first choice. By storing it in Azure, I know it is both secure and accessible to me should I ever need it. 

    SQL Server 2014 is shown in the screen captures below. SQL Server versions as far back as SQL Server 2005 can be easily backed up to Azure by using the free Microsoft SQL Server Backup to Microsoft Azure Tool.

    The first step is to create a storage account in Azure. On the left hand side of the page, select STORAGE. Next click either + NEW or CREATE A STORAGE ACCOUNT.

    image

    Figure 1. Pick either of the options to create a storage account.

    Specify the first part of the URL that will be used to access the storage account.

    image

    Figure 2. Entering mimic2 for the URL makes the entire URL mimic2.core.windows.net

    image

    Figure 3. Click on MANAGE ACCESS KEYS.

    image

    Figure 4. You must save the access keys. They are your passwords to the storage account.

    You’ll need to create a security credential to use in SSMS. Here is what your key maps to in T-SQL:

    image

    Figure 5. The storage account name maps to IDENTITY. The primary access key maps to SECRET.

    image

    image

    Figure 6. You can use the SSMS gui to create the security credential instead of T-SQL. Notice the primary access key from Azure maps to Password on the New Credential box.

    Now that you have a storage account, the next step is to create a container. Click on CONTAINERS to change the view.

    image

    Figure 7. Click on CONTAINERS to create a container for your backup.

    To create the container, click either + ADD or CREATE A STORAGE ACCOUNT

    image

    Figure 8. Pick either of the options to create a container within your storage account.

    image

    Figure 9. Enter a name for your new container.

    image

    Figure 10. Your backups will reside in the blob container you’ve created.

    Refer back to Figure 5 or Figure 6 to create a security credential. You’ll have to do that before proceeding to the next step.

    Once you have a security credential created, you are ready to back up to Azure.

    image

    Figure 11. In SQL Server 2014, back up to Azure is accessed like any other back up method.

    To backup to Azure, select URL from the Back up to dropdown list and select your credential name from the SQL credential dropdown list,

    image

    Figure 12. Select URL and your credential name from the dropdown lists.

    You probably want to select Backup Options so that you can select Compress backup from the dropdown list. A compressed backup will take less time to upload to Azure and it will occupy less storage space.

    image

    Figure 13. Selecting Compress backup will reduce the upload time and Azure storage space.

    You can also connect to Azure directly from SSMS and browse your Azure storage.

    image

    image

    Figure 14. Select Azure Storage to connect to Azure to look at your Azure storage from within SSMS. The Account key is the primary access key in Azure, the SECRET in T-SQL, or the Password on the New Credential box in SSMS.

    image

    Figure 15. View your Azure storage accounts, containers, and backups from within SSMS.

    image

    Figure 16. You can also examine the contents of your container from within the Azure portal.

  • Antivirus and SQL Server

    Antivirus protection and SQL Server don’t mix well. Some good tips on getting antivirus software to play nicely with SQL Server can be found here. Take a look, you might find something you haven’t considered.

  • SQL Server Analytic Functions

    Last week I demonstrated SQL Server analytic functions at Houston TechFest. Below you will find an expanded set of code samples from my original content. As I explained in the presentation, I also used content from Itzik Ben-Gan’s excellent book from Microsoft Press, which I highly recommend. I also recommend Itzik’s columns at http://sqlmag.com. Joe Celko has written some very informative articles on analytic functions at https://www.simple-talk.com/.

    Run the code samples on a machine running SQL Server 2012 or higher. The code is also available as a file attachment.

    -- create a table that has a column for ordering the data
    CREATE TABLE #numbers (
         nbr    INT
        ,tempus DATE  -- used for ordering the data
    );

    -- insert some sample data
    INSERT INTO #numbers
    (tempus,nbr)
    VALUES
    ('1/1/2018',1)
    ,('2/2/2018',2)
    ,('3/3/2018',3)
    ,('4/4/2018',4)
    ,('5/5/2018',5)
    ,('6/6/2018',6)
    ,('7/7/2018',7)
    ,('8/8/2018',8)
    ,('9/9/2018',9)
    ;

    -- run an ordinary query ordering by the tempus columns
    SELECT nbr
    FROM #numbers
    ORDER BY tempus;

    -- show the nbr value in the current row and in the previous row
    -- the first row retrieved has a NULL for the previous nbr

    SELECT nbr
          ,LAG(nbr, 1) OVER (ORDER BY tempus) AS prevNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the nbr value in the following row
    -- the last row retrieved has a NULL for the following nbr

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the first value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the last value
    -- notice how it is really the last value so far

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- modify code to always return the last value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- this returns the same results as the previous query
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- notice that the syntax for lastNbr is not what is needed for firstNbr
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- fix the previous query to always show the very first value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    DROP TABLE #numbers;

  • AdventureWorks2014 installation script

    The AdventureWorks2014 sample database is downloadable from here. If you choose to run the script and are unfamiliar with sqlcmd, this post shows you what to do. Be sure to extract the zip file’s contents to a new folder because the instawdb.sql comes with 72 csv files that contain the actual data. If you specify a folder other than the default, you’ll have to change a path in the instawdb.sql script to point to your directory.

    image

    Figure 1. instawdb.sql script file in SSMS. Notice the 3 lines that begin with a colon.

    It’s always prudent to read comments and examine script files before executing them. Notice that you are instructed to change the paths if your actual locations are different from what is in the script. Also notice that FULL TEXT SEARCH should be enabled. The most important instruction in the comments is that the script must be run in SQLCMD Mode, which is available in SSMS but not enabled by default.

    image

    Figure 2. Enable SQLCMD Mode from the Query menu.

    Once SQLCMD is enabled, the SQLCMD statements are highlighted.

    image

    Figure 3. SQLCMD enabled and SQLCMD statements highlighted.

    It took about a minute and a half for the script to execute on my machine, so be patient. You might want to turn off SQLCMD after you’re done installing AdventureWorks2014 to restore SSMS to the state you’re more familiar with.

  • Surface Pro 3 first impressions

    I traded in my Surface 2 (the trade-in program is now over) and bought a Surface Pro 3 with an i7 processor and 8 GB of ram. I greatly prefer the 3 by 2 aspect ratio of the Surface 3. After only one day of ownership, I’ve decided to purchase a docking station. I have a 7 year old desktop with a quad core Q6600 processor overclocked to 3.0 GHz and 8 GB of ram. It has a Plextor 512 MB SSD as the primary drive. It’s a very capable machine, but it does have a little bit, and I do mean only a little bit, of fan noise. I’d like to eliminate even that small amount of noise.

    My subjective initial impression was that my new Surface Pro 3 was significantly outperforming my desktop. Time to use PerformanceTest to get the facts. The Surface Pro 3 outperforms my desktop significantly on every measure except one of the 2D graphics tests. The desktop was slightly better drawing 2D vectors at 18.7k vectors/sec compared to the Surface’s 15.7k vectors/sec.

    PerfRes

    Figure 1. PerformanceTest 8.0 test results.

    I have 6 TB of storage on my desktop. The desktop could be moved to the closet where my router and switch are. With the docking station, the Surface can use my gigabit network to access the files on the desktop. My desktop cannot run the latest version of Hyper-V, which is most inconvenient. Installing Visual Studio 2013 installs Hyper-V. As a reader points out below, this alters the behavior of how Windows runs on the device.

    What I’ve found is that I don’t want to go back to non-touch enabled devices. I use both touch and a Bluetooth mouse with my Surface Pro 3, which is what I also did with my previous Surface 2. Having said that, I use the mouse with SSMS. I’m a little afraid of using touch when doing things to a database. If you’re going to use the Surface Pro 3 for development or doing demos of SQL Server, I recommend that you get 8 GB of ram. Both i7 models have 8 GB of ram. Only the higher version of the i5 Surface that has 256 MB of storage comes with 8 GB of ram.

    UPDATE: After taking the Surface to work today, I concluded that I want a lunch bag with a padded slot to hold the Surface. That way I can carry everything in one convenient bag. To the entrepreneur who creates such a lunch bag, send me one and we’ll call it even, okay?

  • 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.

This Blog

Syndication

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