Creating UTC function

UTC dates are used across the board for the applications need to be run on different time zones.

Recently I needed to migrate data from one source to different instances of databases that will be used in different countries (time zones). I expected to see a predefined SQL function that gets the datetime value along with the Time zone and returns the datetime in UTC format, but could not find such a function which was kind ofsurprising to me!

Based on my research, it can be achieved using a CLR function. Let's follow the steps below:

Step 1: We need to create the assembly CLR function which does the conversion. The CLR function can be written in .net and gives us the assembly (dll file) to be used in SQL Server. If you are not sure how to create a CLR function, refer to the link below:

http://blogpad-online.blogspot.com.au/2012/06/create-assembly-clr-function.html

Step 2: When the dll file is created, we can create an assembly to call it. For example, we name the dll file ‘TzConverter’ and copied it to the folder below:

'C:\Users\Dev\UTC\TzConverter.dll'

In creating the assembly to call CLR function, it is important to set the TRUSTWORTHY ON and create the assembly with UNSAFE permission set:

USE testDB
GO

ALTER DATABASE testDB
SET TRUSTWORTHY ON;
GO

CREATE ASSEMBLY tzconverter
FROM 'C:\Users\Dev\UTC\TzConverter.dll'
WITH PERMISSION_SET = UNSAFE
GO

Step 3: Now that the assembly has been created, we need to create the SQL function. It enables us to call the CLR function in SQL Server:

CREATE FUNCTION [dbo].[ToUtc](@dt [datetime], @timeZone NVARCHAR(100))

RETURNS [datetime] WITH EXECUTE
AS CALLER AS
EXTERNAL NAME tzconverter.[UserDefinedFunctions].ToUtc
GO

sp_configure'clr enabled', 1;
GO

RECONFIGURE;

GO

Ok, we are done. Call the function and get the UTC datetime!

--- Examples : It shows the current date time in AustralianEastern standard time format
SELECT GETDATE(), [dbo].ToUtc(GetDate(), 'AUS Eastern Standard Time')

--- Examples : It shows the current date time in New Zealandstandard time format

SELECT GETDATE(), [dbo].ToUtc(GetDate(), 'New Zealand Standard Time')

Opening a database only using the mdf file

I love to improve my knowledge and skills, especially in the profession I work in. In every day work, we come across to some problems and solve them. Therefore, I thought it would be worthwhile to share my experiences with you, hope you find them helpful.
This is my first post. I’m going to talk about attaching a database using only the .mdf file.

Attach mdf file without ldf file in Database
When you have both mdf and ldf files of a database, attaching the database would be very easy, but if we have only the mdf file and the log file is missing, it would not be as straight forward as using the SQL Server management studio visual interface. You need to write SQL script to do that.
First let see what would happen if we try to attach the database by mdf file from SQL Server Management studio:

After selecting the file, it opens two entries in the grid below, one for mdf file and the other ldf file that is missing:



After clicking ok, the error below would be displayed:



By clicking on the referred hyperlink the error details shows the issue as expected. The ldf file is missing:


Solution:
The script below will do the job easily, only you need to copy the mdf file to the DATA folder that the other SQL Server db files are stored. In my case the database name AdventureWorks2008R2 and it is stored in the following folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

We use CREATE DATABASE syntax using “FOR ATTACH_REBUILD_LOG” option. This command will create the database based on the mdf file and will rebuild the missing ldf file.


CREATE DATABASE AdventureWorks2008R2 ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO


Pressing F5 and the database will be created easily :)

SQL Server 2008 R2 New Features

Introduction
This article will introduce the top features and benefits of SQL Server 2008 R2 which seem to be interesting for both DBAs and Developers.

Microsoft SQL Server 2008 R2 is not a major revision that is why it is called R2 which is sort of intermediate release.

Following are some of new features available in Microsoft SQL Sever 2008 R2:

1. Powerpivot
This feature is a good news for people who want to load large amount of data in Excel and use it back of their Share point server. However to be able to use this new feature, we need Excel 2010 and if you wan to use in for Sharepoint, you will need Sharepoint 2010.

Every one who works with loading large number of records into Excel know loading data into Excel is time consuming and even with a good memory it takes a long time.

Due to using column compression, loading millions of record into Excel 2010 just takes a less of proceessing thousands of record before this feature.

   

The other good thing about Power pivot is that, it comes with analytical capabilities which is quite useful.

2. StreamInsight

This feature lets us to handle the processing of the events in flight. For example using this component , monitoring the production server can be implemented easily. Let say we are going to monitor the CPU metrics for a server and we don't want to record every single metric, we just are interested to persist the aggregated values of the metrics to the database.

That's where StreamInsight comes handy. The engine has been implemented as a set of .Net classes. To be able to call and use these classes, the code must be written in .Net. This would be a real time system and we don't have to wait for table writing commits.

3. Master Data Services Master Data Services is a new feature which can assist businesses to create a safe guard for data validation and applying business rules before distributing them to the several systems.

Data items such as customer details and product names should be centrally managed so that all consuming applications have the same information. It provides us a database and interface which can be used to manage the central data.

4.Multi Server Monitoring

This feature is so useful for DBAs. We can see volume storage utilisation at the drive level, file group and file (data and log) level.

5. Data-Tier Application (DACPAC)

DACPAC is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package in a visual studio file. It is specially useful when the newly developed database objects want to be deployed. Instead of sending the objects (e.g: Store procedures, table definition, etc) in individual SQL files, they can be packaged in a file and sent to the DBA to deploy. It limits the human interactions and consequently mistakes.