SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

DR Failovers

Almost everyone struggles with setting up disaster recovery (DR) plans and resources. There are a few companies that take their DR seriously, but for most organizations, it's an afterthought. It's an insurance premium that can easily be avoided if there are not pressing problems and your past experience with disasters is minimal. After all, it's rare that any of our data centers shuts down because of an earthquake, hurricane, fire, or other similar large scale event.

However most of us try to have some type of disaster recovery in place. We may have cold or warm systems available. Our companies have funded an AlwaysOn Availability Group, or more likely, mirroring and/or log shipping for critical systems where data is moved to a remote location on a regular basis. We monitor these processes, and we try to keep them running, though I'm sure if they break, many people don't give the repair top priority in their daily work.

A DR environment is like a backup. If you don't test it, you're never sure if it really is something you can use in a disaster situation. You may periodically test your fail-over, like you test a restore, but do you ever really lean on the secondary system? This week, I wanted to ask you this question:

Do you fail over to your DR system and run your business from the system for a day (or week or longer)?

I know a few companies that consider secondary systems to be critical and will actually fail over, and then run the other system for a few months, failing back to then retest the primary system. In this case, there really isn't a primary and secondary system, but rather two systems that can work, being alternately used throughout the year.

This is actually moving closer to a cloud architecture model, where you don't place high importance on any particular system, you assume any system can fail, and you have redundant systems that can pick up the load. In a cloud environment you might have more than two, relying on dozens of systems instead, any of which could fail, but with a small interruption in service.

I'd hope that SQL Server, the version of the platform I can install in my data center, would get close to this, allowing me to serve database services to clients, but seamlessly moving those services across instances, with clients unaware when physical machines have crashed because their services just moved to another host.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


Video and Audio versions

Toda's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. You can also follow Steve Jones on Twitter   to find links and database related items and announcements.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video (17.6MB) feed

MP4 iPod Video (21.2MB) feed

MP3 Audio (4.2MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center


ADVERTISEMENT

Use Deployment Manager for free! For up to 5 projects. Try it out and deploy software and databases faster and easier than ever.

ADVERTISEMENT
SQL Backup Hosted

Got your 5GB free hosted storage yet?

Back up your SQL databases to the cloud using SQL Backup Pro. You’ll get your first 5GB of storage free. Try it now

SQL Data Compare

Save time when comparing and synchronizing database contents

"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.

SQL Monitor

Optimize SQL Server performance

“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

Featured Contents

 

Formatting Dates with 3 Character Months (SQL Spackle)

Jeff Moden from SQLServerCentral.com

Another in the series of articles to help you "fill in the cracks" in your T-SQL knowledge. MVP Jeff Moden shows us a super simple, high performance method to solve this timeless problem. More »


 

New Resource: LearnSomeSQL.com

Additional Articles from SQLServerCentral.com

We're trialling a new resource for SQL beginners and we'd like to know what you think. More »


 

SQL Saturday #234 Baton Rouge, LA

Press Release from SQLServerCentral.com

SQL Saturday is coming to Baton Rouge for a free day of SQL Server training and Networking on August 3. There is also a pre-conference session presented by Bill Pearson on Practical Self-Service BI with PowerPivot for Excel on August 2nd. More »


 

Telemetry basics and troubleshooting

Additional Articles from MSDN Communities

From the MSDN Windows Azure blog - We recently introduced Azure CAT team series of blog posts and tech articles describing the Cloud Service Fundamentals in Windows Azure code project posted on MSDN Code Gallery. The first component we are addressing in this series is Telemetry. This has been one of the first reusable components we have built working on Windows Azure customer projects of all sizes. More »


 

Disaster Recovery Tip #4 - Learn about partial restores!

Press Release from SQLServerCentral.com

Sometimes recovery simply entails rerunning a failed process. More »


 

From the SQLServerCentral Blogs - SQLSaturday#235 NYC - The Schedule You All Have Been Waiting For!

Robert Pearl from SQLServerCentral.com

Late last night in the Halls of Congress, a grand gathering of planners took place to form that which we... More »


 

From the SQLServerCentral Blogs - SSAS Joining Facts at Different Granularities

MikeDavis from SQLServerCentral.com

In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example,... More »

Question of the Day

Today's Question (by Koen Verbeeck):

What is the most recent name for the development environment in which you create BI solutions for SQL Server?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: SSIS.

Did you miss yesterday's question, TDE Setup? Answer now.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server Concurrency

If you've designed your SQL code intelligently, and implemented a sensible indexing strategy, there's a good chance your queries will "fly", when tested in isolation. In the real world, however, where multiple processes can access the same data at the same time, SQL Server often has to make one process wait, sacrificing concurrency and performance, in order that in order that all can succeed, without destroying data integrity. Transactions are at the heart of concurrency. I explain their ACID properties, the transaction isolation levels that dictate the acceptable behaviors when multiple transactions access the same data simultaneously, and SQL Server's optimistic and pessimistic models for mediating concurrent access. Pessimistic concurrency, SQL Server's default, uses locks to avoid concurrency problems. Get your copy from Amazon today.

Featured Script

Find Default Data and Log File Paths

Will Spurgeon from SQLServerCentral.com

A function to wrap Alex Aza's "find default paths" solution.

http://stackoverflow.com/a/12756990/377058

Uniquely fails back to [master] settings if defaults are not explicitly set.

Tested on SQL 2005.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Office/Administrative Assistant ! - :-) We are looking for a professional office assistant that is self-motivated, detail-oriented, energetic and highly organized. Prior office/administrative assistant experience...

