SQL Server 2008 and 2008 R2 Integration Services - Consuming Web Services Using Script Task

The omnipresent nature of Web-based technologies expanded the concept of distributed environments with the ability to utilize services hosted on remote servers accessible via standard Internet protocols. This capability can be easily leveraged in SQL Server 2008 R2 Integration Services by employing Web Services Task, whose characteristics have already been covered in detail on this forum. However, in some cases, it might be required to reference Web Services directly from within a Script Task code. We will describe how such a need can be accommodated in this article.


http://www.databasejournal.com/features/mssql/sql-server-2008-web-services-script-task.html

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/

Use ACE Drivers and PowerShell to Talk to Access and Excel

Quit using deprecated JET drivers to talk to Microsoft Access and Excel. Learn how to use Windows PowerShell and ACE Drivers...


http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/04/use-ace-drivers-and-powershell-to-talk-to-access-and-excel.aspx

Obtaining Identity Column Values in SQL Server

When you enter a new record into a table that contains an identity column, the identity value will be set with the next available identity value. Lots of times your database design requires you to obtain the value that was used to set the identity column. The identity value will be used to populate other tables that need to have a foreign key relationship with the inserted record. Therefore you need to be able to return the identity column value of the newly inserted record.


http://www.databasejournal.com/features/mssql/article.php/3931466/Obtaining-Identity-Column-Values-in-SQL-Server.htm

SQL Server Foreign Key Update and Delete Rules

Foreign key constraints are an integral part of SQL Server database design. These are used to maintain integrity among related data in different tables. While implementing update and delete operations on values in the parent table (referenced table with primary key) we have to consider the impact on related values in the child table. SQL Server provides different rules for managing the effect of updates and deletes on child table values. How can these rules be used effectively without threatening the relational integrity?


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

Continuous Integration for SQL Server Databases

When it comes to the subject of putting databases into source control, Troy Hunt is no shrinking violet. “Database source control is no longer negotiable”. In this article, published originally in his Blog, Troy explains how to do Continuous Integration from a database that is in a Version Control System (VCS).
http://www.simple-talk.com/content/article.aspx?article=1247

You’re deploying it wrong!
http://www.troyhunt.com/2010/11/you-deploying-it-wrong-teamcity.html

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

Geocode Addresses in T-SQL

This article shows how you can "geocode" street address information natively in T-SQL with MS SQL 2005, and as a byproduct, how you can validate, normalize, and parse address information.

Note that this approach will work in SQL 2008 as well, though SQL 2008 provides geospatial types and functions that could be used in lieu of the numeric data type used here.

Geocoding is the process of converting a geographical address into latitude and longitude (GPS coordinates). Geocoding address information has a number of benefits:

• Means to interoperate with web-based mapping services
• Means to interoperate with GPS devices
• Makes it possible to calculate distance between addresses
• Makes geospatial analysis possible (i.e. identifying geographically similar addresses, etc.
• Compact way to represent a geographical location
• And more...

Map IP Address to a Geographical Location

On the Internet there is the concept of web site analytics that helps track all the visitors' activities and usage patterns. One of the dimensions to track is the geographical information of the visitors, which can be obtained by using the IP address information that is collected when a user lands on a Website. In this article, I will describe a simple process that enables your reporting system to display the geographical information of the visitors.

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/

Laying out SQL Code

It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create an overall logic to the way it is done?

http://www.simple-talk.com/sql/t-sql-programming/laying-out-sql-code/