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

The Abstract DBA

Today we have a guest editorial from Andy Warren as Steve is out on holiday.

Back when I became a DBA I was involved in everything. I worked on the hardware evaluations and purchase decisions, everything from memory and storage to network cards.  I decided on the RAID type, stripe size, and more. I was a backup to the server team and was a domain admin so I could both provide coverage and just get things done when needed. I did the SQL Server installs, upgrades, and patches, and I consulted with the developers on everything from design to performance to security. I went to class to learn clustering and SAN administration, more to understand how they worked than an expectation that I’d be doing in on any kind of regular basis.

Today I work with a team where I’m a DBA but I don’t do the installs or the service packs. I don’t have anything to do with storage besides asking for more when needed. I don’t build or patch the servers, I don’t build clusters. It’s all handled by the server/operations team, a team that doesn’t contain a single DBA. It’s more and more common, and I think a good thing if you have the staff to do it. I don’t feel like any less of a DBA because someone else does the service packs!

I was struck by a recent conversation with one of those team members who remarked that they didn’t like that the DBA team had direct access to the server because we could make a change that would bring down the server. I had to laugh. I get the urge/need to limit access, and I’ll get by with whatever access I am handed, but in my biased view bringing down the server pales in comparison to the kind of issues that a DBA can cause with a single mistake.

If you think about it, that’s a lot like what we get in cloud solutions today. We’re abstracted from the hardware and the storage and just about everything else that isn’t a direct component of SQL Server. It feels like a loss of control, and it is of course, but it also means that we can just focus on the database. It doesn’t mean that mistakes or under provisioning won’t cause us pain, but as a service we can just identify it and then wait for whatever team owns it to fix it.

If I had a choice, which way would I have it? I’m reluctant to admit it, but I like the way things are today. What about you – do you like being ‘just’ a DBA, or do you yearn for (or still have) your fingers in a lot more pieces of the technology? 

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
SQL Compare

Need to compare and sync database schemas?

Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.

SQL Monitor

What does normal looks like on your servers?

New benchmarks in SQL Monitor instantly show you if a performance spike is a problem, or within normal SQL Server behavior. Try it live.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

More Tips for New (and old) DBAs

Craig Outcalt from SQLServerCentral.com

Following up on the popular article: Tips for New DBAs, author Craig Outcalt tackles three more issues including customer support and why you should learn T-SQL. More »


 

Workaround for lack of support for constraints on SQL Server Memory-Optimized Tables

Additional Articles from MSSQLTips.com

SQL Server 2014 offers In-Memory OLTP, but you may be hesitant to use this because of the lack of support for constraints on Memory-Optimized Tables. In this tip we will see how we can solve these issues. More »


 

From the SQLServerCentral Blogs - How to Fail a Phone Screen

Jen McCown from SQLServerCentral Blogs

It’s funny to me how easy it is to fail a phone screen – both as the interviewer, and as the... More »

Question of the Day

Today's Question (by Steve Jones):

If I wanted to avoid matching a character, which wildcard would I use inside the square brackets? 

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: T-SQL.

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

ADVERTISEMENT

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

It's Thanksgiving in the US. Assuming we have a table of colors associated with holidays, what should be returned by this query?

SELECT sales = color + ' ' + datename( dw, DATEADD( d, 1, GETDATE()))
 FROM SalesDays
 WHERE holiday = 'Thanksgiving'

Answer: Black Friday

Explanation:

The Friday after Thanksgiving is known as Black Friday in the US. This is the day that many retailers begin their Christmas shopping season with sales and deals.

Since today is Thanksgiving, Thursday, November 28, 2013, the GETDATE() should return this date. The dateadd should move the day to Novermber, 29, 2013 and the DATENAME should return "Friday". I am hoping you realize which day this is and therefore our table should contain a row with "Black" and "Thanksgiving" in it.

Happy Thanksgiving.

Black Friday - http://blackfriday.com/


» Discuss this question and answer on the forums

Featured Script

Create Mirror Alerts for All Mirrored Databases

Jordon Pilling from SQLServerCentral.com

Ever come across a 64bit SQL Server with more cores than you can shake a stick at, with a but load of Mirrored databases with no mirroring alerts what so ever?

