Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
Getting Started with SQL Server 2014 Administration
Getting Started with SQL Server 2014 Administration

Getting Started with SQL Server 2014 Administration: Optimize your database server to be fast, efficient, and highly secure using the brand new features of SQL Server 2014

eBook
$9.99 $18.99
Paperback
$28.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Getting Started with SQL Server 2014 Administration

Chapter 2. Backup and Restore Improvements

We saw in the first chapter that the lines between on-premise and public cloud services have become blurred. The integration between on-premise and off-premise technologies imply that you can now easily implement a backup solution that makes use of both, thus allowing you to maintain up-to-date backups both on-site and off-site. This will allow you to implement a disaster recovery plan that meets strict service-level agreements without having to invest in a Disaster Recovery (DR) site. The DR site can be in the cloud. Depending on your environment, this can significantly reduce the cost of implementing a fully blown disaster recovery site or server room.

There are several new features in SQL Server 2014 that make this possible; they are as follows:

  • Backup to URL

  • Backup to Microsoft Azure

  • Encryption

In this chapter, you will look at how you can make use of the new features and integrate them into your backup and recovery plans to ensure that you maintain...

Database backups to a URL and Microsoft Azure Storage


The ability to backup to a URL was introduced in SQL Server 2012 Service Pack 1 cumulative update package 2. Prior to this, if you wanted to backup to a URL in SQL Server 2012, you needed to use Transact-SQL or PowerShell. SQL Server 2014 has integrated this option into Management Studio too.

The reason for allowing backups to a URL is to allow you to integrate your SQL Server backups with cloud-based storage and store your backups in Microsoft Azure. You saw in the first chapter that you can now place your database files on Microsoft Azure cloud-based storage. By being able to create a backup there, you can keep database backups of your on-premise database in Microsoft Azure. This makes your backups safer and protected in the event that your main site is lost to a disaster as your backups are stored offsite. This can avoid the need for an actual disaster recovery site.

In order to create a backup to Microsoft Azure Storage, you need a...

Restoring a backup stored on Windows Azure Storage


In this section, you will learn how to restore a database using the backup you have stored on Windows Azure Storage:

  1. To carry out the restore, connect to your local instance of SQL Server in Management Studio, right-click on the databases folder, and choose the Restore database option.

  2. This will open the database restore pages. In the Source section of the General page, select the Device option, click on the dropdown and change the backup media type to URL, and click on Add.

  3. In the next screen, you have to specify the Windows Azure Storage account connection information. You will need to choose the storage account to connect to and specify an access key to allow SQL Server to connect to Microsoft Azure. You can get this from the Storage section of the Microsoft Azure portal. After this, you will need to specify a credential to use. In this case, I will use the credential that was created when I took the backup earlier.

  4. Click on Connect to...

SQL Server Managed Backup to Microsoft Azure


Building on the ability to take a backup of a SQL Server database to a URL and Microsoft Azure Storage, you can now set up Managed Backups of your SQL Server databases to Microsoft Azure. It allows you to automate your database backups to the Microsoft Azure Storage. All database administrators appreciate automation, as it frees their time to focus on other projects. So, this feature will be useful to you. It's fully customizable, and you can build your backup strategy around the transaction workload of your database and set a retention policy.

Configuring SQL Server-managed backups to Microsoft Azure

In order to set up and configure Managed Backups in SQL Server 2014, a new stored procedure has been introduced to configure Managed Backups on a specific database. The stored procedure is called smart_admin.sp_set_db_backup. The syntax for the stored procedure is as follows:

EXEC smart_admin.sp_set_db_backup  
     [@database_name = ] 'database name...

Encryption


For the first time in SQL Server, you can encrypt your backups using the native SQL Server backup tool. In SQL Server 2014, the backup tool supports several encryption algorithms, including AES 128, AES 192, AES 256, and Triple DES. You will need a certificate or an asymmetric key when taking encrypted backups. Obviously, there are a number of benefits to encrypting your SQL Server database backups, including securing the data in the database. This can also be very useful if you are using transparent data encryption (TDE) to protect your database's data files. Encryption is also supported using SQL Server Managed Backup to Microsoft Azure.

Creating an encrypted backup

To create an encrypted SQL Server backup, there are a few prerequisites that you need to ensure are set up on the SQL Server.

Creating a database master key for the master database

Creating the database master key is important because it is used to protect the private key certificate and the asymmetric keys that are...

Summary


This chapter has shown some of the new backup features of SQL Server 2014. The ability to backup to Microsoft Azure Storage means that you can implement a robust backup and restore strategy at a relatively lower cost. In the next chapter, you will look at what improvements have been made in the database performance arena.

Left arrow icon Right arrow icon
Download code icon Download Code