DB Free space issue - Hello, we have a vendor DB which always keeps around 3GB of free space. It is sql 2005 SP3 and DB Data...

Best Index Rebuild/Reorganize and Update Statistics Strategy - I have a several databases in full recovery model around 200 GB. As soon I run maintenace plan, log fills...

sql backup to avamar - Is anyone using avamar backups to do live backups of sql server? are there any issues, are there problems? AW

SQL Server 2005 : Backups

SQL Server Backups & Maintenance - Best Practices - Hi, I was wondering if you guys would be able to shed some light on a subject that seems very involved! We...

SQL Server 2005 : Development

SQL Query to find out MAX Qty and MAX Childitem of a parent item - Hi Folks, I need to write a query to find out all the parent items which has a MAX of Qty...

SQL Server 2005 : SQL Server 2005 Compact Edition

total time difference between more than two dates - advance thanks to all...can anybody helpme soon........... i am using sql server 2005........i want to find out time difference between more...

SQL Server 2005 : SQL Server 2005 General Discussion

Different ways of checking for an object - From the "Things You've Probably Wanted to Ask About SQL Server but Didn't Want to Sound Like a Noob" department. Something...

Service Broker not enabled for this DB - We lost a sql server disk array. Had to be re-raided. Then system disk and registry was restored from tape....

Index rebuild date/time - For some unknown reason today “ReIndex_All” job was running almost 8 hours (instead of normal 20 minutes)-eventually I canceled it....

SQL Server 2005 : SQL Server 2005 Integration Services

Do not fail package if the Data Flow Task fails - hi there, I have a specific requirement, but do not know how to implement. "Extract Data from DB server A into DB...

History data Comparision and then Update flag on target table. - Question :1 Server : USING sql server 2008 R2 a) I have "prestaging" table as source table. b) I kept working table and History...