This script has 6 main parts:

  1. Delete any old DBM alerts
  2. Create a Stored Proc to create alerts for a given database, both Threshold Based alerts (which it created Default THreashold values for you) and WMI based alerts for those awkward state changes.
  3. Create a Stored Proc to Email current Mirror Status for all databases
  4. Create a Job to Run the SP in point 3 (to be used by certain alerts)
  5. Loop through all mirrored databases and call the SP in point 2
  6. Drop the Temporary Components etc

This script will create alerts for each mirrored database, the alerts created will cover:

  • Commit Overhead
  • Oldest Unsent Transaction Threshold
  • Unrestored Log Threshold
  • Unsent Log Threshold
  • Automatic Failover
  • Manual Failover
  • Mirror Connection Lost (my favourite)
  • Mirroring Suspended
  • No Quorum
  • Principal Connection Lost (my favourite)
  • Principal Running Exposed
  • Synchronizing Mirror
  • Synchronizing Principal

To use the script, simply paste it into a new query window on your principle, do a find replace on "XXXXX@YYYY.COM" and replace with your DBA address, then execute. Repeat on the Mirror.

This is version 8 of the script, based on feedback in the discussion thead.

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 2012 : SQL 2012 - General

SQL SERVER 2012 MIGRATION FROM 2005, Help needed!! - Hi All, We are planning to migrate to SQL Server 2012 from 2005. We are in the initial process of building...

with VIEW_METADATA - SQL view have three view options 1) with schemabinding 2) with encryption 3) With view_metadata I have perfectly understood the first and second but...

Linked Server - Hi, I have recently setup a linked server from sql server 2012 to MySQL. I am able to see the...

AlwaysOnAG. The 'Readable Secondary' option impact on backup job? - Hello, quick question. For example my AG consists of two instances that are set up with synchronous data movement. Also the...

extract the id's only - Hi Expert, [code="sql"]CREATE TABLE tmp_id (Name nvarchar(100) null) INSERT INTO tmp_id VALUES ('King, Martin F. [1038134]') INSERT INTO tmp_id VALUES ('Abcdef-Hh, Cccc Dddd...

Logical reads calculator - Hello, I am looking for logical read calculator. Everytime when i have to calculate Logical reads of the query and its quite...

How can i check a reseed statement has been run on a table - i have a table with user_id (identity) as primary key everything is getting inserted normally up untill row 140 next day...

Using VMs in a cluster - Hello Could someone provide insight on using 2 VMs in a cluster fro SQL Server 2008 R2 (or higher)? Today, I have...

Best Tools for Monitoring a Large Estate - What is the best 3rd party tool for monmitoring a large SQL estate.

What is the impact in renaming an existing database which is participating in Always ON - Hello, I would like to know what is the impact in renaming a database that is currently been set up for...


SQL Server 2012 : SQL Server 2012 - T-SQL

SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL - Hi, This post is related to an error I have discovered through the execution of a SSIS package that has...


SQL Server 2008 : SQL Server 2008 - General

now.dateadd(d, -1) at 7AM - Hi all... I am trying to add a default date parameter in my report where the start date is yesterday...

SSIS: Execute SQL Task: Problems with paremeterse - I'm having a very elementary problem with parameters passed to an Execute SQL Task: I have two parameters defined at the...

formatting varchar as percentage - Hi all the clever ones I have a variable someVariable(varchar(30), null) in our database The vaues in this variable look like this: 8.000 7.000 6.000 How can...

Heavy Table Copy Slow - We have an table of huge size with 19 million rows in it. Table has just two columns one of...

Applying Latest service Pack in clustered environment in sql server 2008r2 - Hi, Please suggest the best way to applying latest service pack in sql server 2008r2 in clustered environment without effecting the...

help with interpretation of results - I have been checking my disk i/o and ran sp_blitz script which identified 'slow storage writes on drive S. The...

Running DBCC CHECKDB Against Mirrored DB - Hi Would anyone happen to know how I would run a full DBCC CHECKDB against the database that is acting as...

want to know Difference between isnull(sum(xx),0), sum(isnull(xx,0)) - Please find the following example. I want to know the difference between sum(ISNULL(i,0)), ISNULL(sum(i),0) Both will return the same value. Which one...

