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 Challenges of Being Safe

This editorial was originally published on Mar 19, 2009. It is being re-run today as Steve is away at DevConnections.

One thing we recommend is to make sure that production data is always kept safe, which means that you keep control of where it lives and how it's used. I saw a post recently where someone needed to send data to a business partner and wanted to obfuscate it.  I think that is a great thing to do and was glad to see someone asking for advice.  Sending data to business partners is required, but you want to be sure that you are not necessarily giving away too much information, especially identity, privacy, or financial information that you shouldn't.

However what about your test and development environments? I've seen people obfuscate data here, but not always. In fact, not usually. This is despite the fact that you might turn over developers often, expose that data to testers, or other people that might not normally have access, and the fact that these environments almost never have auditing enabled.

We need to mimic production environments and data, but there are quite a few challenges with doing this in a safe and secure manner. Just having scripts to obfuscate data is a challenge in and of itself. I'd love to see a tool built that would do this automatically, though I wonder how many companies would actually buy it. I'm not sure there's a great market for tools here until insurance companies start require it for your "data loss insurance."

But building those scripts is both hard and time consuming. How do you decide what to obfuscate? What values do you use? How do handle PK/FKs to ensure that things match up correctly if you've used personal information, like a SSN, as a key field?

Beyond that, there's much more. What about ensuring that data matches up correctly? Can you really determine if there is an issue with some calculation or relationship if you have random data. After all people many times will have favorite accounts that they know well and understand what the data should look like. A developer may expect certain order details or address information, and use that as a benchmark when developing new code. If the data is random every time his environment is refreshed, does that slow his productivity? How do you test things like URLs and emails if data is randomized?

And what about when you alter your schemas? How much work is there to update your scripts?

And what about mistakes? How often has someone pointed development code at a production server? What happens if they accidently run an obfuscation script against production? If you've scrambled data around, would you catch it quickly enough to restore before too many transactions were sent through?

Using some type of data obfuscation or randomization is a great way to help ensure that your production data is kept safe, but it definitely makes for a much more complex environment, and likely, more headaches for DBAs and developers.

Steve Jones


The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). 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.

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.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
Free eBook

SQL Server Concurrency: Locking, Blocking and Row Versioning

Read Kalen Delaney's eBook to understand SQL Server concurrency, and use SQL Monitor to pinpoint excessive blocking and deadlocking.

Download the free PDF.

SQL Compare

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.

sqldbabundle

SQL DBA Bundle

Save 45% on our top SQL Server database administration tools. Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly.

Download a free trial now.

Featured Contents

 

Replication: Distribution Agent Monitoring

Brian Wilson from SQLServerCentral.com

Learn about the distribution agent and how to identify replication bottlenecks. More »


 

Lab Ops 2–The Lee-Robinson Script

Additional Articles from Microsoft MSDN

Marcus Robinson adapted PowerShell scripts by Thomas Lee to build a set of VMs to run a course in a reliable and repeatable way. With Marcus’s permission, Andrew Fryer has put that Setup Script on SkyDrive, and provided notes on the script. More »


 

From the SQLServerCentral Blogs - MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

Sherry Li's BI Corner from SQLServerCentral Blogs

In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and... More »

Question of the Day

Today's Question (by Dan Hess):

In SQL Server 2012, how does Microsoft recommend to add a database user to a database role?

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 2 points in this category: ALTER ROLE.

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

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Samith C):

create table #temp_test
(id int)

insert into #temp_test
 values(1)
insert into #temp_test
 values(NULL)
insert into #temp_test
 values(2)
insert into #temp_test
 values(3)
insert into #temp_test
 values(2)

select count(distinct id) 
 from #temp_test
What will be the result ?

Answer: 3

Explanation:

Distinct count() never considers NULL as a distinct value since NULL can be the same of other value. According to the documentation, COUNT returns unique, non-null values.

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


» Discuss this question and answer on the forums

Featured Script

Find Top 5 expensive Queries from a Write IO perspective

Bodhisatya Mookherjee from SQLServerCentral.com

Recently I wrote this query that helps us find the most expensive queries from a write IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

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 2014 : Administration - SQL Server 2014

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2012 : SQL 2012 - General

Support of JET OLEDB - All, We are planning to upgrade from SQL Server 2005 to SQL Server 2012. 1. We have many SSIS packages. What is...

BIDS vs SSDT - Hi, Simple question. We already develop with BIDS for SQL server 2008. We recently upgrade th PC to Visual Studio 2012 for...

Launch SSMS from command prompt - Hi, How do we launch 2012 SSMS from command prompt? I was using ssms.exe to launch 2008 SSMS also googled and...

SQL Wait type xe_live_target_tvf - Hi All, We're having some performance problems with our server, and so checked the wait stats. When I run the following...

