In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
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.
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
sqlinthecity SQL in the City London - Free SQL Server training the Red Gate way
SQL Server MVPs, ingeniously simple tools and an awesome training experience. Find out more and register for the next event happening in London this June.

In This Issue

Data Mining Introduction Part 5: the Neural Network Algorithm

This is the 5th article about Data Mining with SQL Server. This chapter is about Neural Networks. More »


Paging Data with T-SQL

Greg Larsen shares a number of different TSQL methods to provide paging functionality, for when your application requires that you display only one page of data at a time. More »


From the SQLServerCentral Blogs - Object Qualification

I came across an interesting issue recently with NHibernate, now it is widely known I despise ORM’s, in my experience... More »


Editorial - Understanding HA

I've learned a lot about high availability (HA) and disaster recovery (DR) over the last two decades. Some of the things I've learned were from studying, some from experimentation and practice, and a few from hard lessons when some part of a system failed. The latter were how I learned the quickest and most effectively, but those were also the most stressful times in my career. Getting called by senior management because a system is down is not the way that I want to improve my skills.

I know many people struggle with HA and DR, which are two different concepts. They are designed to solve different problems for different situations. SQL Server includes a number of options for how you might handle either HA or DR situations (or both), and the list of options continues to grow over time. We have the AlwaysOn suite of technologies, database mirroring, replication, log shipping, and more.

All of the technologies that you might use to solve HA and/or DR problems may provide other benefits. The ability scale out, or spread load are a possibility with a few of these features. What I find, however, is that many accidental, junior, or otherwise inexperienced technology professionals that need to manage SQL Servers get confused. They seem to think HA and DR are the same thing, and one technology is somehow better than others.

Whether you need HR or DR, or some other solution, it's important the you research, understand, and practice working with the technologies that you will implement. Books Online gives you a good starting point, and there are plenty of online resources where you can ask additional questions or debate the particulars of your situation

Disasters rarely occur, but you should take the opportunities to hone your skills at working with the various technologies you may implement before things fail. Schedule failures of systems in your test environment. See if your HA technology keeps the system running if you remove a system, or test that you can recover using your DR techniques. When something actually goes wrong, you'll be glad you prepared.

Steve Jones

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

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


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. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:


Question of the Day

Today's Question:

You manage a 2008 R2 instance with a PROD_DB. You want all login events to be stored in a separate database called AUDIT_DB containing just one table for that purpose. You create the AUDIT_DB, enable broker on it, create a QUEUE, create a SERVICE on that queue and ROUTE on that service. Then you create EVENT NOTIFICATION ON SERVER FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED and define a stored procedure in your AUDIT_DB that receives any login events from the QUEUE and inserts the login data into the AUDIT_DB.

All works fine and you forget about it until a few months later, when you find out that someone must have dropped the AUDIT_DB, because it is no longer there. In addition, you notice that one of the remaining databases grew unusually large (over 500%) and keeps growing every day. Which database is it?

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

This question is worth 1 point in this category: AUDIT_LOGOUT. 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

What will the following query return?

CREATE TABLE #Temp
(FirstName varchar(20),
 LastName varchar(20)
)

INSERT INTO #Temp
SELECT 'John', 'Smith'
union all
SELECT 'Joseph', 'Robertson'
union all
SELECT 'Robert', 'Jefferson'

SELECT substring(LastName, CHARINDEX('s', LastName)-1, 10) Name  from #Temp

Answer: Smith, tson, rson

Explanation: CHARINDEX() finds the first instance of the first value you provide and then returns the position. In this case, we take the position number and minus one. For Smith, we don't substring it, since S is the first character. For Robertson and Jefferson, we find the s and then go back one character, giving on tson and rson.

Ref: http://msdn.microsoft.com/en-US/library/ms186323(v=SQL.90).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

To Check for Orphaned Users in a Database

This script is helpful to identify the orphaned users in a database, useful when we restore a database from a different location. 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

Deleting backups on OS -- problem? - Hi guys, Here we have a 2005 XE instance (XE has no maintenance plans). There is a script that backs up...

Very large table - performance issues - We have a tall table that contains 2.6 billion rows Table structure: [img]http://i.imgur.com/Z02zDqk.jpg[/img] The application which uses this table has been running slow...