OLEDB Command fails pre-execute phase - Im using an OLEDB Command to call a certain procedure, which is failing at runtime with the following error: (Source:...

SQL Server 2005 : T-SQL (SS2K5)

Contains Exception - I am using sql server 2005 [b]The filed r_resume_text data type is text When i run the Below query i am getting...

SQL Server 2005 : SQL Server Newbies

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : General

Stored Procedure Repeating Last Line Of Loop - I have various stored procedures that generate and email reports. They always duplicate the last line of the report. I...

Using the ROWGUIDCOL attribute on primary key - Hi! I'm using unqiueidentifiers as my primary keys for all tables with a default value of NEWID(). Are there any advantages/disadvantages...

SQL Server 7,2000 : SQL Server Newbies

SSRS Report Manager Error - Hi Everyone, Hope you could help me with this. I have set up Reporting Services and when I've tried accessing the...

SQL Server 7,2000 : T-SQL

Dynamic Pivot for multiple columns - Hi, Here is my friend's sql server version info : Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright...

SQL Server 2008 : SQL Server 2008 - General

HEAP - Hi, I am looking for a script that will give all Heaps (user tables only ) in a sql server 2008 database....

How to dynamically create my FILEGROUP during a restore? - How can I dynamically create my FILEGROUP during a restore? My FILELISTONLY renders [font="Courier New"]LogicalName PhysicalName Type FileGroupName =========== ============ ==== ============= MyDBData_Primary G:\SQLData\Archive_Primary.mdf D PRIMARY MyDBData_1...

Identity property - Hi, I have a table with some columns like Table name:Mydata columns:sid,sname,course with some data. so, can i add a Identity property to the sid...

Force SQL jobs to run serially, not in parallel - In SQL 2008 R2 (sp2) our vendor requires that thier 14 SQL database FULL backups execute in a sequential order....

Active Users - Hi, How do i find out the number of users having an active transaction at the moment by using T-SQL Statement

New server. How to migrate? - My web site uses MS SQL Server. I ordered a new server for DB and want to migrate from old...

SQL Replication details - Hi Can anyone point me to any DMV or anything else that can provide a history of when information was replicated?...

LS - what happens to the logshipping when we delete tuf file ?

Indexes with INCLUDE columns - Good Day. Include columns sound wonderful , but I would like to know is How many columns should be allowed as INCLUDE...

How to get exact figures in fraction as in execl in sql server. - Hi, In Excel 29/30 gives me 0.966666667 that is 0.97 But In Sql it gives as 0. I had tried the following code,but...

Tell me if I'm crazy - Recursive CTE + View (Materialized, indexed?) - First off, I'm a .NET developer, and not a SQL wonk. Please be gentle. And thanks for your help. So I...

using between on string columns - This is puzzling. I've run across a case where using between on string columns returns rows I'm not expecting. The following...

SQL Netbackup Job failures - Hi all, I have an instance where there are 2 databases and it is backed up by Netbackup MS sql client...

sys.dm_db_index_physical_stats - Doubt in the Results - For a table with 3 indexes, 1 clustered and 2 NC, Index Physical status dmv returned 53 rows with clustered...

SQL 2008 - Connecting to Integration services - From my SQL 2008 installed development machine, to connect to Integration services of an SQL 2008 server, do I have...

Log shipping: New Index Not carried over - I have a few databases that are log shipping from ServerA to ServerB where they are available as a Standby/Read...

using the default trace - Hi Late in the afternoon i had a support ticket raised for 'the app'. It was running slow around 9.30am untill...

Join of 2 subqueries - Hi All, I've been trying to do a join of 2 subqueries, but haven't been having much luck and keep getting...

create proc - create procedure insyenkcek @phone nvarchar(20),@SertiNom nvarchar(50),@ASA nvarchar(100),@telUnvan nvarchar(100),@kateqoriya nvarchar(100) ,@SHesab nvarchar(20),@Alam nvarchar(60),@IDCDMAalam nvarchar(30),@TelTarix nvarchar(50),@MuqBasTar nvarchar(50) ,@MuqSonTar nvarchar(50),@Milliyet nvarchar(60),@Cinsi nvarchar(50),@DoYer nvarchar(100),@DoTarix nvarchar(50), @SexVes nvarchar(60),@Elaqe nva

Find the host name - Dear, I require to know the host name from which my database has been accessed I mean performed DDL,DML operations within...

Selecting records in table only when ALL related records in other table satisfy multiple conditions - Hello and thank you for your time. I feel like this should be simple but I can’t seem to work...

Sending mail from On Prem server to Off prem exchange - Im having some troubles getting my SQL mail setup using an off premise exchange with office 365. I can get...

SSIS in cluster - I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these...

Insert bulk failed due to a schema change of the target table. - Hello Expert. First time I used sqlbulkcopy its performance is impressive, however my application threw a SQL exception after processed about...

SQL Service Restarted - Fault with MSVCR80.dll - Hi All, Our production SQL Server service stopped and restarted automatically in 2 mins. It is SQL Server 2008 R2 Enterprise...

SQL Server 2008 : T-SQL (SS2K8)

How to Parse two string Columns using a Function - I need to parse the sample data below as shown. Please help me in writing a TSQL Function. Create Table...

Error inserting data with T-sql - Hello I am inserting data from one table to another using update,set command and getting the following error. [center]update XDDDepositor SET XDDdepositor.WBeneName = Vendor.RemitName, XDDDepositor.WBeneAddr...

SP Date parameter default to GETDATE()? - I want my procedure to include an optional date parameter that defaults to the current date. Why does the following...

SPs inside BEGIN/ROLLBACK TRAN - When testing Stored Procedures, can you run something like [code="sql"] BEGIN TRAN EXEC sp_deleteSomeRecords "Varchar Parameter", 999, NULL -- COMMIT TRAN -- ROLLBACK TRAN [/code] ... to...

delete top 100 - hi my query is giving me erro delete top(1000) from table a join table b on a.id = b.id and b.date < getdate() error: incorrect syntax...

Need help with Select Where clause using parameter equal blank or in a list - I have a stored procedure that passes in several parameters that can be blank or a list of selected filter...

Index and Table Size - Hello Everyone Happy hump day to all. I am working on gather some stats for each table in a database. I am...

check if index exists - hi i am creating script for non clustered , i need to see of particular index on column on particular table exists...

previous week query 0700 - 0700 - HI all I have been asked to generate reports from my c# package I have created every Monday at 0700hrs,...

SQL Server 2008 : SQL Server Newbies

Table Join on three columns - I have two tables with identical structure. one table is for month1 the other is for month2 i want to compare...

Not your usual documentation question - I am a server/SQL admin in a large organization. We have thousands of SQL databases on many clusters and individual...

SQl 2008 R2 - Maximum memory - Hi All I am using sql 2008 R2 DB. As per the msdn document max memory that can be allocated to...

Prev and Next Row Without RowNumber - Hello All, Can anyone help me with this one. here is the table sample. [code="sql"] --------------------------------------------------------------- CREATE TABLE [dbo].[Invoice_t]( [Cost_Center_code] [int] NOT NULL, [Payment_code] [int] NOT NULL, [INV_No]...

SQL to combine rows based on dates and other common factors - Hi All, We have a database for employees, jobs and work allocation. I am trying to find an efficient way to...

Stupid Question: Return Object Explorer to It's original position - Stupid question but I accidentally moved object explorer from it's original default position on SSMS. By default, object explorer is...

SQL Server 2008 : SQL Server 2008 High Availability

I need cluster installation information - Hi all Here we have to setup cluster environment for one of my client. Here we are having the 500gb hard disk...

DB Mirroring for transactions committ - Hi All, Under high safety mode for DB Mirroring , the transactions will get committed in mirror server first and it passes...

Change Compatility level in active mirroring session - Hello, i've changed the compatibility level on the principal mirrored database but this change has not been apply to the second...

Moving storage (SAN hp -> eva) with SQL Mirroring - We have a request come in to have the storage moved form HP--> EVA. The server that is part of...

SQL Server 2008 : SQL Server 2008 Administration

Transaction Log growth out of control - Last weekend I re-indexed some Databases. Before doing so I backed up the Databases and Set them to simple recovery mode. After...

Server crash during load testing - After a peak load test carried out for an application for two consecutive days, the DB server got automatically bounced...

SQL Server available memory during load test - On a Database server, Buffer pool is configured to utilize 80% of RAM and remaining 20% is for OS. At the...

Backup strategies and scheduling - Our DBA has left the company and I have just inherited the daily job of reviewing backup jobs to ensure...

SQl 2008 R2 enterprise edition compressedn backup to be restored Standard edition - Hi I have a SQL 2008 R2 enterprise edition compressedn backup which is to be restored on SQl 2008 R2 Standard...

Transaction Log Backup fails Exclusive access could not be obtained because the database is in use - I have an AM and a PM TransactionLog Backup. The AM runs fine the PM fails I get the following error. Date 07/15/2013 03:00:00...

Upgrade from Sql 2003 to Sql 2008 - Hi, We are planning to upgrade from Sql 2003 to Sql 2008 and I would like to know what needs to...

litespeed restore - we have db backup file(litespeed backup file). Litespeed is not installed in the server. we need a command to restore the litespeed...

Avamar Transaction Log Backup Issue - Hi, We just recently started using Avamar to backup everything including an hourly SQL server transaction log backup. The issue we’re...

System-health extended-event session does not capture latest deadlocks - While running the following query to capture the latest deadlocks recorded in the default system-health extended-event session, I noticed that...

Cannot access tempdb properties - Immediately after a reboot we can access the properties of the tempdb. However, a few minutes later when you try...

Programming : SMO/RMO/DMO

SMO.Scripter: Help Walking a DependencyCollection - Hi there I am working on automating the process of scripting creates for our SQL Views and object dependencies of...

Programming : Powershell

Open Excel Error Using PowerShell in SQL Server Agent Job - I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

SSRS Report : Help Me to display text box values in Header of the report - Hello guys, sorry if I am posting in the wrong section of the Forum, I am looking for the help on...

Difference between site setting and folder setting in SSRS Report Manager 2008 R2 - Hi, I have read lot of articles on MSDN websites but still not very clear on what is exact difference between...

Deadlock - Hi, I've been doing some monitoring on one of our SSRS servers and have picked up a dead lock that occurs...

Data Warehousing : Integration Services

Using SSIS to move some files and then delete them - I am trying to use SSIS 2008 R2 to move some files and then delete them from the source. I created...

Data Transfer - data not transfered in order - hi there, I have a table with 371 751 rows. I have a standard SSIS package that transfers data from table...

Data Warehousing : Analysis Services

Using SSIS to move some files and then delete them - I am trying to use SSIS 2008 R2 to move some files and then delete them from the source. I created...

Cube processing failures in sql server analysis services 2008 r2 sp2 - Hello all, Does anyone know how to debug cube processing failures in sql server analysis services 2008 r2 sp2? SQL Profiler doesn't...

Can I join a cube to a DB? - Hi, Brand new with cubes, so please excuse my ignorance. I have a cube that has overtime budgeted values. I have a...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com