slow query, speed up with a non clustered index - Hi I have a query like that [code="sql"]select c.client_group, s.product_id, count(*) as q from Sales s inner join Client c on c.client_id= s.client_id group...

Hyphen vs. Underscore in LIKE clause? why they are the same ?? - Can't understand why it happening, am I right in my sample, or there is any special preset option I'm using...

Unique XP_Cmdshell issue. - Hi all, I am facing unique issue haven't experienced this kind of a issue so far. 1) I am running a...

regarding backups - Hi All, I have 500 tables in a database. i want to take the backup and restore the 3 tables along...

deleting some data from a column - Hi All i had some virus attach on my database and my online database suddenly got corrupted. with every column data...


SQL Server 2008 : T-SQL (SS2K8)

How to parse connection strings on a column in SQL Server 2008 - I want to extract [u]database names[/u] from a varchar column storing connection strings. A sample value is; '[b]server=SVR1;database=[u]DB1[/u];uid=user1;pwd=pass1;enlist=true;[/b]' Each record might have...

Enter user-defined variables at runtime - Hi all, I am writing a simple script to allow developers to create database snapshots for the purposes of code-testing. This is...

Duplicate row data on condition... - Hi, I have a table with following structure: [code="sql"] SalesRow SaleId INT, SaleRowId, TaxCode INT, TaxValue INT, Price INT, ExtraTaxCode INT, ExtraTaxValue INT [/code] On a...

Concatenate column values as single value SQL Server 2008 - have a table with a column: |-------------| | ColumnName | |------------- | | Value One | | Value Two | | Value Three | | Value Four | | Value Five | |-------------| I will declare...

understanding the use of cross and outer apply operator - I want to know when you have different types of joins available in which scenario cross or outer apply is...


SQL Server 2008 : SQL Server 2008 High Availability

Transactional Replication with growing commands and transactions - I have setup transactional replication at a customer site with SQL 2008 R1 SP1 Enterprise Edition residing on Windows 2008...


SQL Server 2008 : SQL Server 2008 Administration

What will happen if the hard drive with the data file goes to zero space? - SQL Server 2008 R2 The drive with our main database file is almost out of disk space. We are getting new...

SQL 2008 R2 Cold server DR licensing - no Software Assurance - Hi all, From what I read you don't need to license a cold DR server running SQL 2008 R2 Standard (provided...

Only send DBMail when query results are present - I have a job in SQL Server Agent that sends the results of a query to my email twice daily,...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Find out top memory consuming queries - Hi, Checking if anybody has a tsql script to display top 10 high MEMORY consuming queries? Thank you.

Recommended websites/book about PT - Hello! Do you have any good websites or books to read about best practice or advice for performance tuning?


SQL Server 2005 : Administering

copy job fails - Hi All, I have copy job Problem in Log Shipping, the copy job failed, but when i see the restore job...

RESTORE Problem in log shipping - Hi All, I have restore Problem in Log Shipping, the copy job succeed but when i see the restore job status...

Logshipping issue with restoring job - Hi All, Since couple of days i am experiencing on issue regarding log shipping restoring. backup and copy jobs are...

Serverlog: run reconfigure statement - while reading my sqlserverlog i found the following entry: Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement...


SQL Server 2005 : Backups

Backup failed - Hi all, I attempted to perform a backup and ended up with error message: [code="plain"] TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for...


SQL Server 2005 : Business Intelligence

MSBI career - Hello Everyone, I have 2.11 yrs of experience in SQL Server development and .Net too. Have more interest to Database side...

report viewer control on winform - Hello, using ssrs 2008. The ssrs report takes over 20 mins to populate. This works fine if it is run from the...

report timeout - The 2008 ssrs report is using a stored procedure which takes 15 mins to run. The report shows "timeout" error and...


SQL Server 2005 : SQL Server 2005 General Discussion

Sell old SQL 2005 discs and CAL - Is it possible to sell on our no longer used SQL Server 2005 (standard edition) and 25 Client license? or is...

Activity Monitor-ing - Can anyone explain why Microsoft consistently make the Activity Monitor in SQL Server (whichever version) so very, very bad? As...


SQL Server 2005 : SQL Server 2005 Integration Services