FULLTEXT search - Hi, I came across a FULLTEXT catalog that documents tables in a database. The catalog has 5 descriptions tables (table, columns, triggers,...

ADODB Recordset Updating - The following MSAccess VBA code worked well with SQL Server 2005, but produces an error with SQLServer 2012. The error is...

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. - Where can i place my ORDER BY statement to sort ALL the data from the table, not just the 'from'...

"could not open error log file" on incorrect SQL cluster node - Hi. We have 2-node SQL 2012 cluster running as VMs in Hyper-V. The cluster hosts 4 SQL instances, running in...

Sybase ASE 12 to SQL Server 2012 via SSMA - were using Sybase ASE 12.5 32 bit to SQL server 2012 64 bit via SSMA. I'm going to transfer only...

importing a populating csv file - I have a CSV file which is getting information about every 30 seconds from a third party program and I...

Need A Tool to Reverse Engineer an ERD - Our friends have done us a solid with Visio. Since SQL 2005 and Visio 2007 I have been reverse engineering...

Unable to shrink Database - I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB....

Traces question - Hi, I'm running a server trace to determine where the procedures take more time to execute. I'm tracking events 10, 12, 43...

could not understand what the error is - Hi guys, I got an error message like this.... i could not figure out what made it to occur, do any one...

Issue with AlwaysOn High Availability - Friends, We are facing an issue with our DBs. We have configured AHA for couple of SQL Server Instances. The first...

Printed Books Vs E-Books - ;-)

Missing OraOLEDB provider - I need to be able to set up Linked Servers on our SQL Server 2012 Server. When I try to...


SQL Server 2012 : SQL Server 2012 - T-SQL

Help needed with BCP command - Hello I'm attempting to use the BCP command for the first time, I'm effectively trying to get the output of a...

ORDER BY, Bad form? - So there's a trick I've used a few times with the ORDER BY clause in SQL, and I was just...

Searching for n-categories - Hi there I have a challenge where I need some help. May someone can help me? I have a table where a...

Trigger with RAISERROR - Hi, Why doesn't a RAISERROR inside an AFTER trigger "break" the transaction? Is there any flag or SET option to do...

FULLTEXT problem - Hi, I've a table with a fulltext index and when I search for an exact phrase I get a result but...


SQL Server 2008 : SQL Server 2008 - General

Archive Database in SQL 2008r2 - Hello All, How to archive the entire database in SQL Server 2008r2 Thanks, Santosh

Flat File not moving all data - Hi, Im using flat file (.csv) to sql 2008, which has only 40 rows but 39 rows get transfered 1 row...

SSIS Transfer SQL Server Objects Task - Permissions Issue - I am attempting to create an SSIS package that will copy data from selected tables on one server to another....

SQL Server 2008 R2 Hyper-v 2012 live migration - Hi all, I would like to know if anybody has tried a live SQL Server 2008 R2 VM live migration on...

sqlcmd using ctrl-A - I need to be able to export the data from a table to a text file using sqlcmd. I need...

t-sql is bigger - Guys, I currently have a database of 80 giga bytes. did a shrink log file to 1 mega byte. after...

Performance When dropping multiple tables - Hi, The software we use often creates a lot of temporary tables that need to be deleted from time to time. Depending...

Count Of Level in parent table - hello all.i have a parent table: subjectid subjectname parentid organizationid 4058 x -1 576 4059 x1 4058 576 4060 x2 4058 576 4061 x3 4058...

Change value for allow_anonymous & immediate_sync for Replication - Hi, is there any good reason, why this both values should be set to true. I had to change this values...

2008 RTM VS R2 - Dear All Where can I find the differnce between 2 versions of sql 2008 Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) VS Microsoft...

approximate timespans for db backups? - Hello, I was wondering if you generally see a fairly consistent timespan for database backups? For example, I just backed...

connection options to sql server 2008 - In a new position that I have, I am told that I will be connecting to reporting services for a...

Installing a 1t db and best practices to keep 3 data bases in sync - Hi, All This is my project which i am going to work on. -- have to install and configure test/dev/prod databases using...

How to run multiple stored procedures in parallel? - I have a stored procedure which runs in database DB1 This stored procedure will have other stored procedures inside it which...

Need Help on Fastest Search Logic - Hi, I have two tables named "Table1" and "Table2". Table1 Details: id bigint, product_name nvarchar(1000),quantity int records count on Table1 : 25000( may increase in...

SSIS source file with time in the name - Hi everyone. I have been tasked with building some SSIS packages to load data from a csv into the database each...

Error message when changing location of share drive in SSIS package - Hi all, At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS...

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 Server 2008 : T-SQL (SS2K8)

Question Regarding Store Procedure - Hi Guys, I have quick question, I am using SP, in the SP I am using few Insert and Update statements. I...

wish to add rows having NULL values as placeholders for "missing" dates - I have a table that contains stuff happening. There's a datetime stamp column to show on what date the stuff...

Linking a SQL Server 2005 (Windows Authentication) to a SQL Server 2008 (Mixed Mode) - I am trying to link a production database (SQL Server 2000) that is on Windows authentication to a test database...

trying to write a case statement for datetime stamp - some trouble - Hi. I'm working on creating a histogram using time, as the set of 6 buckets that data can fall into....

T-SQL and BCP to qureyout to a file on database premissions - Ok I at an end, I'm close but no grasping at straws. In this set of statement I collect instance...

Ignore Error in T-SQL - Hi, How do we ignore an error in T-SQL? For e.g.:- The following code throw an error once the @lCounter reach...


SQL Server 2008 : SQL Server Newbies

Combining fields of matching records - I have a table that contains several fields, and some of the records are identical except for one of those...

Is this right if i will do my project this way?.. - Hi Everyone..I hope everyone is having a nice day:-) Is this right if i will do my project this way? Please see...

SQL Agent Job error - Hi, I am experiencing the following error below. I have an update uncf processes job that has 35 steps. The job...

Can You make this code Shorter??.. - [code="sql"] DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max) SET @SqlQuery = '' SET @SqlQueryStatus = '' SET @SqlQueryFirstname = '' SET...

Auto Increment Alphabet - Hi All, I need to generate a query that'll prefix my form series like the below AA AB AC ... AZ BA BB BC ... BZ CA CB CC ... CZ .... .... ZA ZB ... ZZ AAA ........... Basically, I need to increment...


SQL Server 2008 : SQL Server 2008 High Availability

want to ship log ship status of server as a mail to client - Hi my client require me to ship status of log shipserver whether they are in insync or not. there is...

Multiple Mirrored databases, Dev, Test, Prod off of one Witness? - It was suggested to me that we could just have 1 witness for all of our {application name} databases for...

Experiment in High Availability - Hi All, Would like to get some hands-on with the high availability options (Log-shipping, Replication, Database-Mirroring and Server Clustering). Can I do...

SQL 2008 R2 Mirroring + Compression - Hi - I have two physically large servers. 64 Cores X 1 TB RAM. SQL Services utilizes a 1 GB connection on...


SQL Server 2008 : SQL Server 2008 Administration

Shrinking Temp DB - I am trying to shrink the first temp db mdf, but it is not shrinking... 95% free... just added some...

SQL Server 2008 R2 Cluster in Windows Server 2008 R2 - Maintenance Activity - Start/Stop Services - How to Start/Stop SQL Server Services in Two Node Active/Passive Cluster environment?

SSAS Change Management - Not sure if this is the correct place to post this but I am looking to see if there is...

Create Full Backup with Multiple FileGroups - Hello Everyone I am in the process of writing a Full Backup Database script, but this database has multiple filegroups. The...

Memory assigned to the OS - Total physical RAM on the system is 288GB. Min and Max memory assigned to SQL Server is 0 and 280GB. Should I...

Log not available error 9001 - Came in to work this morning to face a bunch of alerts for severity 21 errors. "DESCRIPTION: The log for database 'SpotlightManagementFramework'...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

hep needed in dbcc checkdb - Hi last night at one of client side we had one of our databases RECOVERY PENDING state.what it is .I...

Invalid entries in sys.indexes and sys.objects - the dbcc checkcatalog, returns follow: [quote] Msg 3852, Level 16, State 1, Line 1 Row (object_id=717531377) in sys.objects (type=U ) does not have a...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Creating Index on all FK (more than 15 per table) - Hi, I have 2-3 huge tables (more than 20M rows, and more than 50 columns each). About 20 of these columns are...


Cloud Computing : Amazon AWS and other cloud vendors

How to Import data from S3 directly into my sqlserver database on amazon EC2 - How can i take text files, or csv files from s3 and directly upload/insert them into a table in my...


SQL Server 2005 : Administering

Set File or FileGroup to READ_ONLY on a busy system. - According to Books Online and some testing, I think the answer is probably "NO" but I thought I'd ask just...

Urgent: How to shrink this huge DB? - I inherited a bad designed DB with: 25GB mdf + 99GB ldf. As I know, most data are redundant and can be...

Database Not responding from 8:00 PM to 10:00 Pm - Dear All, I have an Sql server 2005 server which is hosting multiple Databases. Recently, users are complaining that the application...

Serious memory pressure - Hi, I would like to get some opinions on possible paths to take to rectify a physical Windows 2003 Standard server...

Named Instance port number - Hi, I have a default instance of SQL Server 2005 and 3 named instances. I know that by default a named...

How to get file date using xp_cmdshell? - Hi, I have a file in my C: directory, using SQL, I am trying to get the date of that file. ...


SQL Server 2005 : Business Intelligence

SSIS objects (MS SQL 2005) - Hi Everyone, Can someone provide me a site's link discussing the different SSIS objects. (example : Execute SQL task,Excel destination, etc. everything seen...

Reading Server details or Enviornment variables in SSRS report - Hi, Is there any way to read SSRS report server name or enivornment variables when report is run with limited access....


SQL Server 2005 : Development

Invalid object name 'DDLTriggerTest..EventTableData'. - hi, when u tried to modify the procedure then it gives me error like this... [b]Procedure tr_ProcedureEvents, Line 6 Invalid object name...


SQL Server 2005 : SQL Server 2005 General Discussion

OLE DB provider "MSDAORA.Oracle" has not been registered SQL Server Error: 7403 SQL 2008 on Windows Server 2008 - Can someone give me some pointers for a Oracle Linked Server? On my Windows 7 ODBC, I can successfully connect...


SQL Server 2005 : SQL Server Express

unable to delete, File is open - Hi am delete database but it says: cannot delete database because the file is open in sql server, I can see...


SQL Server 2005 : SQL Server 2005 Performance Tuning

performance issue - Hi When I am runnng the below query it is taking long time. Before it was fine but when i added...


Reporting Services : Reporting Services

Can you have a report mail based on when a field in a table is updated? - I send out a report with a subscription. But I set it up to mail out at 8:00 am daily...

Maximum request length exceeded - I'm having the following in my relatorio he is already quite long. "There was an exception running the extensions specified in...

Selecting dynamic date range - Hi, I have an sql query in Excel Existing connections through which i am able to pull the data. I...

Question About Creating Grouped Reports Outside of the Wizard - So this has been bugging me for quite some time now. I know it's faster to create report using the...


Reporting Services : Reporting Services 2005 Development

Page breaks in columns of one table in ssrs page - I am creating a SSRS 2008 Report in my report i have 200 columns, it is difficult for visualizing purpose,...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 what is first displayed when user clicks to run report - In an SSRS 2008 r2 report that I am suppose to work with, there are a large volume of rdls...

ssrs 2008 r2 add connect data to a gauge - In an SSRS 2008 r2 report that I will be creating, I want to add charts and gauges to the...

graphs in ssrs 2008 - I have a question about accessing the graphical features in SSRS 2008. Can you tell me how to access the...


Data Warehousing : Integration Services

SSIS Export Package Failure - I get the error below when running an SSIS Export Package through a SQL Agent scheduled job. When I do...

ExcelFilePath disappears after setting the ConnectionString dynamic (Expressions) - Hi, I have two similar SSIS packages and the other one works fine but the other does not. I have two template...

[Add Date from variable [2]] Error: An error occurred while attempting to perform a type cast. Getting error.. - Hello , I am getting below error when I am adding row into destination table using derived column. [Add PaymentDate_FirstOfMonth from variable...

Question about dynamic OLE DB connections - Ok I have the following issue & scenario I want a ssis package that connects to a single configuration database server,on this...

Using SSIS 2012 to read data from Oracle 11g R2 - Hi, I need to read and import into a SQL Server 2012 db data from an Oracle data source (Oracle 11g...


Data Warehousing : Analysis Services

Analysis Services Cube Automation to browse from Excel - Hi all, I have a system, like I have database, I make cube. When I had to make some changes in...

Find Date from SQL Table in Analysis Services - Hi, How can I find date from SQL Table in Analysis Services. Like I have Thousand of date in a table...


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


SQL Server 7,2000 : Administration

Log Shipping on SQL Server 2000 Standard Edition - Hi All, Thanks for looking into my question. Do you know if it is possible to setup log shipping in SQL...

DBCC LOGINFO has a status 2 that I can''t seem to get rid of - I have done as many tricks as I know (forcing a shrink with dummy records, backing up tx log regularly,...


SQL Server 7,2000 : T-SQL

update from multiple tables - Hi Folks, i am using 4 tables company, users, ARSAP, ARCUS to update a column in company table. the tsql i am...

SQL Help in grouping - Hello, SQL Server Version 2000 create table students ( course_id varchar(5), course_name varchar(10), roll_no varchar(3), student_name varchar(40), geog_marks varchar(2), math_marks varchar(2) ) insert into students values ('1','course1','1','abc','40','') insert into students values ('1','course1','2','aaa','40','') insert...

I need help Please :( - I have a table name Employee Logs table consist of EmpID, Empname,Logdatetime. I can generate the first in and last out by...

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