Encrypt and Decrypt Column Value In SQL Server 2012 Table

/* First Create table */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Password]( [ID] [int] IDENTITY(1,1) NOT NULL, [Password] [varbinary](100) NOT NULL, [Application] [varchar](100) NULL, [CreateDate] [datetime2](7) NOT NULL, CONSTRAINT [PK_Password] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = […]

Read More »

Encrypting SQL Server 2012: Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the ‘data at rest’. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files […]

Read More »

Monitoring Disk Space on SQL Server and Email

Keeping tabs on available disk space on your SQL Servers is something that every DBA should do- because once SQL Server runs out of disk on the underlying host, then everything obviously comes to a crashing halt. While pro-actively sizing data and log files sizes is the best technique to use when it comes to […]

Read More »

SQL Server: Drop a foreign key

SQL Server: Drop a foreign key Learn how to drop a foreign key in SQL Server with syntax and examples.   Description Once a foreign key has been created, you may find that you wish to drop the foreign key from the table. You can do this with the ALTER TABLE statement in SQL Server (Transact-SQL).   Syntax The […]

Read More »

GRANT Schema Permissions on all Store Procedures.

Challenge: Developer emailed me today and asked me to have db_owner access to production database. Initial Reaction: When I first saw this email, I said to my self “HELLO NO” Solution: I took a deep breath and emailed him and asked “What exactly are you going to do in production” His response was “I would […]

Read More »

Don’t go Cheap on Memory!!

  If you have SQL Server enterprise edition (expensive version ), Do not go Cheap on RAM.  If your databases are larger than 1TB, I would highly recommend starting with 256GB . The best way to to justify your Memory needs start with a baseline of the environment.  Counters to begin monitoring would include: SQL […]

Read More »

What is a Linked Server and How to set it up?

Sometimes an application may need data stored in another database on a different instance of SQL Server. That different instance might be on the same physical machine or might be on another machine altogether. So what do you do in this situation? Your options depend on your data requirements, like how up to date does […]

Read More »

Who are the sysadmins in SQL Server?

Sysadmins are pretty much powerful in the SQL Server environment. They can put the whole SQL Server infrastructure under their control. That said, it is important to know who else has this fixed server role. Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a […]

Read More »