Most of us that work with data are concerned about losing any of the bits or bytes we are responsible for. Many of us practice restores when we can, we complain about the low disaster recovery budgets we're allowed, and we regularly check our systems for corruption. Even those of us that aren't extremely diligent in our daily data protection are often worried about losing our jobs if a disaster causes data loss. Many of us don't even trust our users, preferring to implement logical deletes in applications rather than physically running a DELETE statement.
However there are times that we do want to remove data from our systems. We may find data quality is poor and want to erase the results of an ETL load. We may find ourselves bound by regulations that require the removal of data from our systems. We may upgrade old software and end up with copies of obsolete databases whose contents have been copied and reformatted by a new version of an application.
This article talks about the data-pocolypse, and in somewhat of a jesting way, but it has a few good points that we may want to consider when we do need to remove data permanently. We should understand that deletes are not always deletes, and if a permanent solution is needed, we should use a utility to wipe the drive or physically destroy the hardware. However there are other places we should worry about old copies of data. Backups should be deleted from remote storage that might not have cleanup jobs running anymore if the servers are decommissioned. We should be wary about taking databases offline, or detaching them without physically removing the files. Development machines, laptops, etc. should have data removed if we are sure we don't need it again.
We should be especially careful about security access to servers that we are not using. Users might easily have links or pointers to old servers, and mistakenly connect. Make sure you remove access when you decommission the instance. Be careful about keeping old file exports, such as reports or feeds that were generated in the past. It might not be practical to wipe backup tapes, but be sure you've changed documentation and surfaced the information to all administrators that copes of databases restored from tapes after xxx date are not useable in DR situations.
Of course the first thing you should do is make sure you have a good backup of the most current version of your data before you start deleting older copies. Delete this last, after you're absolutely sure that users are no longer going to request a restore. I wouldn't even ask users about this, however, because they may not be sure themselves. Instead I'd set a reminder a few months in the future to go back and delete this one, last, most current backup.
Ask database administrators how they implement disaster recovery in their big data environments and you'll get two typical responses: DR plans are not necessary and backups take up a lot of space. Despite this reasoning, a disaster recovery plan for your big data implementation may be essential for your company's future. More »
You know the problem: the “Estimated Number of Rows” in an operator in the Execution Plan is 42, but you... More »
Question of the Day
Today's Question (by Steven Neumersky):
A week of Analysis Services
You have deployed a cube successfully, but your users tell you "it does not make sense to roll up a certain attribute hierarchy in the currency dimension to "ALL". What property must you change in order to disable that attribute's "ALL" level while keeping the rest of its attribute hierarchy intact?
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: Hierarchies.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steven Neumersky):
A week of Analysis Services
SQL Server Analysis Services (SSAS) loves memory. From time to time you may need control the amount of memory consumed by SSAS especially if you have other SQL Server components on the same server.
What structures WITHIN SSAS have "shrinkable" memory structures? Shrinkable is defined by SSAS as "structures that gradually build up to increase performance". (Choose 3)
Non-shrinkable memory includes all structures required to keep the server running: active user sessions, working memory of running queries, server object metadata, and the msmdsvr.exe process itself. Memory can still, however, be paged out by the operating system in certain cases.
Frequently used calculated cells use the calculation cache, a shrinkable structure.
Dimensions accessed by users are also kept in cache to speed up attribute and natural hierarchies.
Not all subcubes are cached, but those that are cached become part of the data/storage engine cache--which is a shrinkable structure.
The use of dimension or cell security disables global caching.
Some subcubes are not cached at the data/storage engine cache level but rather at the session cache level--not a shrinkable memory structure.
In this example, the above script (Output_Schema.ps1) is placed in the folder c:\PowerShell the output text file is written to the folder D:\Reports\Schema Dumps and the database to use is Accounts_Database.
NOTE: The database parameter is optional, if omitted, then all databases on the server will documented.
SSIS Foreach file enumerator
I want to practice SSIS. I am working on For each enumerator.
I followed the link
I created four text files...
SQL Job reports strange results
- I've created the following script to report on the status on SQL logins:
@subject = 'ED - SQL_Logins_Report',
@profile_name = 'XXXX',
Linked server error
I had setup linked server and when i query the it gives me below error.
SELECT top * from [Servername].[WH_SYSTEM].[dbo].T_ENCOUNTER_PHYSICIAN
WHERE PhysicianID LIKE...
- hello all
I Have this function:
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
declare @idx int...
Write a conditional query
I require to write a single query that if I pass a customer id, it will retrieve that customer's information....
Custom Error Message
CREATE TABLE [dbo].[TestTable]
ID INT NOT NULL PRIMARY KEY
RAISERROR('Everything is OK!', 5, 1)
RAISERROR('There was a problem!', 16,...
I have Change tracking set on two databases, required for synchronization.
The retention period is set to two days with auto-cleanup...
problem with openquery
- hello all.
I have this sp:
ALTER PROCEDURE [dbo].[IcanSp_HumanResourceDailyMissionRegister] @EC INT
CLR assembly DLL file location? Corrupted
CLR file location.
Is it C:\windows\Framework\version?
Frequently facing problems SSIS - backup package dll files are corrupted and maintenance plan not taken automatically...
- Hello Experts,
we have a situation he a table grown to 180+ Gigs with several millions of records. this affects Re...
SSAS Role Dynamic Security
- Hi all,
I have been reading this on Technet (Roles (SSAS Tabular):
I would love to implement a cube some day soon,...
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.