Audit SQL Server permission changes to improve overall security

One of the most important tasks for a DBA aiming to keep database and the data in it secure and away from unauthorized access or, heaven forbid, malicious changes is to always stay on top of the effective SQL Server permissions his users have over the SQL instances as well as the databases, database objects and data stored in them. Although this might seem like a pretty straightforward task, as the number of database users grows on one hand, and the number of databases and objects on the other things can get really complicated. Add to that the ever changing business requirements, and soon, unless you have some kind of documenting system in place, you can end up with users not having sufficient permissions or even worse – users having more permissions that they actually require

April 23, 2013

Improve the performance of an ETL process

Due to the sheer volume of data usually involved in an Extract – Transform – Load (ETL) process, performance is positioned very high on the list of requirements which need to be met in order for the process to go as smoothly as possible. Here are some guidelines which will help you speed up your high volume ETL processes

April 23, 2013

Synchronize SQL Server databases in different remote sources

The scenarios

The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc. are designed to collect data from remote locations and then send it to a data center. Also, the data from the data center, occasionally need to be sent back to these remote locations, to keep them up to date.

For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day.

In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database.

April 19, 2013

Get to know an inherited SQL Server database

Taking over a database you’re not familiar with requires you to dedicate some time to analyze and understand it. Deciphering an inherited database takes some time, that among other factors, depends on the available documentation (if any), comments left in object SQL, and database complexity.

April 18, 2013

Recover deleted records in Dynamics CRM 2011

Even though Dynamics CRM 2011 is very on clear on the fact that that record deletion is permanent and even provides a warning, it’s not unusual one or more CRM records to be deleted by accident. The bad news is that once the records have been deleted, there is no way to undo the deletion as removing a record from CRM actually translates to deleting it from the underlying SQL database.

That is one of the biggest differences between Dynamics CRM 4 and Dynamics CRM 2011. Unlike Dynamics CRM 2011, Dynamics CRM 4 uses the “soft delete” approach. This means that a deleted record in Dynamics CRM 4 could be recovered relatively effortlessly – to recover a deleted record from Dynamics CRM 4 all you have to do is to connect to the underlying database, locate the deleted record and simply update the value in that record’s IsDeleted column to false. So, how can one go about recovering deleted records from a Dynamics CRM 2011 database?

April 12, 2013

Use database backups as live SQL Server databases to reduce downtime

Being able to revert to the latest available backup immediately is a key requirement that every SQL Server DBA needs to meet. There are so many scenarios in which immediate action needs to be taken – starting from actual disk failure or corruption, system failure all the way to UPDATEs without WHERE clauses, malicious INSERTs or accidental DELETEs, DROPs or TRUNCATE operations. However, usually this is easier said than done

Even if you take meticulous care of your database backups, have the last available database backup verified and at hand and are sure that restoring it won’t overwrite any valid changes made to the database since the accident has occurred – the fact that the system will experience downtime until the backup is fully restored still remains. Luckily, downtime caused by the time needed for a full database backup restore can be minimized

April 12, 2013

How to check whether you can safely deploy to production database

The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies.

As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations.

April 11, 2013

How to script encrypted SQL database objects

One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects’ DDL script can be locked, so that unauthorized users cannot see them:

CREATE FUNCTION [dbo].[ufnGetAccountingEndDateEnc]()
RETURNS [datetime] 
with encryption 
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));

April 8, 2013

Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – you might need a specific object (table, view, stored procedure, user, etc.) recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If your database is versioned under source control, you can easily find object there but let’s assume you only have SQL backups.

April 5, 2013

Script a database for specific DML records only

Sometimes the best test data is the data you have in your live, production SQL Server database. Since using production data is most often not an acceptable option, this requires retrieving the data from a live database table and inserting it into a new table. The challenge arises if you don’t want to insert all records from the original table but only the records that comply with certain conditions.

How to create INSERT INTO script that moves only specific data from one table to another.

April 5, 2013