Backup to Windows Azure from an on-premise SQL Server database

I’ve found this blog post / tutorial by Biz Nigatu on how to backup your on-premise database (SQL 2014) to Windows Azure Cloud Storage:

Backup SQL Server 2014 database to Windows Azure cloud storage

In this blog post we will see one of sql server 2014 new features, Backup SQL Server 2014 database to Windows Azure Cloud Storage.

You might want to bookmark this one, remember for ISO-standards (forget the specific name) you’ll need to store a database backup at a distance of several kilometers away. So this might be an easy solution for that, but keep in mind that it might also be an expensive one 🙂

cloud-storage


Advertisements

SQL Server 2012 Encryption (Security)

SQL Server database encryption the 2012 way…
How to fully secure your sensitive data? Have a look!

Am I still missing anything? Reply!

Query:

Results:


SET NOCOUNT ON;

DECLARE
@Pass1 NVARCHAR(25),
@Pass2 NVARCHAR(25),
@Salt NVARCHAR(25)

SET @Pass1 = '23543'
SET @Pass2 = '45663'
SET @Salt = 'R@nd0mS!a6lTValue'

SELECT TOP 10
FirstName
--Encrypted, 2 times to prevent the use of rainbow tables:
,EncryptByPassPhrase(@Pass2,
EncryptByPassPhrase(@Pass1,FirstName)
) AS Encrypted
--Decrypted:
,Convert(NVARCHAR(50),
DecryptByPassPhrase (@Pass1,
DecryptByPassPhrase (@Pass2,
EncryptByPassPhrase(@Pass2,EncryptByPassPhrase(@Pass1,FirstName))
)
)
) AS Decrypted
--One-way hashing on multiple columns:
,HashedMultipleValues =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+'|'+IsNull(LastName,'NA') --'|' = seperator
)
--One-way hashing on a single column (like creditcard numbers of passwords):
--Don't forget to use HashBytes() multiple times using the same @Salt value!
,HashedSingleValue =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+@Salt --Added @Salt to prevent the use of rainbow tables
)
FROM AdventureWorksDW2008R2.dbo.DimCustomer


 

—>>> ToDo’s (don’t forget!):
– Save all encrypt/decrypt coding (SP/FN) with the ‘WITH ENCRYPTION’-statement,
or you can also use a certificate and key to encrypt/decrypt data:
http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
– Enable TDE (Enterprise Edition of SQL Server 2012) to prevent any user to access the data on disk level:
http://msdn.microsoft.com/en-us/library/bb934049.aspx
– Make sure to secure all inbound and outbound connections by using Certificates (for example by Symantec / Verisign):
http://www.symantec.com/products-solutions/families/?fid=ssl-certificates#tabs
– More info on SQL Server 2012 Encryption:
http://msdn.microsoft.com/en-us/library/bb510663.aspx
– DO NOT store your certificates on the same server….use an encrypted USB disk and store it in a physical safe for example
– Are there any applications using your SQL Server? Hire a hacker to perform a security check (like SQL Injection)

Use PowerShell to script all database objects

Last week I started working with PowerShell for the first time.
My goal was to create a script that would somehow script all indexes (CREATE-statements) in specified databases.

When I was done Google-ing for all kind of solutions, I combined all sorts of existing PowerShell scripts. The resuls was a single PowerShell script that simply scripts all database objects within the specified database (like the ‘Generate Scripts‘-option does in SSMS/SSDT)!

Modify the script to adjust a few settings!
In the header section, you may want to change the destination folder for the output scripts. Look for:
$SavePath = "C:\PowerShell\SQLScripts\" + $($dbname) + "\" + $DateFolder

Another things is that you’ll need to define which databases on which server needs to be scripted in the bottom section of the PowerShell script:

#Execute function for all PM's user databases:

Script-DBObjectsIntoFolders '.' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'localhost' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'servername\instancename' 'AdventureWorks2012'

How to execute?:
Execute this statement in Command (CMD) or via a SQL Agent Job (PowerShell step):

PowerShell "C:\PowerShell\ScriptAllObjectsInDatabase.PS1"

sql-agent-powershell-step-script-all-objects

The Result?:
It creates a new folder every time it is executed, the name of the folder will be a timestamp.

Schedule the thing!

I used the command (CMD) steptype of SQL Agent, because I simply didn’t got it working as a PowerShell steptype:

Download the PowerShell script here: ScriptAllObjectsInDatabase
Don’t forget to save it as ‘ScriptAllObjectsInDatabase.PS1‘!

*** UPDATE ***
In case you would also like to script XML Indexes, add these code-lines to the script on the right locations:

$scriptr.Options.XmlIndexes = $True
...
$scriptDrop.Options.XmlIndexes = $True



What is Social Analytics? (Social Business Intelligence)

Social Media Analytics: Monitor, Measure and Manage Your Reputation on the Wild Wild Web of Social Media.

Social Media Analytics is a must-have for campaigns and organizations eager to track, understand and measure word of mouth in the ether of blogs, social networks and micro-blogging applications.

(Wikipedia is terrible btw)

A couple of populair terms about Social (Media) Analytics:

How does it work?

What will be the result of this kind of analysis?

Want to read more?
Actionable Social Analytics

BumbleBI launched service provider: SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions

BumbleBI launched a new service provider called:
SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions.
 

 
Our servers are hosted in EasyNet’s datacenter at Schiphol-Rijk (Amsterdam, The Netherlands) which is directly connected to one of the world’s fastest internet hubs: Amsterdam Internet Exchange (AMS-IX).
 
Ideal for smaller databases without paying the expensive licensing costs.
Or for the large corporations for world-wide database access for free usage of Excel, PowerPivot, Power View and/or tablet apps.
 
Our Microsoft Business Intelligence (BI) consultants can support deploying your data to this cloud-environment (near realtime of by a daily import job).
 
The databases of these software suites can be made available in our SQL Hotel environment for analysis, reporting, data mining and more:
 

 
Interested? Check one of our websites:
SQLHotel.nl
SQLServerDatabaseHosting.com
 
Spread the word!