Thursday, September 17, 2015

CLR function and procedures in SQL Server(GZipStream)

CLR function and procedures in SQL Server(GZipStream)

Today I have work with some CLR function and procedures in SQL Server. And plan to publish some CLR code example in my weblog. 


following code will explain,
  • How to Write CLR function using C# and T-SQL.
  • How to add “GZipStream” in to CLR function.
  • How to compress and decompress given string.
  • How to Create “ASSEMBLY” using T-SQL.


First will create C# code for CLR Assembly mapping DLL,

using System;using System.Collections.Generic;
using System.Text;using System.IO;
using System.IO.Compression;using System.IO.IsolatedStorage;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

    public class GZipDatabaseUserGroup
    {
        public static byte[] GZip(string value)
        {
            byte[] byteArray = Encoding.UTF8.GetBytes(value);
            MemoryStream ms = new MemoryStream();
            GZipStream zip = new GZipStream(ms, CompressionMode.Compress);
            zip.Write(byteArray, 0, byteArray.Length);
            zip.Close();
            byteArray = ms.ToArray();
            ms.Close();
            zip.Dispose();
            ms.Dispose();
            return byteArray;
        }

        public static string unGZip(byte[] data)
        {
            MemoryStream output = new MemoryStream();
            MemoryStream ms = new MemoryStream(data);
            GZipStream zip = new GZipStream(ms, CompressionMode.Decompress);
            zip.CopyTo(output);          
            string strOutPut = Encoding.UTF8.GetString(output.ToArray());
            return strOutPut.ToString();
        }

        public static string fn_CLR_DecompressGZIPBytesAsString(byte[] ZipInput)
        {
            try
            {
                string returnVal = unGZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return "Error";
            }
        }

        public static byte[] fn_CLR_CompressGZIPStringToBytes(string ZipInput)
        {
            try
            {
                byte[] returnVal = GZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return null;
            }
        }
    }
using above C# code you can build DLL. And next with using above DLL you can create  assembly as follows,

CREATE ASSEMBLY <ASSEMBLY NAME> from '<DLL Path>\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;

Using above Assembly name, you can create CLR function as follows,

CREATE FUNCTION fn_CompressGZIPStringToBytes(@InPut nvarchar(max)) RETURNS varbinary(max)ASEXTERNAL NAME <ASSEMBLY NAME>.<C# Name>.<C# function name>;GO
Hope now you have some idea about how to create CLR function.And this will need bit C# knowledge, if you are not friendly with C#, then you can get some help from Microsoft MVA.

Well! now i'll show you full T-SQL code example for above task,


use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

USE [DATABASEUSERGROUP]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[GZipDatabaseUserGroup_Table];
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
CREATE TABLE [dbo].[GZipDatabaseUserGroup_Table](
[id] [int] NULL,
[strData] [nvarchar](4000) NULL,
[unGzipBinary] [varbinary](max) NULL,
[GzipBinary] [varbinary](max) NULL
); 
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_DecompressGZIPBytesAsString]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_DecompressGZIPBytesAsString];
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_CompressGZIPStringToBytes]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_CompressGZIPStringToBytes];
END
GO

IF EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
DROP ASSEMBLY GZipDatabaseUserGroup;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
CREATE ASSEMBLY GZipDatabaseUserGroup from 'E:\databaseusergroup\bin\Debug\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;
END
GO