Merge Replicatoin Issues - Hi All, I am facing a couple of issues both after Configuring and before Configuring the Merge Replication. 1) After Configuration : I...

sysJobActivity showing jobs from years ago that never ended - I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to...

SQL Server 2005 : Backups

INDEX REBULID - HI, ANY ONE GIVE ME SCRIPT FOR INDEX REBULID AND UPDATE STATISTICS IN SQL 2008 R2

SQL Server 2005 : CLR Integration and Programming.

Using an ASP - I am a total newbie and have a question to ask. I am the only DBA on a team managing...

SQL Server 2005 : SQL Server 2005 General Discussion

BCP Utility to output contents into CSV - I have a stored procedure that gets a SQL query, file path (to output) and file name as parameter which...

Retrive parent child relation - Hi all below is my table and I want query for parent child relation. CREATE TABLE [dbo].[PRDST]( [PARENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS...

SQL Server 2005 : SS2K5 Replication

ALTER TABLE breaks replication - I have a table-article being replicated as part of a publication that replicates DDL commands. Historically, executing ALTER statements on...

SQL Server 2005 : SQL Server 2005 Integration Services

Execute Process Task to execute a .jar - I'm trying to run an Execute Process Task that initiates a java program. The error I receive when I run...

Not Possible to Import Data from Excel sheet - I was importing a table data from excel sheet using SSIE wizard. After mapping the table its giving error Operation stopped... -...

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...

SQL Server 2005 : T-SQL (SS2K5)

Trying to fire this off, but only if there are results... - Hi, I'm using this bit of code to email results, but only if there are results/records: [code="sql"]IF select count(*) from (Select deddedcode...

SQL Server 7,2000 : Administration

Decyphering waitresource - I have a spid that shows a wait resource in the form n:n:nnnnnnn . The first number is dbid and the...

SQL Server 2008 : SQL Server 2008 - General

Initial data dumps - We have a fairly large production database for few hundred customers. When we add a new customer, we need to...

SQL Server Corporate Standards - Automated Compliance Checking - Does anyone know of a flexible automated SQL Syntax checker? I want to be able to analyse scripts before they are...

Why is my subtree cost so high?! - I really need help with optimizing this code. The subtree cost is really high, but it may just be a...

Report not working on Report Server - I'm not sure if this is the right place to post this one, but maybe someone can help. I have a...

Convert data to HL7 message - Hello everyone. I have be trying to figure out how to convert patient records into HL7 format for about a...

Tempdb file sizing - Need some recommendation on sizing of tempdb files for the following specs: • Two SAN drives - 400gb each have been assigned...

For your amusement - [code] USE [collect2000] GO /****** Object: UserDefinedFunction [dbo].[tvf_ScrubData] Script Date: 05/21/2013 12:54:28 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_ScrubData]') AND type in...

Dynamic Pivot Using sp_executeSQL with Parameters - Hello, I am having an issue with a dynamic pivot query(SQL 2008). I have read that using sp_executesql with parameters...

syntax difference between create index and create clustered index - Is there any difference if I say Create index and create nonclustred index? or both means same? ex: [i]CREATE INDEX[/i] IX_ProductVendor_VendorID ON...

How To DISTINCT COUNT with Windows Functions (i.e. OVER and PARTITION BY) - SQL 2012 Reference = http://msdn.microsoft.com/en-us/library/ms175997(v=sql.110).aspx I'm trying to get a unique count of rows in a child table in a multi-table join...

indexed views, aggregations and poor cardinality estimations - I've got a large sales table (66m rows) that I need to query in an aggregated way to return a...

BIDS 2008 - unable to rename dtsx package - This strange thing is happening: I am creating a new package, always has to happen by starting from an existing...

ORM Queries Prone to Excessive Blocking - Our application is using nHibernate as our ORM, and during some small load tests we've run I am noticing that...

Shrink helping performance - I have a situation that i thought was impossible and cant really explain to the users. I was asked to...

Update Statement on specific criteria - I have an existing table where if I use a Select statement against one of the columns I could get...

r squared trendline ssrs - how to calculate values from dataset - I'm sure there is a way to do this. My user wants a scatter plot chart with an R2 trendline. Seems...

SQL Server Native Cleint and PB - Dear Gurus, How to the apply the ‘Auto Translate’ property of the SQL Native client (OLE DB) connection string in Power...

recording changes to a table without creating multiple history tables - I'm curious if there is a built in way or a best practice that allows for recording changes in tables...

dropdown list to call different reports based on selection - Hi is it possible to call different reports from a dropdown list?? example: dropdown list contains: test 1 test 2 so if I select test...

Memory utilization is reaching more than 90% in sql server 2008 - Dear All, Last night onward my memory utilization is going beyond 90%. We have sql server 2008 r2 with two node cluster. we...

Running updation in SQL - Dear Team , Kindly advise , How to create query in sqlserver for running updations, Qustions : 1 . 1st row Total comes to...

How to restore database from network place... - Hi. My requirement is Restoring database from network place. when i am trying the restore the drive is not display...

Kindly advise , How to create query in sqlserver for below issue, - Dear Team , Kindly advise , How to create query in sqlserver for below issue, I Have Table with below details Id Name...

Query implementation help. - I have about 50 fields (client attributes) that needs to be shown on the report. Client may or may not...

TimeStamp without milliseconds!!! - Hey, Am trying to add an timestamp column by using derived column but i dont want to get the milliseconds. i...

How to write a select query that shows upcoming birthdays - This will be an easy challenge. Select date_of_birth from person --This gives values in the YYYYMMDD format like 19700101 I want a...

select records from a table where the records belong to fist clienname when ordered in ascending order of clientname - Using SQLServer Management Studio 2005 I need to develop a query/storedproc using which I can retrieve all records pertaining to first...

My CPUs are all at 100%. How can I find out what is running *right now*? - There are a number of nice queries (below) that show you what is using CPU time on your server. But...

Making data in a column unique (that's not a PK) - This may be an elementary question but if I don't ask, I won't learn. What's the best way to ensure that...

SQL Server Security Issue - Hello all, There are accounts that were created well before my time in which the users (developers) know the passwords. These...

Attaching a database in SSMS without sysadmin role gives error - I am accessing my sql server 2008 r2 express server using SSMS. I have a user which has got db_owner...

Settings of Windows Server for SQL - Hello, Do you have any tips for settings of WS? Roles, memory, etc.. ? it can be different betwen performance? any...

SSIS format Excel worksheet with script task - I'm exporting to an Excel file and then formatting the spreadsheet. Using SQL2008R2. When I run the package from the...

Antivirus on a SQL Server VM - hi guys, my vm admin wants to run AV at the host and not at the guest level. what do...

Running DTS packages on 2008 R2 - Hope someone can help me on this one We have a 2008 R2 cluster which failed over recently, a number of...

SQL Server Link to Oracle Inconsistent metadata problem - Hi I have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can...

SQL service account is sysamdin or not? - A question I have not got a clear answer: When installing SQL server 2008 or 2008 r2, we choose to use...

Count Words in Text Field and/or Column (frequency of use) - I searched the archives for this and could not find a solution. I have a comment table and would like to...

SQL Server 2008 : T-SQL (SS2K8)

SELECT INTO ignoring IDENTITY column - Hello, I want to know if anyone knows of an easy way to do a SELECT INTO from a table with...

List out rows from a duplicate record set that has one or more odd values - I have a table with data that looks as below... [img]http://www.sqlservercentral.com/Forums/Attachment13718.aspx[/img] Each CaseID has multiple records in the table. Each of these...

Very difficult query, not sure how to handle without a cursor. - I have a scenario here I have been wrestling over off and on for weeks. We have to tables, one lists...

Datetime function help - Hi, I m trying to get a datetime function for the start day of current week (Sunday). I m using...

Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY. - Hi I’ve a CustomerActivity table that records all the activities of the consumers. I've create a simplyfied version of the table with...

creating table for fiscal year - hi all i want to create table for fiscal year in sql server 2012. the fields are : 1-ID 2-StartDate 3-EndDate but i have some problems...

SQL Server 2008 : SQL Server Newbies

SQL 2008 R2 performance monitoring and troubleshooting - Hi folks, I'm a Network Admin that has been assigned the task of evaluating our SQL server for performance issues...and when...

Problem with truncate not releasing exclusive access - I am truncating several tables using a SQL script. The script runs successfully. The next thing I do is a...

SIGN - Why does this return more then one row? The book I'm reading doesn't say why and I'm used to select...

mdf file growing - Hi, I am new in SQL Server. I have a database whose size is almost 25 GB, there log size 18...

SQL Server 2008 : Security (SS2K8)

AD group has dbowner access to DB. How can I stop users from accessing from outside the application. - What am I missing. An application uses the users AD ID to execute against the database (read, write, create temp...

Best practices for SQL Server Agent service account - Hello, Can someone suggest what is the best security for SQL Server Agent service account? Or in other words which account...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 Merge Replication issue - Hi, i am getting the below mentioned error for one of my database which is in replication, please help what needs...

create a login on snapshot database on mirroring server - hi all, i would like to create a login on snapshot database on mirroring server. i have tried to create a...

SQL Server 2008 : SQL Server 2008 Administration

Added node to clustered sql prior to installing SQL on it, what to do :) - Hi - I am suppose to setup an active/active clustered server (none of servers had any sql on it, these are...

Memory and tempDB on SQL server - Hello guys, notice : my boss finally went out and I can focus to optimalizing. :-D So its his work. I...

Backup Job Incomplete - Hi, I have problem My backup script runs on every sunday for full backup and should have to take 41 database...

cannot connect to the server - We have a SQL server 2008 Cluster instance. Our SSIS package failed early morning. And the web site uses it...

Moving everything on an SQL instance to new hardware - We're setting up to move everything from a 32 bit machine to a 64 bit machine. I'm still researching the...

SQL Agent jobs do not run as scheduled after change the time zone to a previous time - (1)I create a job and schedule it to run every 20 seconds. (2)I change the time zone of the sql server...

client was unable to reuse a session? - Hi. Error messages, The client was unable to reuse a session with SPID 246, which had been reset for connection pooling....

restoring partial partitioned database - Hi all, I'm rather inexperienced in the Data Warehouse side of SQL, but we have rather small sized data warehouse db...

Career : Certification

70-462: Can the Hyper-V environment be downloaded somewhere? - Hi all, When I just opened the 70-462 TK I saw a six server Hyper-V environment is neccesary to complete the...

70-461 - SQL Server 2012 Querying a Datbase - Just started to look at the certs for 2012 and it looks like this needs to be taken before you...

SQLServerCentral.com : Anything that is NOT about SQL!

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 2005 Development

Subscription Emailing report when it shouldnt....or when its not desired - I have a datadriven subscription that uses a stored procedure. The code is below: declare @ReturnValue int exec @ReturnValue = s003pSRC.sp_Par_Rate_Compare_Report_Subscription_Condition select @ReturnValue ReturnValue I...

Data Warehousing : Integration Services

Move One or Two files from One Folder to an Archive Folder - Hi All, I have a package that does the following: 1. Truncates the staging table (in db) 2. Data Flow Task that connects...

call sproc in oracle - We would like to do this in SSIS package: 1. pull a list of employeeID, with their address from a SQL...

File System Task - Copying files from Folders to multiple folder based on the first 4 digits of file name - Here is the 2nd task I want to do I have 10 folders from 10 services. Each of these services can...

Recording File Name etc into a SQL Table using a loop for each directory required - Hi, Im starting a new project today. What I have is an area with 10 folders where services drop their files...

Expose SSIS task script to application - Hi Is there a way where I can expose the vb.net/c# code used in Script task to external application? I mean...

ssis - hi all, my excel source file contain some data name as coordinates i want to load it into sql server table...

SSIS Package to remove leading 0's - Hey Everyone, :-) I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot...

Saving Attachments in SSIS - Can anyone out there recommend an SSIS task that can go into extract a file from an email attachment? We...

Data Warehousing : Analysis Services

Can anyone let me know why not so many companies choose SSAS for data analysis and base layer for dashboard? - I kept tracking the job market and noticed not a lot of company developed their DW with the use of...

Data Warehousing : Data Transformation Services (DTS)

How much faster is bulk insert? - I've read some articles stating that bulk insert is quicker than using transforms. Has anyone got some benchmark figures or...