On more than one occasion, I have run into a case where a large database with a large transaction log was being mirrored. Then a hiccup comes along – the transaction log on the primary server grows to a point where the transaction log on the secondary server fills the drive. Now you have a problem and you need to reclaim some space or you need to find more disk.
Well, just because the database is mirrored and the drive is out of space, doesn’t mean there is nothing that can be done without impacting significantly the primary server – nor the mirror.
SQLCat has a nice write-up on what can be done. I have used this method a few times, and felt that it needs to be brought up. You can read the SQLCat article here.
In short (here are the steps from that article), you can do the following:
- On the secondary server, Use ALTER DATABASE MODIFY FILE to move the log file.
ALTER DATABASE <db_name> MODIFY FILE (NAME = LOG_FILE, FILENAME = ‘new location’).
- Stop the SQL Server Service for the instance which has the mirrored (secondary) database.
- Move the log file to the new location specified in the Modify File script already run.
- Start the SQL Server Service for the instance which has the mirrored (secondary) database.
Performing these steps can be just the trick needed to save the day. Performing this move this way has saved me an outage on more than one occasion. Also, this has saved me hours of work that could come along with having to break and rebuild the mirror.
If you have been hiding under a rock, you have probably not heard that SQL 2012 has launched. With it, some really cool features are now available to use in our SQL database environments.
Some of these cool new features include some new DMVs, the AlwaysOn feature, and columnstore indexes.
But did you know that there are some new recommended best practices?
Today, I want to share with you a new best practice that involves table naming schemes.
With the release of SQL 2012, it is now advised that tables be named in such a manner that the name is obfuscated. The reason for this is to slow down any would be attacker from quickly finding which table holds the most sensitive data. Currently it is not too far fetched for an attacker to look in a database and figure out where the orders or customer confidential information may be placed. After all, it is fairly common to name those tables in such a manner that the name describes the data.
Now though, the best practice is pushing in a new direction. And no longer is it even a good idea to use legible words. The drive is for full obfuscation of the table name. In an effort to demonstrate, here is a quick script that will create several tables that are obfuscated in name.
IF not exists (SELECT 1 FROM sys.databases WHERE name = 'Fun')
CREATE DATABASE Fun
SET NOCOUNT ON;
DECLARE @rocket CHAR(100)
DECLARE @boost TINYINT
DECLARE @tiers TINYINT
DECLARE @maxtiers TINYINT
DECLARE @SQL VARCHAR(MAX)
DECLARE @overall TABLE (rocket VARCHAR(MAX));
DECLARE @somevar VARCHAR(MAX)
,@someint INT = 0
SELECT @rocket = ' ', @tiers = 1, @boost = 1, @maxtiers = 4
INSERT INTO @overall (rocket)
WHILE @tiers < @maxtiers
SELECT @boost = POWER(@tiers,2)
WHILE @boost < 12*@tiers
SET @rocket = STUFF(@rocket, (DATALENGTH(@rocket)/2)-(@boost/2), @boost,REPLICATE('*', @boost))
SET rocket = rocket +CHAR(10) + @rocket
SET @SQL = 'Create Table ['+ @rocket + '_'+ CONVERT(VARCHAR,@someint) +'] (someint int);' PRINT @SQL
SET @boost = @boost+2
SET @someint = @someint + 1
SET @tiers = @tiers+1
SELECT @somevar = rocket
PRINT 'The following Tables have been created:' +CHAR(10)+CHAR(13)
I like this new standard. Now, I will just design images into my tables that describe the database in whole. As you can see, this particular database would be useful for maybe a space case or maybe a hobbyist with a high enthusiasm for rockets. Or maybe it just works really well for any demo involved with the SQL 2012 Launch. Other than a neat little picture, the tables are completely obfuscated.
Keep best practices in mind whenever designing a database. Maintaining best practices can help protect the data you were hired to manage and protect. In addition to that, make sure you forget every other word of this post. It is complete and utter hogwash. Happy April Fools.