In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Check SQL Server performance at a glance
We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.

In This Issue

Incremental Data Loading Using CDC

Describes a design pattern for using CDC to power fast and efficient incremental data loads. More »


More Uses for Data Explorer for Excel

Microsoft has introduced a new BI product that will help simplify the data discovery phase for Excel users. “Data Explorer” is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access. Data Explorer is a preview product so these features may appear differently in the final release. More »


New Custom Metric: Percentage of blocked connections

This metric reports the number of connections that are currently blocked, divided by the total count of current connections. Most well-balanced SQL Servers will have some degree of blocking. This metric attempts to measure the impact of lead blocking queries against other queries. Higher values indicate that many connections are being blocked, and queries should be tuned to reduce the amount of contention. More »


From the SQLServerCentral Blogs - SQL Server 2014!

Just when you thought you got a handle on SQL Server 2012, here comes the next version!  SQL Server 2014 was announced... More »


From the SQLServerCentral Blogs - Helping Database Administrators

Let’s be honest. Database administration is not all that tough. Set up your backups. Test them. Get consistency checks on... More »


Editorial - The Decision to Fail

We have a number of automated technologies that we can us to seamlessly move from primary to secondary systems without human intervention. SQL Server incorporates a number of these, and many companies use them to ensure their applications are highly available. However things don't always go as planned in a disaster and sometimes humans get involved.

Unless you are one of the companies with a very large budget and high risk of business issues when systems failover, you probably have some sort of high availability (HA) or disaster recovery (DR) process that requires human intervention. Log shipping, for example, usually requires that some human reconfigure the application to use secondary servers. Even with Availability Groups, clustering, or database mirroring, you may need to manually fail back to primary systems.

In those cases, it's not always a clear decision to do so. Many of the switches are disruptive, or have the potential to be disruptive. Cluster fail-overs should not impact the application, but there is a brief period where clients may not connect. Outside of disasters, Management, and often technical people, usually want to schedule any failovers after they have prepared the end user for potential issues, however brief.

In disaster situations, when there hasn't been a complete failure of a system, you may not want to have unscheduled failovers right away. This week I want to know:

How do you make the decision to fail over from one system to another?

I'm speaking to you, the data professional or the administrator. I would guess that most of you are not the one that ultimately makes the decision to leave your primary systems. Often I've found that someone in management has to make the decision, but with input from the technical people. In that case, think about how you present the situation and pros and cons of the failover. Do you give hard numbers, like latency and relative CPU power in failover machines or do you attempt to quantify the effects on the business when secondary systems are in use.

I've rarely had a large budget for secondary systems. Network bandwidth, CPU and memory, and more are sometimes sacrificed in secondary systems in order to align the cost of these systems with the risk of needing them. In many cases, we didn't have automatic failover for many systems because we had to know our primary systems would be down for more than 5 or 6 hours before we would switch to the backup environment.

If you have similar guidelines or processes in place, let us know.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

Today'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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

What will be returned from the last 2 selects ?

DECLARE @a FLOAT(53)
DECLARE @b FLOAT(53)
DECLARE @c FLOAT(53)
DECLARE @T TABLE(I INT IDENTITY(1,1), N FLOAT(53))

SET @a = 0.115
SET @b = 0.075
SET @c = 0.04

INSERT @T
 SELECT @a
INSERT @T
 SELECT @b + @c

SELECT I, N
 FROM @T

SELECT I, N
 FROM @T
 WHERE N = 0.115

SELECT I, N
 FROM @T
 WHERE N > 0.114
 AND N < 0.116

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

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

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

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

Using SQL Server 2012 I execute the following

DECLARE @a VARCHAR(4) = 'r ',@b VARCHAR(1)= 'R' 

SELECT IIF (@a = @b, 'Yes', 'No') AS Result;

Question: What is returned by the select statement? Is "Yes", "No", or an error returned? Meaning IIF is not a function in SQL SERVER 2012? 

Answer: Yes is returned

Explanation: IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server 2012.

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF.

Ref: http://msdn.microsoft.com/en-us/library/hh213574.aspx

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

Insert Commas Into Number String

Function to take a number as a string and return it with commas separating thousands, millions, etc. 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 : Business Intelligence

Report data Model on VS 2010 and 2012 - hi all, I ave one question...I saw that in BIDS we had a Report Data Model on Visual studio 2008. after...

SSRS - DateTime parameter issue - I have deployed a report which has DateTime parameter on sharepoint server. The server is located in india. When i...

