background preloader

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

The SQL Server Conference - SQLBits - Query across the Mersey - 29 Sep 2011 - 1 Oct 2011 - Liverpool Index Defrag Script, v4.1 « SQL Fool It’s been quite some time since my last index defrag script update. A big part of the reason for that is because I wanted to implement many of the suggestions I’ve received, but I just haven’t had the time. I still have those changes planned, but I’m not sure quite when I’ll get to it. Rather than continue to wait for a major release, I’m releasing a small update to my defrag that will take care of the most common complaints I receive. Change Log: Bug fix for databases containing spaces or special charactersSupport for case-sensitive databasesRe-executable CREATE script (for those who want to re-run the whole script)Comma-delimited list of databases is now supported for the @database parameter Feature List: Defrag a single database, a list of databases, or all databases (@database)Time Limitations: stop defragging after the specified amount of time has elapsed (@timeLimit). I often receive the same questions about this script, so allow me to answer them here: SQL Server Book Reviews for DBAs There’s a ton of SQL Server books out there to help get you started on the road to becoming a database administrator. I’m going to start at the start of your career, and go forward. My Favorite Non-SQL Server Books These books aren’t specific to DBAs, but are things I’ve found really helpful in my career: Getting Things Done Getting Things Done by David Allen Do you get nervous at the thought of opening your email in-box because there’s so much piled up, and you don’t know where to begin? David Allen’s book Getting Things Done has been the answer for me and for a few of the folks I work with. I showed my current manager into the GTD philosophy, and he caught on right away. You can buy the paperback on Amazon, and there’s also a Kindle version. Time Management for Systems Administrators by Thomas Limoncelli If you’re not quite ready for the GTD strategy, check out this lighter version of the philosophy that’s tailored specifically for IT workers. The Whuffie Factor by Tara Hunt

When SSDs Make Sense in Server Applications In past posts, I’ve talked a lot about Solid State Drives. I’ve mostly discussed about why they are going to be relevant on the server side and the shortest form of the argument is based on extremely hot online transaction processing systems (OLTP). There are potential applications as reliable boot disks in blade servers and other small data applications but I’m focused on high-scale OLTP in this discussion. The problem is best summarized by my favorite chart these days from Dave Patterson of Berkeley: This chart is from an amazingly useful paper, Latency Lags Bandwidth (if you know of no-charge location for this paper, let me know). Understanding disk bandwidth growth is a growing problem, let’s compare disk sequential bandwidth with random I/O rates over-time. We know that disk sequential bandwidth growth lags the rest of the system. Disks clearly aren’t the full solution. When should we expect the crossover? My observation goes like this. Let’s try an example. --jrh

Free Training Videos and Tutorials We speak at SQL Server user groups and live events, and we record presentations and post them here whenever possible. Every Tuesday at 11:30AM Central, we run a free webcast covering SQL Server, virtualization, storage, and more. If you can attend live and ask questions, that’s great! You can register for our upcoming webcasts, and add a recurring calendar reminder so you can make it each week. Want to automatically get new videos and set up easy viewing on your tablet or phone? Pop some popcorn and turn down the lights…. SQL Server Performance Tuning Tutorials SQL on SSDs: Hot and Crazy Love – Brent Ozar talks about the attractions and perils of super fast storageDevelopers, Performance, and the SQL Server Transaction Log – Kendra Little talks about how application patterns impact performance of the transaction logDBA Darwin Awards: Index Edition - Brent Ozar shows you common mis-steps people make with indexes. Professional Development Videos SQL Server DBA Tutorial Videos

NOSQL Patterns Over the last couple years, we see an emerging data storage mechanism for storing large scale of data. These storage solution differs quite significantly with the RDBMS model and is also known as the NOSQL. Some of the key players include ...GoogleBigTable, HBase, HypertableAmazonDynamo, Voldemort, Cassendra, RiakRedisCouchDB, MongoDB These solutions has a number of characteristics in commonKey value storeRun on large number of commodity machinesData are partitioned and replicated among these machinesRelax the data consistency requirement. (because the CAP theorem proves that you cannot get Consistency, Availability and Partitioning at the the same time)The aim of this blog is to extract the underlying technologies that these solutions have in common, and get a deeper understanding on the implication to your application's design. I am not intending to compare the features of these solutions, nor to suggest which one to use. API model The basic form of API access is Data replication

Selecting the SQL Server database recovery model to ensure proper backups ProblemOne of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database. SolutionFor SQL Server 2000 and 2005, Microsoft offers three different recovery models for your databases. The three recovery models are: SimpleThe simple recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server. Type of backups you can run: Complete backups Differential backups File and/or Filegroup backups Partial backups Copy-Only backups Data is critical and data can not be lost.

SQL Server 2008 MCM Readiness Videos The Microsoft Certified Masters Program for Microsoft SQL Server provides the most experienced and talented IT Professionals with worldwide validation and recognition of their deep technical expertise in SQL Server. The SQL Server 2008 Microsoft Certified Master program has been re-launched in a more scalable format because of the increased interest and global demand for the program. For more information, see the Microsoft Learning SQL Server 2008 Microsoft Certified Master program site. The following readiness videos provide candidates who want to pursue SQL Server 2008 Microsoft Certified Master certification with an overview of what you need to know to prepare for the SQL Server 2008 Microsoft Certified Master Knowledge Exam and Lab Exam. Each module establishes the level of depth and breadth required to achieve this certification and also includes an MCM-level deep dive of one or more aspects of the specific subject area.

Why SQL Server runs best on Microsoft Azure - SQL Server Team Blog Microsoft Azure Infrastructure Services offers many ways to optimize your on-premises data and data platform projects from development and test of new SQL Server applications to migrating existing on-premises SQL Server instances on the latest images of SQL Server to cost effective hybrid scenarios such as database backup and extended business continuity. So why is Microsoft Azure Infrastructure Services the ideal place to implement such scenarios? The latest Images of SQL Server always available Let’s start with having the latest images of SQL Server as soon as they are made generally available including tuned images for select workloads like an optimized data warehousing image. Additional VM Sizes to choose from and more continually being added With recently added larger VM sizes including the A8 (8 vCPUs, 56GBmem) and A9 (16 vCPUs, 112GB) virtual machines you know have access to more cores and more memory for your larger SQL Server workloads. Try Microsoft Azure

Photo Gallery: SQL Server Hybrid Cloud Options | SQL Server Pro Without a doubt, SQL Server DBAs and database professionals are among the most reluctant customers to adopt cloud anything, and there are some good reasons for that. SQL Server is a business critical infrastructure component that most organizations simply won’t trust to the cloud. Those organizations have a significant investment in on-premises hardware and software, plus moving to the cloud involves a number of risks. Microsoft has recognized that most businesses are going to be sticking with their on-premises infrastructure for some time, and it has quite pragmatically come out with several hybrid cloud options that can be a real value to SQL Server customers. These hybrid cloud options don’t require you to move the primary processing of your SQL Server databases to the cloud. Related: Top 5 SQL Server Hybrid Cloud Resources

SQL Server 2016 public preview coming this summer - SQL Server Team Blog Satya Nadella, CEO of Microsoft, announced SQL Server 2016, an intelligent platform for a mobile first, cloud first world. The next major release of Microsoft’s flagship database and analytics platform provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. Top capabilities for the release include: Always Encrypted - a new capability that protects data at rest and in motion, Stretch Database - new technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure, enhancements to our industry-leading in-memory technologies for real-time analytics on top of breakthrough transactional performance and new in-database analytics with R integration. Always Encrypted Stretch Database Today, in the Ignite keynote, we showcased how you can gain the benefits of hyper-scale cloud in the box with new hybrid scenarios including Stretch Database. Real-time Operational Analytics & In-Memory OLTP