Day Zero with PowerShell + SQL

Learn how to get setup with PowerShell and SQLPSX from MVP Aaron Nelson, one of the experts teaching us how to use Powershell for SQL Server


http://www.sqlservercentral.com/articles/powershell/73288/

Top 10 Mistakes When Building and Maintaining a Database

Building and maintain a SQL Server database environment takes a lot of work. There are many things to consider when you are designing, supporting and troubleshooting your environment. This article identifies a top ten list of mistakes, or things that sometimes are overlooked when supporting a database environment.


http://www.databasejournal.com/features/mssql/article.php/3906986/Top-10-Mistakes-When-Building-and-Maintaining-a-Database.htm

Partition offset and allocation unit size of a disk for SQL Server

SQL Server performance is dependent on the server resources available and disk performance is probably the most important resource.  To maximize disk performance for SQL Server, I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs.  How do I find out the allocation unit size and partition offset for my drives?.


http://www.mssqltips.com/tip.asp?tip=2119

How Old Are Your Database Statistics?

Unlike fine wine, database statistics do not improve with age. I recently helped out with a client who was having issues with poor query performance on a SQL Server 2005 instance on very good hardware. After having them run my standard SQL Server 2005 Diagnostic Information queries, nothing glaringly obvious was jumping out at me. Sure, there were a couple of missing indexes that needed to be added, but overall query performance was still pretty bad. The server as a whole was not under obvious CPU, memory, or IO pressure.


http://www.sqlservercentral.com/blogs/glennberry/archive/2010/8/30/how-old-are-your-database-statistics_3F00_.aspx

Restoring Exchange Server 2010 using Windows Server Backup

In my last article we discussed how to benefit from the built-in windows server backup feature of Windows 2008 and 2008 R2 and perform healthy backups of your Microsoft Exchange databases for free, without the need for any 3rd party tool. Today, we are going to go over the restore process by looking at two different situations; the first one is when we have experienced a complete loss of one database, and the second is when we need to recover just a single item from a mailbox.


http://www.simple-talk.com/sysadmin/exchange/restoring-exchange-server-2010-using-windows-server-backup/?utm_source=simpletalkExchange&utm_medium=email-sysadmin&utm_content=Restoring-20100706&utm_campaign=Exchange

Backing up Exchange 2010 For Free

It's hardly surprising that many SysAdmins are willing to pay over the odds for sophisticated backup solutions which they don't necessarily need, just to make sure their data is safe ASAP. Thankfully, Antoine Khater is here to give you a short and sweet walkthrough on how to keep your Exchange 2010 Server backed up for free. And the best news? You've already got everything you need.


http://www.simple-talk.com/content/article.aspx?article=1040

High Performance Storage Systems for SQL Server

Rod Colledge turns his pessimistic mindset to storage systems, and describes the best way to configure the storage systems of SQL Servers for both performance and reliability. Even Rod gets a glint in his eye when he then goes on to describe the dazzling speed of solid-state storage, though he is quick to identify the risks.


http://www.simple-talk.com/sql/performance/high-performance-storage-systems-for-sql-server/

Reliable Storage Systems for SQL Server

By validating the IO path before commissioning the production database system, and performing ongoing validation through page checksums and DBCC checks, you can hopefully avoid data corruption altogether, or at least nip it in the bud. If corruption occurs, then you have to take the right decisions fast to deal with it. Rod Colledge explains how a pessimistic mindset can be an advantage


http://www.simple-talk.com/content/article.aspx?article=1027

Help, my database is corrupt. Now what?

A corrupt database is probably one of most DBA's worst nightmares. It results in downtime, managers shouting and all other sorts of unpleasant things

In this article, I'm going to explain some of the things not to do to a corrupt database, and then go through some of the things that should be done, some of the scenarios and the fixes for those.

Reporting Services Disaster Recovery

Many organizations now rely on their reporting solutions for day-to-day business as much as the underlying OLTP systems. For some organizations, being able to report on data in real time can be as important as the availability of their underlying OLTP systems. So like any important database solution, you need to plan on how to recover your reporting solution quickly and efficiently, to keep your business running smoothly. For most medium and small companies, that will mean using the tools available to you with SQL Standard Edition.

