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
To view the current database master key in your database, you can use the following system security catalog view
Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.
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
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
You can by default install all the products and try them for Free
or pick ApexSQL Complete 2012
Once the install is complete, Please launch SQL Server 2012 Management Studio,
ApexSQL Complete menu has the following options
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.
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
ApexSQL Complete also check your query as you type and shows them in “Error List” window
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
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.
ApexSQL Complete Options
The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”
The below view “dbo.vwAddress_Enc” is an encrypted view
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
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
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
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
When you select the Alias, it automatically fills the referenced object name
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
Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well
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 !!!
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 !!!
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
2. Local or Network Folder
3. Drop Box
5. Amazon S3
7. Google Drive
Professional Version also supports SFTP and FTPS and AES Encryption of Backup files
Please follow the below steps for installation
The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup
Select the databases you want to Backup
Click on “Add backup destination” to specify the backup destination
For our demo, Let us select Drop box option
Click on “Authorize…” and Login to your Drop Box account
Click “Allow” to approve the Drop Box access
Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.
Specify the Folder to which Backup has to be done
Click “Test” to make sure it works
Once Test is completed, Click “Ok” to proceed
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
If you want to enable Email notifications, Please select and specify the email address
Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer
Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed
Scheduling the Backups
If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option
Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed
Please click “Run Now” to test the job manually
Once test is completed, Now click on “Save” button on the tool bar to save the job.
Hope this helps !!!
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:
Now you can see the Business Intelligence project templates in Visual Studio 2012
Analysis Services Project Templates
Integration Services Project Templates
Reporting Services Project Templates
Hope you find this information useful !!!