Complex and weird requirement : multi-customers Datawarehouse - Hi, We have a project of having a kind of premade-standard-retailler-datawarehouse in the Cloud (hosted on a SQL Server somewhere,...

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - I have created an SSIS package to copy the data from an Oracle database to SQL Server 2005. the package...

SQL Server 2005 : Data Corruption

data corruption - hello experts, i executed DBCC CHECKDB command on my 600 GB database and got below error message. "found 1 allocation errors and...

SQL Server 2005 : Development

Time out Expired - i am using CTE in following ways with CTE1 ( select * from xx ), CTE2 ( select * from test inner join CTE1 CT on test.id=CT.id ) , CTE3 ( ...

SQL Server 2005 : SQL Server 2005 General Discussion

DTSX Variable Issue, can't distinquish between "42" and "00" - Correction to the title... Can't distinguish between "42" and "IM" I have a For Loop that iterates through text files and...

SQL Server 2005 : SQL Server Express

SP for inserting max(eid)+1 no for all fields - tableA has eid field which stores the last eid value from tableB when records are added in tableB records are saved...

SQL Server 2005 : SQL Server 2005 Integration Services

how to write expression for Derived Column transform - I need to filter out all records as follows: where [Month 01] is null and [Month 02] is null and ...

no matter how large the destination column eg. nvarchar(max), excel source choke on column - I'm using VS2010 BIDS, importing from Excel 97-2003 .xls worksheet. I've got the following config: Excel Source -> Conversion Split Transform...

Problems with excel 2007 import using SSIS 2005 - Hi, I am trying to import data from excel 2007 into my sql server 2005 database using SSIS 2005. SSIS 2005...

SQL Server 2005 : T-SQL (SS2K5)

How to create a repeatable +ve integer from a nvarchar? - Hi, I need to create an id from a nvarchar value that is repeatable, sort of like a checksum or...

Emailing in HTML - Hi All, I am trying to format an auto-generated email from SQL into HTML, i've sussed a bit of it...

SQL Server 7,2000 : Administration

Maintaining a 2nd DB y - Hi all, We keep an offsite copy of a live DB. We currently refresh thus; 1) Back up the ENTIRE live...

Changes the database which specified spid context to the specified database - hi, I want changes the database which specified spid context to the specified database A spid:53, current database :test B spid:54, current...

SQL Server 7,2000 : SQL Server Newbies

attempting to recreate complex "multi-layer" Access queries in SSMS - Please excuse me if Im not usung the right terminology. I am a SQL Server newbie and have a basic...

SQL Server 2008 : SQL Server 2008 - General

TVCs - Table Value Constructors.... I've been trying to do some research and some tests and I haven't come across anything that...

How to output multiple columns one column depending on its values - Hi, I have a table where I have amount and rank columns. It is basically an approval workflow. there can be...

linked server issue - Hello, I have setup a linked server between SQL Server 2008 R2 and MAS90 server and I am able to...

How to get the list of tables that has specific column name from linked server, available in SQL SERVER 2008 R2 - Hi, I have an access to oracle linked server called [oracleLS] it is there under the SERVER OBJECT => LINKED SERVER =>...

Error converting Varchar to Float - Hi All, I am trying to use the query below but receive the error message "Msg 8114, Level 16, State 5,...

prevent simultaneous update - Hi I have a table where multiple Agents can assign tasks to themselves. So I basically pull the minimum TaskId which...

Profiler shows long duration for sp but actually it is not - Hi I have a sp which is timing out on my website. When i run a trace on it, duration comes...

SQL Server Transactional Replication - We did a performance audit on the system and the reports revealed we should change the Clustered index (the primary...

Certification Question: You need to ensure the successful recovery of any single database from a catastrophic failure without requiring backup data center in a different location - You need to ensure the successful recovery of any single database from a catastrophic failure without requiring backup data center...

Service Broker Question - End Conversation command - Does "END CONVERSATION" send a message back to the actual queues? The reason I'm asking is due to some code I've...

Transposing a table from multicolumn to key-value - I have a transactional table with 100+ columns and each row is uniquely identified by an id column. For a...

Division problem in SQL - Hello All, Can anyone please explain why do we always get 0 when we divide 1 by any other larger number. select...

How to copy new records and updated records from a database table in local server to database table of remote server - Hi I am having locla server and remote server. In the local server ,i have database table which is inserted,updated regularly.There is...

Calculate Page number - I have a table that I need to return with a dynamically calculated page number. The number of items on...

How can we find the Null values on indexed columns from all tables in SQL Server. - Hi, Please help me on finding the Null values on indexed columns from all tables in SQL Server. I need a SQL...

SSIS Derived Columns - I am new to SSIS. There is a data flow task for a derived column. I want to have a...

Transaction replication has many undistributed commands, but tracer token arrives immediately. - hi i have sql server 2008 R2 with transactional replication. In general this replicated database that normally performs very well,...

sql server transactional Replication - We are running SQL Server Transactional Replication and we selected a few tables to be replicated. One of the tables...

Issue while joining two tables with no unique columns - I have two tables with no common columns: Graph_Range Start_Range End_Range 0.10 0.20 0.20 0.30 0.30 0.40 Graph_data Asset_Value factor...

Fizz Buzz interview questions for phone screens? - We're trying to find basic SQL interview questions where the candidate can answer over the phone. There are surprisingly a...

Querying sys.dm_db_partition_stats instead of sys.sp_spaceused - We have a daily process that saves into a table the storage info returned by the SP, but it's procedural,...

Performace issue while updating records and trigger on table - Hi All, I am having a performance issue while updating records into sql server table, I have created below trigger to update...

Using the same partition function and scheme for multiple tables - I have a database where most of the tables have an integer field say "SetId" denoting a batch of data. If...

DROP PK on highly called big table - HI I have a table with 80 million records with PK on an int column. A service selects data from this...

how to archive data based on on archive it self? - Hi I have an archive tabel in Oracle. We copy this to a ms sql 2008 r2 database for datawarehouse purpose. The...

Google Analytics SQL Import - Hi All I was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS? A quick google...

read registry w/o sysadmin, is it possible? - Hi, in my SQL Server 2008 EE SP1 environment, one of the application needs to use xp_instance_regread for reading registry...

Invoke or BeginInvoke cannot be called on a control until the window handle has been created - Hi I am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the...

SQL Server 2008 : T-SQL (SS2K8)

Query if ANY record between specific time - So, say I have a record where [i]StartTime[/i] is [b]'2013-06-07 13:00:00.000' [/b]and [i]EndTime[/i] is [b]'2013-06-07 13:02:00.000'[/b] Is there a way to...

Case statement with subquery - I have a CASE statement with a subquery that works most of the time but if the subquery comes back...

Money not accepted a datatype - I have the following: DECLARE BalanceDue money, ktr int but I get a red underline under "money" and the message I get...

Primary Key Violation when inserting records in table - I have stored procedure which inserting records in two table.both table have primary key and when i am running that...

Adding time - I need to add time to a value. The value is stored as char(4) - example '0630'. I know to use the...

help getting return of function and loop into stored proc - Hi, I have a tricky issue I am struggly with on a mental level. In our db we have a table showing...

Update Performance - Hi Guys, Any Ideas what the best way would be to perform this update? UPDATE D SET Track_ID = P.Track_ID, Territory_ID = P.Territory_ID, Major = P.Major FROM Staging.Track_Mapping P INNER JOIN Staging.Track_Play D ON P.ISRC = D.ISRC_Code AND P.Event_Date = D.Event_Date Both tables...

update for My table? - Hai friends, my table structure is create table journey ( journey_id int identity, fromplace varchar(44), toplace varchar(44), mode nvarchar(20), seattype nvarchar(20), status_id int ) insert into journey '2013-05-10','chennai','Mumbai','Air','Business','2' how to write...

truncate error - foreign key and check constraints - Hi. could not truncate table due to FK constraints Table defination [code="sql"]/****** Object: Table [dbo].[DiagHistory] Script Date: 06/07/2013 10:49:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER...

Creating hierarchical metadata based on DMVs or system tables - I have a requirement to archive & purge my OLTP data. The database is a tightly defined relational database with Primary...

Running a DELETE Stored Proc but finding degradation in Performance in WHILE loop iterations increase - 1st time/long time, Quick question in general. I have a table i'm trying to purge a table. i'm deleting using a...

Forcing a row to be related to at least another row - Hallo I need to implement this: A row in table A must be in relationship with [b]at least one[/b] row in table...

Combine columns into One with Delimiter - I have the following: [code="plain"] Create table Table_1 ( [col1] varchar(10) null, [col2] varchar(10) null, ) Insert Table_1(col1, col2) values ('Bill','Smith'),('Bill',null),(null,'Smith') select rtrim(isnull(col1+'/',''))+rtrim(isnull(col2+'','')) as firsttry from Table_1 [/code] This returns: Bill/Smith Bill/ Smith I...

APPLY versus ROW_NUMBER versus double JOIN to retrieve most recent record from a foreign key table - I've been mulling over this for a while, and there's something about using the APPLY function (OUTER APPLY in this...

SQL Server 2008 : SQL Server Newbies

slowly changing dimension? - Hi everyone. I've got to figure out whose games scores changed the least over time. I've created DDL to illustrate [code="plain"] create table...

dynamic joins - Hi , What would be the best way to dynamically self join a table to create the example contained with the...

theoritical size of an index - I have an index occupying almost 77 GB space on the drive which is 70 percent fragmented. I am trying...

Importing/formatting MYSQL time date into SQL as char - i have created a view in MYSQL which is imported into SQL Server. The originating MYSQL table column definition i...

Inline Table Valued Function - Hi, I would like some help with my first inline table valued function, I have to create one that builds a...

SQL commands to learn - Can anybody recommend a good online guide to SQL commands? Or at least a list of the ones I should...

Calculating Sales History for Months/Years - I have a table - TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty The TrnYear and TrnMonth are the Financial Periods and dont always tie...

get lookup data - I have two tables, lets say Table 1 and Table 2 as below Table 1: Col1 Col2 Col3 1 _A 11 2 _B 12 3...

SQL Server 2008 : Security (SS2K8)

Recovery Model - SIMPLE V/S FULL - Hi, If we having Full backup every night but it's set up FULL Recovery model, I have a question that if...

SQL Server 2008 : SQL Server 2008 High Availability

Large scale Transactional replication solution (1200+ Pub & Subs) - So I have been working on a large scale replication project that has suddenly come to a standstill and I’m...

Please Please help me- upgrading to sql 2008 R2 enterprise edition from sql standard edition on SQL Failover Cluster - I'm currently looking into the possibility of configuring a 2008 R2 failover cluster for my organisation. I'm curious as to...

In Logshipping, Restore job is using the full backup and restoring every time and not taking the t-logs - hi experts, I have two servers, one is primary and another is secondary. i have created backup and copy job at...

Logshipping Configuration error:3201 - [b]Hi am configuring Logshipping on SQL Server 2008 R2 servers, my primary and secondary both servers are same editions and...

Load on publisher in case of PUSH replication - Suppose we have distributor configured separate than publisher. And we are using PUSH method for transactional replication. Then what will...

Need to install ssqlserver service pack sp4 in a cluster server - Need to install sqlserver service pack sp4 in a cluster server 1.what are the pre-requesties i have to follow 2.cluster...

SQL Server 2008 : SQL Server 2008 Administration

Any Tool / Application to read SQL Server Database Fragramentation ? - Hi I was wondering does anyone know of any tools that are available for SQL Server 2008 R2 to see...

IP Address Doesnt turn from offline to online - :unsure: Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything...

Restore backup on lower version - Hi, Is any one know to restore backup of 2008 R2 on 2008 (higher to lower)? Thanks in advance.

Dead lock problem - i am trouble shooting one dead lock problem on SQL Server 2008 R2 SP1 which is weird for me i...

SQL Server Version upgrade, Production DB's Left in Previous Version Compatibility - Hi guys, I was just wondering if it is safe to permanently run a production database in a newer version of...

Access to Snapshot only - I want to give a group of users access to run select queries on a database snapshot but not the...

Programming : Connecting

Serious problems to run SSIS packages from Sql Server 2012 64 bit platform against Oracle 10.2g database over ODBC. - Hello, (the same is attached in rtf doc if better) Any help would be appreciated, I've run out of time and ideas...next...

Programming : General

Counting weekdays within a date range and grouping them by month - Hi Folks, I was hoping I can get some help and your expertise on below reuirement. I have this data where first...

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

Move Report Designer (rdl) to Report Service (rdl) - Can I take a rdl/txt file from Report Designer (2.0) And try to upload it to Reporting Service ? Do I...

Interview Questions for a Jr. Level SSRS Report Developer - Hey guys! First, I apologize if this is an inappropriate question for this subforum, but I could use some advice....

SSRS CustomReportItem deploying issue. - We have a custom report developed in 2005 item that works fine in design and preview mode while in Visual...

Client side function error - I am trying to add a function to a subreport but continually get the error 'subreport could not be shown'. I...

SQL Query - I have the following query: SELECT Sales_Intrastat.SalesID, Sales_Intrastat.Year, Sales_Intrastat.Period, Sales_Intrastat.Sales, Sales_Intrastat.CostOfSales, InstrastatCustomers.Depot, InstrastatCustomers.AccountName, InstrastatCustomers.CountryCode, InstrastatCustomers.AccountNumber, CountryCode.Description, InstrastatCustomers.ContractC

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...

Reporting Services : Reporting Services 2005 Administration

SSRS Error: Failure sending mail: The report server has encountered a configuration error. Mail will not be resent - Hi All, I have been trying to send ssrs report to users via email and i followed all the rules like...

Data Warehousing : Strategies and Ideas

Log ship to local db for ETL source data - I'm using log shipping to maintain a failover copy of my main db on a secondary server. I'd like to...