CREATE FUNCTION fn_CompressGZIPStringToBytes
(@InPut nvarchar(max)) RETURNS varbinary(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_CompressGZIPStringToBytes
GO

CREATE FUNCTION fn_DecompressGZIPBytesAsString
(@InPut varbinary(max)) RETURNS nvarchar(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_DecompressGZIPBytesAsString;
GO

--insert data
INSERT INTO [dbo].[GZipDatabaseUserGroup_Table]([id],[strData],[unGzipBinary],[GzipBinary]) 
VALUES(1, 'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server',
CAST(N'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server' AS varbinary(max)),
dbo.fn_CompressGZIPStringToBytes('Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server')) ;
GO
SELECT DATALENGTH([unGzipBinary])unGzipBinary,DATALENGTH([GzipBinary])GzipBinary,* FROM [dbo].[GZipDatabaseUserGroup_Table];
GO

SELECT dbo.fn_DecompressGZIPBytesAsString([GzipBinary]) GzipBinary,* FROM [DATABASEUSERGROUP].[dbo].[GZipDatabaseUserGroup_Table];
GO


use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
Cheers...
and Most welcome your comments and ideas...
      

Tuesday, September 15, 2015

SQL Server - Linked Servers

SQL Server - Linked Servers

Hope most of SQL Server DBA or Developer knows how to create link server in SQL Server.according to Microsoft  "the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server. " then you are eligibale to create link server between two data sources. This following picture will explain great view of link servers,(this picture used from MSDN)




  • How to create link server between two SQL Servers,
USE master
GO
--MSSQL
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP'; -- provide the linking server name
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN
EXEC master.dbo.sp_addlinkedserver
@server=@LINKED_REMOTE_SERVER_NAME,
@srvproduct='SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,
@useself='False',
@locallogin=NULL,
@rmtuser='sa',
@rmtpassword='<sa password>';--you can used any user name and pwd with sysadmin role
EXEC master.dbo.sp_serveroption
@server=@LINKED_REMOTE_SERVER_NAME,
@optname=N'query timeout',
@optvalue=N'36000';
END ELSE
BEGIN
PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';
END
BEGIN TRY
EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;
PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';
END TRY
BEGIN CATCH PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();
END CATCH

  • How to create link server between SQL Server & Oracle,


USE master
GO
--ORACLE
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'ORA_DATABASEUSERGROUP'; -- provide the linking server name
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN
EXEC master.dbo.sp_addlinkedserver 
@server = @LINKED_REMOTE_SERVER_NAME, 
@srvproduct=N'Oracle'
@provider=N'OraOLEDB.Oracle'
@datasrc=@LINKED_REMOTE_SERVER_NAME;

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,
@useself=N'False',
@locallogin=NULL,
@rmtuser='ORA_USER1',
@rmtpassword='<password>';

EXEC master.dbo.sp_serveroption 
@server=@LINKED_REMOTE_SERVER_NAME,
@optname=N'query timeout',
@optvalue=N'36000';

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;
END ELSE
BEGIN

PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';

END

BEGIN TRY
EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;
PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';
END TRY
BEGIN CATCH
PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();
END CATCH

  • Drop Link Server
USE master
GO
--DROP LINK SERVER
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP' -- provide the linking server name
IF EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN EXEC master.dbo.sp_dropserver
@server=@LINKED_REMOTE_SERVER_NAME,
@droplogins='droplogins';
END
Well! hope above script will help you to manage SQL Server link servers.
Cheers...
and Most welcome your comments and ideas...

Delete SQL Server ErrorLog

Delete SQL Server ErrorLog


After long time i have come up issue "SQL Server ErrorLog" grown to a very large size. I have done this SQL Server 2005 and after that, I have never come up this issue. 

~~~
This Monday, I have small issue (most common one :) ) SQL Server Database server have very less disk space.   
As usual, I have verify transaction log grown in "tempdb" database because our most of temp databases(all local test database environments) located in C drive. But unexpectedly it's normal, and I was bit surprised! 
finally I have just check SQL Server error log(As a best practices any DBA this should check first unfortunately I haven't follows that :( )               
well! well! I was bit happy because after long time I have face this issue. :D :D 
~~~

use master 
GO
EXEC sp_cycle_errorlog;
GO
And I have check MSDN, Did SQL Server come up with any new method to face above issue. but according to Microsoft still we have to follows old way. 

with above command you can shift SQL Server error log one by one and last log will be deleted.And with restating SQL Server instance also will be apply same method in background.  

Cheers...
and Most welcome your comments and ideas...

Friday, September 11, 2015

CREATE DATABASE SNAPSHOT GIVEN DATABASE

CREATE DATABASE SNAPSHOT GIVEN DATABASE

                            Sometimes you have noticed, when you need to create database snapshot of given database, then SQL Server management studio will not support that facility for your self.The only way is, you have to execute "create database snapshot" T-SQL script.
And at the same time, if your database contains many data files in different disks, then you have to take some time to create script for given database. 
And same as if you need to change snapshot location to different disk then, again you need to change it manually.

Hope this T-SQL script will help you above issues,



SET NOCOUNT ON

DECLARE @database_name nvarchar(128)='<Your Database Name>';
DECLARE @database_snapshot_location nvarchar(128)='';--if this is blank then snapshot create same location
DECLARE @ssql nvarchar(max)='';

SET @ssql='CREATE DATABASE ' +@database_name +'_SNAPSHOT  ON '
SELECT @ssql=@ssql+ssql_data FROM
(
SELECT  TOP (100) PERCENT N'( NAME = N'''+name+''' , FILENAME = '+CASE WHEN @database_snapshot_location='' THEN ' N'''+REPLACE(REPLACE(physical_name,'.mdf','_SNAPSHOT_DATA_FILE.ss'),'.ndf','_SNAPSHOT_DATA_FILE.ss') ELSE ''''+ @database_snapshot_location+name+'.ss' END+''' )'+CASE WHEN (MAX([file_id]) OVER(PARTITION BY database_id))<>[file_id] THEN ',' ELSE '' END+char(10)+char(13) ssql_data
FROM sys.master_files WHERE (database_id =DB_ID(@database_name)) AND ([type]=0) 
ORDER BY [file_id] 
) T;
SET @ssql=@ssql+' AS SNAPSHOT OF ' +@database_name +' ;'
PRINT @ssql
EXEC(@ssql);
    
Cheers...
and Most welcome your comments and ideas...