Archive

Archive for the ‘SQL Server 2012’ Category

Encryption in SQL Server 2012–Creating Service Master Key and Database Master Key

March 23, 2013 1 comment

 

All Version of SQL Server 2012 including the Express Edition supports Encryption

The first steps to start Encrypting data in SQL Server 2012 are as follows.

 

1. Backup Service Master Key and keep it in a secure location

Use master

BACKUP SERVICE MASTER KEY TO FILE = N'C:\Workspace\MYServiceMasterKey.Key' 
ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

2. Create Database Master Key for Encryption for the databases you want to protect

Use AdventureWorks2012
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

 

3.  Backup the Database Master Key to a secure location

Use AdventureWorks2012
BACKUP MASTER KEY TO FILE = N'C:\Workspace\DatabaseMasterKey.Key' 
    ENCRYPTION BY PASSWORD = N'p@ssw0rd'

 

4. Open the Database Master Key and Test it

Use AdventureWorks2012
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';

CLOSE MASTER KEY;

 

If you specify the wrong password to open the database master key, you will be getting the below error

image

 

To view the current database master key in your database, you can use the following system security catalog view

 

image

 

Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.

Advertisements

ApexSQL Complete–Advanced SQL Server Intellisense for Free !!!

March 16, 2013 Leave a comment

SQL Server 2012 has built-in Intellisense, ApexSQL Complete takes Intellisense to next level.

It available as a Free Tool and its one of the best Free Tools available for SQL Server in market.

Its available for download in this below link

http://www.apexsql.com/sql_tools_complete.aspx

The download also includes all the tools from ApexSQL Developer Studio (15 Developer Tools, 3 of them are Freely available)

The installation process is quite simple

image

image

You can by default install all the products and try them for Free

image

or pick ApexSQL Complete 2012

image

 

image

Once the install is complete, Please launch SQL Server 2012 Management Studio,

ApexSQL Complete menu has the following options

image

Please click on “Enable Auto-complete” to use ApexSQL Complete instead of built-in Intellisense

You can open a new Query window and start typing the query and you can activate the Intellisense by pressing Ctrl + Space

You can choose the columns which you want to be included in Query and If you noticed, It automatically add Alias to the Table name automatically as well, which will be very helpful for complex queries and using in joins.

image

Also when you select a table, you can see the CREATE Script for the table as well and you have option to “Copy” the script as well

image

ApexSQL Complete also check your query as you type and shows them in “Error List” window

image

ApexSQL Complete Error List window works similar to Visual Studio Error List window, double clicking on the Error takes you to the exact Line and Column so that you can easily fix it

image

ApexSQL Code Structure

This is one of the most powerful feature I liked in ApexSQL which is not available in any of the other competitive production

It parses your SQL code and display the Code Structure as Tree, so that you can easily navigate the code and click and move to the preferred statement easily.

image

ApexSQL Complete Options

General Options

The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”

image

The below view “dbo.vwAddress_Enc” is an encrypted view

image

Since I have enabled the Decrypt option, ApexSQL Complete is able to show the definition of the Encrypted View, so that I can copy the script

image

Insert Options

image

Hints Options

If you don’t want to see System tables or views in the Intellisense list, you can deselect it using “System Tables” option, similarly you can customize the other hints too

image

Aliases Options

ApexSQL Complete by default create aliases for tables or views selected from Intellisense window, but if you want use your own custom defined Alias, you can define them here

image

I have created an alias named “Emp” for the Employee table, when I write the query, now the ApexSQL Complete Intellisense window displays the Alias on top

image

When you select the Alias, it automatically fills the referenced object name

image

Schema Cache Options

Here you can see the currently cached Database and its object counts, you can select them and right click and Delete or Refresh them

image

Snippet Manager

Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well

image

image

image

Also after doing the customization, You can Export the options and share with your colleagues, and they can import and use the Snippets you created or use the same Aliases you have created.

ApexSQL Developer Studio also has the following products available for free as well

1. ApexSQL Search

2. ApexSQL Refactor

I hope you find this information useful !!! Have fun coding using ApexSQL Complete !!!

How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared

March 14, 2013 Leave a comment

SQL Server 2012 has a new Function called EOMONTH() which returns the last day of the month for the specified date in parameter and also has an option to define offset as well.

Let us first see How we can do this in SQL Server 2008

--Last Day of Last Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS [Last Day of Last Month]
--Last Day of This Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) AS [Last Day of Next Month]

Let us see How SQL Server 2012 simplifies the above code

--Last Day of Last Month
SELECT    EOMONTH(GETDATE(), -1) AS [Last Day of Last Month]
--Last Day of Current Month
SELECT    EOMONTH(GETDATE()) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    EOMONTH(GETDATE(), 1) AS [Last Day of Next Month]

In SQL Server 2008 we had to make 4 function calls in order get the output, whereas SQL Server 2012 helps us do the same thing in 2 function calls

We can use the same function to get First Day of the Month as well

--First Day of Last Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AS [First Day of Last Month]
--First Day of This Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS [First Day of This Month]
--First Day of Next Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE())) AS [First Day of Next Month]

Hope this helps !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

March 12, 2013 Leave a comment

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

image

image

image

image

image

image

image

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

image

Select the databases you want to Backup

image

Click on “Add backup destination” to specify the backup destination

image

For our demo, Let us select Drop box option

image

Click on “Authorize…” and Login to your Drop Box account

image

Click “Allow” to approve the Drop Box access

image

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

 

image

Specify the Folder to which Backup has to be done

image

Click “Test” to make sure it works

image

Once Test is completed, Click “Ok” to proceed

image

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup

image

If you want to enable Email notifications, Please select and specify the email address

image

Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer

image

Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed

image

Scheduling the Backups

If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option

image

Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed

image

Please click “Run Now” to test the job manually

image

image

image

Once test is completed, Now click on “Save” button on the tool bar to save the job.

Hope this helps !!!

Visual Studio 2012 now supports Business Intelligence projects

March 10, 2013 Leave a comment

Microsoft has released Visual Studio 2012 – SQL Server Data Tool for Business Intelligence projects

Download is available in the below link http://www.microsoft.com/en-us/download/details.aspx?id=36843

Steps for Installation are as follows:

image

image

image

image

image

image

image

Now you can see the Business Intelligence project templates in Visual Studio 2012

image

Analysis Services Project Templates

image

Integration Services Project Templates

image

Reporting Services Project Templates

image

Hope you find this information useful !!!

%d bloggers like this: