Encrypting Data with a PassPhrase

The simplest form of encryption is encrypting data by a PassPhrase using the EncryptByPhrase function.

EncryptByPhrase function lets you encrypt your data using a passphrase. In order to decrypt the same data, you use the DecryptByPassPhrase function. This lets you decrypt the data using the same passphrase you used in encrypting the data. THe example below demonstrate how to use a Passphrase in encrypting and decrypting data.


Declare @text varchar(100)
Declare @key varchar(100)
Declare @decrypted varchar(100)
Declare @encrypted varbinary(102) --requires two additional bytes

Set @text ='Hello World'
Set @key = 'Pa55word'

-- Encrypt the text using the @key Pasphrase(Pa55word)
Set @encrypted = EncryptByPassPhrase(@key, @text)
Select @text as 'Text to Encrypt', @encrypted as 'Ecnrypted text'

-- Deccrypt the text using the @key Pasphrase(Pa55word)
Set @decrypted = DecryptByPassPhrase(@key,@encrypted)
Select @encrypted as 'Encrypted text', @decrypted as 'Decrypted text'

Output:

-- The text has been encrypted using the PassPhrase set at @key variable
Text to Encrypt Encrypted Text
----------------------------------------------------------------------------------------
Hello World 0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF

-- The text has been decrypted using the PassPhrase set at @key variableEncrypted
Encrypted text Decrypted Text
0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF Hello World

Note that the PassPhrase are case sensitive. Secondly, you can only decrypt the text using the same passphrasethat were used in ecrypting the data.

Percentage of Logspace Used

You can use this SQL below to check the percentage of logspace currently used by your database.



dbcc sqlperf(logspace)

Apart from setting up alert to tell you when your database is running out of logspace, the above query does the same as well. However, you actually have to manually run the query to view the database logspace usage.

Current Active Transactions

To find how many active transactions are currently running, use the SQL below.



select * from sys.dm_tran_session_transactions

Concatenate Single Column into Row

This query concatenates a single column into one row

CREATE TABLE Numbers
(
number VARCHAR(20)
)
GO
INSERT Numbers SELECT 'one'
INSERT Numbers SELECT 'two'
INSERT Numbers SELECT 'three'
INSERT Numbers SELECT 'four'
GO

declare @list varchar(4000)
set @list = ''
select @list = @list + ',' + number from numbers
select @list = substring( @list, 2, len( @list ) -1) -- To Remove first comma
select @list

OUTPUT
-----------------------------------
one,two,three,four

Authentication Mode

To find out if you are connected to SQL Server using NTLM authentication or Kerberos.

select * from sys.dm_exec_connections where session_id = @@spid

select net_transport, auth_scheme from sys.dm_exec_connections where session_id = @@spid

Database Collation

The following code returns the collation settings of AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation

To display your own database collation, substitute the AdventureWorks Database with your own database.

DBCC Shrinkfile

The code below shrinks the transaction log file to target size specified.

Use AdventureWorks
GO
DBCC Shrinkfile(filename, target_size)

filename is the logical name of the file to shrink
traget_size is the size of the transaction log file in megabytes to be shrunk. For example

Use AdventureWorks
GO
DBCC Shrinkfile(AdventureWorks_Log, 10)

The above code shrinks down the transaction log file named AdventureWorks_Log to 10MB

Network Load Balancing

Network Load Balancing(NLB) distributes incoming I.P(Network) traffic across cluster of servers. NLB provides high availability by automatically detecting the failure on a server and repartioning incoming traffic among the remaining servers.

NLB is mostly used with web services. You cannot run both NLB and Microsoft Clustering Services(MSCS) on the server, however you can run them on the same solution.

You might use both on a database deriven web servers.

Saving SSIS Package to MSDB Database

SSIS does not support multi-instances. It does not allow saving Packages to MSDB database if there are no default SQL Server 2005 installation.

To enable saving packages on the MSDB subfolder in SSIS, You will have to change the MsDtsSrvr.ini.xml file located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory. Sample file:



change the line where the red arrow points to the dot(.)to:



Do this ONLY if there are no default instance of SQL SERVER 2005 installed on your Server.

SERVERPROPERTY Function

SERVERPROPERTY Function returns property information about the server instance.

Level of the version of the instance of SQL Server.
SELECT SERVERPROPERTY('ProductLevel')

Returns one of the following:
'RTM' = Original release version
'SPn' = Service pack version
'Bn', = Beta version


Installed product edition of the instance of SQL Server
SELECT SERVERPROPERTY('Edition')

Name of the instance the user is connected
SELECT SERVERPROPERTY('InstanceName')

Server instance is configured in a failover cluster
SELECT SERVERPROPERTY('IsClustered')

Consult BOL for other SERVERPROPERTY propertyname

Single Quotes

When specifying a value with a single quote, you need to double it.

SELECT 'Hello''s'

Returns: Hello's

Enabling Service Broker

To configure database mail, you need to enable service broker on MSDB databse. Make sure that Sql Server Agent is stoped before running the following query below.

ALTER DATABASE MSDB
SET ENABLE_BROKER

To check that service broker has been enabled, run this piece of sql below.

SELECT IS_broker_Enabled
FROM sys.databases
WHERE name = 'MSDB'

If 1= Broker enabled.
If 0 = Broker Not enabled.

ERROR 18452: The User is not associated with a Trusted SQL Server Connection

This can occur due to one of the following reasons:

1: If SQL Server has been configured to operate in “Windows Authentication mode” and doesn’t allow the use of SQL Logins.
Resolution: Change the Authentication mode from “Windows Authentication mode” to “Mixed Mode”(SQL SERVER and “Windows Authentication mode”)

2: You are trying to connect using SQL Server Authentication but the login used does no t exist on the SQL Server.

3: The login could have been using windows Authentication but the login is an unrecognized windows principal. An unrecognized windows principal means that the login cannot be verified by windows. This could be because the windows login is from a domain not trusted by the server. In this case do the following to determine if there is a trust between the client and the server.
PING: The Server on both IP and Name from the Client.
PING: The Client on both IP and Name from the Server.

Find all SQL Servers on the Network

To find all the available SQL Servers on the network that are broadcasting their existence.

Exec Master..XP_CMDSHELL 'SQLCMD -L'

First you have to enable XP_CMDSHELL from the Surface Area configuration before running this command.

Error when configuring database mirroring

This error occurs when configuring database mirroring on a stand alone PC. One or more of the server network addresses lacks a fully qualified domain name (FQDN).Specify the FQDN for each server, and click Start Mirroring again.The syntax for a fully-qualified TCP address is:TCP://.[.]:
--------------------------------------------------------------------------------------
To trouble shoot this error, do the following below.
Right click on the "My Computer" then properties.
Click on the "computer name" tab, then click on the change button next to the "To Rename this computer or join a domain.
click change" then you see the "Computer name changes" window, click the "More" button on it. You will see the "DNS suffix and NetBIOS ComputerName" window. Here, type "local" to the "Primary DNS Suffix of this Computer" textbox. then press OK 3 times and restart computer.

After put this "computername.local" to your computer name for the mirroring computer server network address.

If you are in domain environment, just use really the FQDN.This is applicable just in case of stand alone machine.

Page Life Expectancy

Page Life Expectancy value is the number of seconds a page will stay in the buffer pool without references. So a buffer that has 300 seconds page life expectancy will keep any given page in the buffer pool for 5 minutes before the buffer pool flushes the page to disk.
To determine page life expectsncy value using query.

SELECT [cntr_value]
FROM master..sysperfinfo
WHERE object_name = 'SQLSERVER:BUFFER Manager'
AND counter_name ='Page Life Expectancy'

Copy-Only Backup

Copy-only backup are new feature in SQL 2005. It allows a backup of any type to be taken without affecting any other backups. Copy-only backup doesn’t affect the restore chain. It cannot be used as a restored base in the event of failure. They are useful in a situation where you want to get a copy of the database for testing purposes or when you want to refresh the staging, development and testing databases.

Copy-only backups are not supported via SQL Server Management Studio(SSMS). It must be performed using T-SQL Backup command.

Backup Database MYDATABASE
TO DISK = 'Drive\Folder\filename.bak'
WITH COPY-ONLY

Error: Cannot resolve collation conflict for equal to operator

If two tables coluns has different collation, when you try to do a join on both querries, it will generate an error "Cannot resolve collation conflict for equal to operator".To resolve the collation conflict add following keywords around the equal to operator.

SELECT Column01, Column02
FROM Table01
INNER JOIN Table02
ON Table01.Colum01 COLLATE DATABASE DEFAULT = Table02.Column01 COLLATE DATABASE DEFAULT

Oldest Active Transaction

The following code displays the oldest active transaction on a Database.

USE Mydb
Go
DBCC OPENTRAN
Go