What you will learn

  • Design a SQL Server infrastructure combining onpremise servers and Windows Azure Storage
  • Create a backup strategy that allows you to store your SQL Server backup in the Windows Azure cloud
  • Improve database performance by using the InMemory OLTP features
  • Implement the delayed durability feature to improve transaction latency
  • Use a cloudbased replica to build an AlwaysOn Availability Group
  • Learn the other enhancements and new features that can help improve database performance

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Apr 25, 2014
Length: 106 pages
Edition :
Language : English
ISBN-13 : 9781782172413
Vendor :
Microsoft
Category :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Apr 25, 2014
Length: 106 pages
Edition :
Language : English
ISBN-13 : 9781782172413
Vendor :
Microsoft
Category :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 131.97
SQL Server 2014 Development Essentials
$47.99
Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide
$54.99
Getting Started with SQL Server 2014 Administration
$28.99
Total $ 131.97 Stars icon
Banner background image

Table of Contents

6 Chapters
SQL Server 2014 and Cloud Chevron down icon Chevron up icon
Backup and Restore Improvements Chevron down icon Chevron up icon
In-Memory Optimized Tables Chevron down icon Chevron up icon
Delayed Durability Chevron down icon Chevron up icon
AlwaysOn Availability Groups Chevron down icon Chevron up icon
Performance Improvements Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.6
(5 Ratings)
5 star 20%
4 star 40%
3 star 20%
2 star 20%
1 star 0%
JD (DBA - South Wales) Jun 27, 2014
Full star icon Full star icon Full star icon Full star icon Full star icon 5
The attractive thing about this publication is that it contains a lot of useful and relevant information in just little over 100 pages.For many of us DBAs we're comfortable with SQL Server 2012/2008 and we are hearing a lot of noise in the SQL community about the new features and enhancements that SQL Server 2014 bundles up.For me the book demystified a few apprehensions I had over cloud technology.It guided me through the new functionality with easy to follow instruction and screen shots.Those sceptical about plunging into the depths of Windows Azure, Virtualisation, Always-On etc will find this book a very useful tool in future decision-making. For example, the prospect of housing your data and SQL environments in the "abyss" may be a daunting one - what this book does is highlight the advantages and pinpoints areas that require careful consideration, such as when to use Delayed Durability.Chapter 3 'In-Memory Optimised Tables' is an in-depth topic but the author clearly defines the differing isolation levels and index choices, making it easily understandable for the everyday DBA.The authors personal experiences of using Cloud-based and virtual technology seems to be evident and there is a plethora of useful easy-to-follow descriptions throughout the entire book.It's a compact book, and as the title suggests - its a useful reference for 'getting started' with some of the features SQL 2014 has introduced. I would recommend this to both newbie DBAs and established SQL 2012/2008 DBAs looking to gain an insight into the latest offerings.
Amazon Verified review Amazon
Ian Stirk May 29, 2014
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
Hi,I have written a detailed chapter-by-chapter review of this book on www DOT i-programmer DOT info, the first and last parts of this review are given here. For my review of all chapters, search i-programmer DOT info for STIRK together with the book's title.Another SQL Server 2014 book quickly follows the product's release, how does it fare?This is a small book of less than 100 pages spread over six chapters. The introduction says "This book is for people who want to learn the new features of SQL Server 2014..." this is at odds with the title which doesn't mention it relating to only new features, I wonder how many people will be caught out by this misleading title?People that want to read only about new features may ignore it, and people that want a general administration book may buy it! "Getting Started with SQL Server 2014's New Administration Features" would have been a more honest title.Below is a chapter-by-chapter exploration of the topics covered.Chapter 1 SQL Server 2014 and CloudThe chapter opens with a few definitions, Windows Azure being Microsoft's public cloud offering, and Microsoft Azure SQL Database the underlying database. It notes that using the cloud reduces concerns about hardware resources, and provides improved scalability.The database provides Software as a Service (SaaS) functionality. A step-by-step walkthrough of how to create a Windows Azure SQL database is given, together with its various settings. Connecting to the newly created database via SQL Server Management Studio (SSMS) is shown next. Finally, the process of migration an existing on-premises database to Microsoft Azure is illustrated.The next section discusses storing your SQL Server database files in Microsoft Azure, combining on-premise and cloud storage solutions. The files are stored as blobs in Microsoft Azure. Various benefits include low cost and elastic storage, together with improved High Availability (HA) and Disaster Recovery (DR). There's a step-by-step walkthrough of creating a database that uses cloud storage (including creating the relevant storage account and storage container).The chapter ends with a walkthrough of creating a Microsoft Azure Virtual Machine (VM), instead of building the VM on-premises.All the sections provide walkthroughs with good descriptions and screenshots. All the sub-chapter headings would have been easier to understand if they specified their function e.g. "Integrating Microsoft Azure SQL Database" would have been better as "Storing your SQL Server database files in the cloud"....ConclusionThis book is a short introduction, from an administrator's viewpoint, to the new features of SQL Server 2014. It's generally practical in its approach, containing step-by-step walkthroughs, together with supporting screenshots. It's generally an easy read, with good but limited explanations.The book's title is incorrect, it should reflect the book relates to new features of SQL Server 2014 only. Some people (myself included) would actually prefer a book that contains only the updated features. I can imagine some people looking for an introductory book for general SQL Server administration, getting annoyed at purchasing a book that contains specifically new features only.One of the problems with reviewing this book is the "Getting Started" part of the title, it provides an apology for the lack of depth and omissions. This is compounded by the book's small size, only 114 pages. For example, there's nothing about the new security enhancements (e.g. connect any database, impersonate any login). I wanted more! I had to keep reminding myself this is a "Getting started" book...Although the diagrams are correctly placed, it might have been useful to provide them with titles - making subsequent chapter scanning easier.I'm not sure for whom this book is intended, if you're new to SQL Server administration, you will need a more general book, not just the new features. If you're experienced with SQL Server administration, you probably need a more detailed book - perhaps this is the book you read before the detailed one (or lookup the detail on the Internet)?As a brief introduction to the new administration features of SQL Server 2014, I can recommend this book, but I suspect you'll soon need a follow-on book containing more detail.
Amazon Verified review Amazon
Vaibhav Gupta Jun 21, 2014
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
Hi ,This book provides introduction to new & important features of SQL Server 2014 and helps you understand why your organization should use this tool over previous versions. This book is for professional DBAs who understands basics of DBA practices and now looking forward to learn new features and going to migrate there environment to upgraded version.A good read !!!Regards,Vaibhav Gupta
Amazon Verified review Amazon
A. Zubarev May 06, 2014
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3
I have been working with SQL Server starting from version 6 in late 90s. Ironically, I had never suspected I will end up using SQL Server or using it throughout the rest of my entire career, and I have no plans defecting. The short story is I joined an incubator company which later became a spin-off a large enterprise where there were almost no Microsoft products used (I initially worked on a Mainframe DB2, then on Unix as a C programmer using Informix and Oracle). At that small start up initially like company all the repeated attempts to install an Oracle database by a vendor have failed. Our CEO was enraged, the deadlines were pressing when my fellow contractor brought a set of freshly purchased MSDN CDs one morning into our office. He exchanged a few words I did not catch with our project manager and vanished in the coldness of our server room. Short time later he re-emerged with a smile on his face and a bunch of us emotionless hearing him say we have a SQL Server database setup for us to use in our belated POC. Frankly, our old-school CEO and many other managers took this optimism with a lot of skepticism. Months later the project went live, on time, all written in VB4 and T-SQL code. And it worked very well.Back then our primary goals were data processing speeds, storage capacity, CPU utilization, scaling and transactional sanity. Fast forward into today all the aforementioned concerns remain as valid. However, what changed is that there are more options to tackle these issues up, in different ways, faster and more efficiently if not with a lower budget. What is new and improved under a DBA or developer’s belt in terms of tooling and capabilities Getting Started with SQL Server 2014 Administration book is going to cover.Indeed, the book has the right timing and is right on the spot covering well the unique and most massive advancement in the entire RDBMs world – InMemory data processing, delayed durability And it does not stop there, the reader will get known how to create databases in Windows Azure, backup into the Cloud or restore from it, creating hybrid AlwaysOn environments, performance enhancements and a few more topics. So this is a book SQL Server 2014 Enterprise specific and for a modern DBA.Alas, the book does not cover any other aspects of even a junior DBA, e.g. there is no mention on how to install SQL Server 2014 on premises, securing it up, choosing a backup strategy, monitoring. Likewise, several other topics that are actually new in this build are also amiss as Lock Priorities, T-SQL (1 change), tempdb performance and the new security roles.To continue on the negatives, one general comment I have is that the book has no enough in-depth feature coverage for non Enterprise grade releases, should have more examples, the book should have been expanded beyond the 80 or so pages of useful content to include the management tools overview, performance troubleshooting, auditing, etc. even for a ‘getting started’ book. Thing is, the life of even a novice DBA would be very demanding in terms of achieving acceptable application response time, feature suggestion, or just the time to fix an issue.I am giving this book a 2.5 rounded up out of 5 mark as I can hardly see how it outweighs the benefits of the MSDN help or the waste amount of online articles.Disclaimer: I received the copy of the book for free from the publisher.
Amazon Verified review Amazon
AmazonCustomer Feb 21, 2018
Full star icon Full star icon Empty star icon Empty star icon Empty star icon 2
Just 100 pages and the information are little. Book price is too much.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.