In this article I will show you how to setup a Reporting Services solution that will allow you to quickly switch from one server to another, should your primary report server / report server database fail or should you need to switch your service to allow you to carry out server maintenance tasks, reducing downtime of the live system.

Managing Data Growth in SQL Server

'Help, my database ate my disk drives!'. Many DBAs spend most of their time dealing with variations of the problem of database processes consuming too much disk space. This happens because of errors such as incorrect configurations for recovery models, data growth for large objects and queries that overtax TempDB resources. Rodney describes, with some feeling, the errors that can lead to this sort of crisis for the working DBA, and their solution.

http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/?utm_source=simpletalk&utm_medium=email&utm_content=DataGrowth-20100125&utm_campaign=SQL

Eating SQL Server Installations for Breakfast

Here you will find wholesome SQL Server installations on the menu, complete with Express, Continental and Deluxe breakfast choices, depending on your application’s appetite. This is the article where your new SQL Server installation is completely yours, having not as yet been turned over to the general populace of developers or users. Enjoy it while you can: From the SQL Server Tacklebox

http://www.simple-talk.com/sql/database-administration/eating-sql-server-installations-for-breakfast/

The 10 Things to Do First for Windows 7

AT A GLANCE:

• Getting to know Windows 7
• Dealing with the latest volume-activation requirements
• Developing a roadmap
• Handling new distributed-security features
• Virtualizing desktops and infrastructures
• Removing users' local-admin rights

When you run your thumb down the list of new features and improvements in Windows 7 (see the feature comparison chart at tinyurl.com/win7featuregrid), you're bound to wonder how you're going to get your arms around all that new technology so that you can deliver it to your users without too much disruption.

Following are 10 steps that can help you accomplish that goal.

Auto generate change scripts in SQL Server Management Studio (SSMS) for tables

As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?  This tip shows you an option that exists within SSMS to automatically generate these scripts for all table changes when using the table designer.

Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1

Today, I am going to show you how to create new source media that will slipstream the original source media and SQL Server 2008 Service Pack 1. Once you have created this drop, you can install SQL Server 2008 SP1 in a single step! These instructions are included with the Service Pack 1 release but there are some issues with the documentation that will be addressed in the next revision of the on-line documentation. There is not a lot of user interface that indicates you are slipstreaming, but there are a few clues, see at the bottom for screen shots.

Microsoft SQL Server 2008 Resource Governor primer

Managing workloads and consumption with Resource Governor

Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation.

It is important to realize from the start that Resource Governor will only throttle processes if SQL Server is experiencing CPU or memory pressure, and it will only throttle incoming requests. For example, a process that consumes high CPU will not be throttled, but the next process may be throttled. Resource Governor is unable to throttle high I/O consumers (processes that read and write excessively from disk) in the SQL Server 2008 release, but this is planned for a future release. 

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

These are the list of database-level permissions.

Handling workloads on SQL Server 2008 with Resource Governor

Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application.

To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first it works for CPU only and second the dedicated CPUs cannot be shared by other SQL Server instances. For example, if there are two SQL Server instances and instance one has been assigned CPU 1 and 2 and instance two has been assigned CPU 3 and 4 on a four processor machine, even if instance one is idle and instance two is in need of additional resources, it can only use CPU 3 and 4.  So what does SQL 2008 offer to solve this issue?

Deleting duplicate Records using SQLCMD

This article presents an approach to showing and deleting duplicate records that is concise, generic and efficient. It uses some features that were introduced in SQL Server 2005.

How to back up Hyper-V virtual machines from the parent partition on a Windows Server 2008-based computer by using Windows Server Backup

This article describes how to back up Hyper-V virtual machines from the parent partition on a Windows Server 2008-based computer by using Windows Server Backup.

To back up Hyper-V virtual machines from the parent partition on Windows Server 2008 by using Windows Server Backup, you must register the Microsoft Hyper-V VSS writer with Windows Server Backup.

To register the Hyper-V VSS writer with Windows Server Backup, follow these steps.