error while executing SSIS package from xp_cmdshell - Hello Friends, I am trying to execute SSIS package from xp_cmdshell. I have created SSIS package which exports data into excel file....

Issues while executing the xp_cmdshell through SSIS - Hi All - First let me tell you what my SSIS package does, [u][b]SSIS Package[/b][/u]: - As per the business requirement, i have...


SQL Server 2005 : SQL Server Newbies

Three tables SQL query - I have a table (Vehicles) which contains a list of vehicles. VehicleID PlateNo CurrentDriver I also have a table (History) which contains a the...

DBCC CHECKDB - How does DBCC CHECKDB works? What is the expected time to finish if the database size is about 800 GB?


Reporting Services : Reporting Services

How to create a dropdown list in which one choice will have the two possible values? - Dear sqlservercentral members, Hello and a pleasant day. I would like to ask how to create a dropdown list in which one...

Multiple Tablix with One to Many Relationships - Hello! I am hoping someone can help me with this - I am quite certain it can be done, I just...

Data Driven Subscriptions without SQL Enterprise - Introduction Do you have SQL Reports that need to go to different people but each person's report has different parameters? Don't...


Reporting Services : Reporting Services 2008 Development

SSRS 2008 r2 pass paramater values from report to report - In a SSRS 2008 r2 dashboard, the user can click on various links to jump to various ssrs reports that...

IsMarksNextToAxis - Hi I understand that the following code will prevent the axis labels from appearing in the middle of my table...

Dynamic Grouping with aggregate function - Hi, I want to do dynamic grouping at report level which includes aggregate function The logic i want to write is =(Floor((A/(Ceiling((Max(A)...

SSRS paramaters disabled in IE8 and IE10 - Hi all, I have three parameters in my SSRS report. As I deploy the report the paramaters are disabled in IE...

ssrs 2008 r2 export to excel on report server problem - In an SSRS 2008 R2 report, I have the following expression on the 'Hidden' property on the Tablixes where I do...

ssrs 2008 r2 c hange server name location - In a new SSRS 2008 r2 report, I am using the 'action' option to point to a url. When the...

ssrs 2008 r2 Globals!RenderFormat - In an existing SSRS 2008 R2 report, I want to show or hide detail columns based upon all the export...


Programming : XML

I can't nested some node in XML output - Hi I have this xml structure <?xml version='1.0' encoding='iso-8859-1'?> <Notificaciones> <Pedidos> <Pedido ID_PEDIDO="P500000999" COD_SAP="50000999" N_AMPL="001" NOM_NOMBRE="COMP.HOSTELEROS LAS BRUJAS,S.L." CANAL="G" NOM_PROVINCIA="Ciudad Real" FEC_ULT_ERROR="2012-08-09T04:22:10.960" DESC_ULT_ERROR="Error de...


Data Warehousing : Integration Services

How to read UniqueIdentifier output parameter from a stored procedure via OLE DB - I have a stored procedure that takes some input parameters and returns a GUID as an output parameter. I want...

How to copy data from multiple queries to same excel destination in ssis - Hi I have a requirement where i have data from multiple queries to be copied to a template in ssis. i...


Data Warehousing : Analysis Services

Time Dimension - Date Format M/d/yyyy instead of dd/MM/yyyy - Dear all I have created a Cube with a Time Dimension. I have deployed this Cube on my local machine and...

Power View --> SSAS Multi-Dim Model Supported? - I thought with the latest Power View in Excel 2013 (or SharePoint 2013) you can connect to a Multi-Dimensional model...


Database Design : Disaster Recovery

Using Asgira for SQL backups - My company is implementing Asigra as a backup solution for our server. Does anyone have any experience of using Asigra...


SQLServerCentral.com : Anything that is NOT about SQL!

KB Knowledge Store - I would be instrested to hear from fellow SQL DBA's BI etc.. Where do you store your notes ? By notes I...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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


Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...


Career : Resumes and Job Hunters

Looking for a Sr. SQL DBA out of SF - Contact: Amber.Richard@staffmark.com Phone: (925) 969-4433 LinkedIn: [url=http://www.linkedin.com/in/amberrichard/]www.linkedin.com/in/amberrichard/[/url] Searching for a Senior level SQL DBA. This is a F/T or contract-to-hire position. SQL...

This email has been sent to {user_email}. 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