Move SQL data files to a different location

Example Call:

EXEC AA_Conversion.Admin.MoveDataBaseFile ‘Database1’, “F:\Server\Data\”

CREATE PROCEDURE Admin.MoveDataBaseFile
(
@DatabaseName NVARCHAR(255),
@TargetFileName NVARCHAR(512) –Complete location with file name e.g., “F:\Server\Data\”
)
/*****

— ============================================================================================================
— Author : Raja Gogoi
— Create date : 08-Apr-2019
— Description : This stored procedure moves the physical database file to another location on the same server.
The assumption is that the database has only single log file.
— ============================================================================================================
*****/
AS
BEGIN

DECLARE @sqlText AS NVARCHAR(MAX), @DatabaseDataFileName NVARCHAR(255), @DatabaseLogFileName NVARCHAR(255), @ParamDefinition NVARCHAR(516)
DECLARE @tblFiles AS TABLE(Id INT, FileName VARCHAR(64))
DECLARE @Id AS INT

SET NOCOUNT ON;

BEGIN TRY
–Move the primary physical data file(s) to target location

SET @sqlText = ‘
SELECT ROW_NUMBER() OVER(ORDER BY physical_name) AS Id, REVERSE(LEFT(REVERSE(physical_name), CHARINDEX(”\”, REVERSE(physical_name)) – 1)) AS FileName
FROM ‘+ @DatabaseName + ‘.sys.database_files
WHERE type = 0 –for data files’

INSERT INTO @tblFiles
EXEC SP_EXECUTESQL @sqlText

SELECT @Id = MAX(Id) FROM @tblFiles

PRINT ‘Set the database to offline mode’
SET @sqlText = ‘ALTER DATABASE ‘ + @DatabaseName + ‘ SET OFFLINE’
–EXEC(@sqlText)

PRINT ‘Move the primary physical data file(s) to target location’

WHILE @Id > 0
BEGIN
SELECT @DatabaseDataFileName = FileName FROM @tblFiles WHERE Id = @Id
SET @sqlText = ‘USE ‘ + @DatabaseName + CHAR(13) + ‘GO’ + CHAR(13) + ‘ALTER DATABASE ‘ + @DatabaseName + CHAR(13) + + CHAR(9) +
‘MODIFY FILE (‘ +
‘NAME = ”’ + @DatabaseName + ”’, ‘ + CHAR(13) + + CHAR(9) +
‘FILENAME = ”’ + @TargetFileName + @DatabaseDataFileName + ”’)’
–PRINT(@sqlText)
SET @Id = @Id – 1
EXEC (@sqlText)
END

RETURN

–Move the log file to target location

SET @ParamDefinition = ‘@LogFileName NVARCHAR(255) OUTPUT, @DatabaseName NVARCHAR(255) = ‘ + @DatabaseName

SET @sqlText = ‘
SELECT @LogFileName = REVERSE(LEFT(REVERSE(physical_name), CHARINDEX(”\”, REVERSE(physical_name)) – 1))
FROM ‘+ @DatabaseName + ‘.sys.database_files
WHERE type = 1 –for log files’

EXEC SP_EXECUTESQL @sqlText, @ParamDefinition, @LogFileName = @DatabaseLogFileName OUTPUT

SET @sqlText = ‘ALTER DATABASE ‘ + @DatabaseName + CHAR(13) + + CHAR(9) +
‘MODIFY FILE (‘ +
‘NAME = ”’ + @DatabaseName + ”’, ‘ + CHAR(13) + + CHAR(9) +
‘FILENAME = ”’ + @TargetFileName + @DatabaseLogFileName + ”’)’

PRINT ‘Move the log physical data file to target location’

–PRINT(@sqlText)
EXEC (@sqlText)

PRINT ‘Bring the database to online mode’
SET @sqlText = ‘ALTER DATABASE ‘ + @DatabaseName + ‘ SET ONLINE’
–EXEC(@sqlText)

END TRY

BEGIN CATCH

DECLARE @ErrorMsg NVARCHAR(1000), @Severity INT
SELECT @ErrorMsg = ERROR_MESSAGE(), @Severity = ERROR_SEVERITY()

END CATCH
END

Advertisements

Leave a comment

Filed under Uncategorized

SSIS – Best way to extract data from 32-bit source!

Brief: In recent past I have struggled to extract data from Lotus Notes using 32-bit ODBC connection via DSN(System) on 64-bit platform. At times it works and most of the time it failed, in short it was inconsistent behaviour. So, I thought writing how I achieved a stable and efficient solution.

Existing Solution:

Packages are deployed in SSISDB(Integration Services Catalog) and run via SQL job agent. Even though in job step(Job Step->Configuration->Advance->32-bit runtime) it was configured to run on 32-bit mode, the execution was inconsistent and used to fail at times.

New Solution:

Use of DTExec.exe utility for 32-bit. There are two DTExec.exe for SSIS, be careful choosing the correct one under Program Files (x86). The 64-bit can be found under Program Files folder.

Use the following command from command prompt with run as administrator:

C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn>DTExec.exe /PROJECT “\\ServerName\Project\bin\Development\ProjectName.ispac” /Package “PackageName.dtsx”

This approach handles very efficiently the extraction of data from non-Microsoft data source(in my case Lotus Notes with 32-bit Lotus Notes ODBC connection) using 32-bit connections on a 64-bit platform.

Leave a comment

Filed under Uncategorized

SSIS Best Practices

Top 10 best practices for SSIS:

  1. Change the Design
    • Avoid Semi-Blocking and Blocking Transformations
      • Semi-Blocking
        1. Merge/Merge Join
        2. Data Mining
        3. Pivot
        4. Term Lookup
        5. Union All
      • Blocking
        1. Aggregate
        2. Fuzzy Grouping and Lookup
        3. Row Sampling
        4. Sort
        5. Term Extraction
    • No Sorting in SSIS
      • Sort is blocking transformation
      • Fall out of memory
      • SQL Server is better at Sorting
    • Set based instead or row-by-row
      • Set Theory
      • Scripts can be problematic which uses row-by-row operation
      • Avoid using Slowing Changing Dimension Task
    • Avoid foreign key constraints check
  2. Minimized logged operations
    • Package and Project Logging
    • Event Handler Logging
  3. Schedule and Distribution
    • Avoid one long running task
    • Use Balance Data Distributor(part of SQL Server 2014 Feature Pack)
  4. Use the Right Data Types
    • Try not to CAST
  5. Tune the network
    • Use Multiple concurrent Connections if possible
    • Use Multiple NIC cards
    • Optimize Package size
  6. Partition the Problem
    • Total run time is set by the largest size item
    • Try read the data in equal sizes
    • Consider table partitioning by using ALTER table by … SWITCH
    • Create file groups for Partitioned tables
    • Create a Partition Function
    • Create a Partition Scheme which uses Partition Function
    • Modify the table and corresponding index to use the new Partition structures
      • Load the new data into a staging table
      • Using ALTER table… SWITCH, move the data from staging table to an empty partition
  7. Optimize the SQL or Read
    • Drop Indexes on tables before inserting
    • Execution Plans, use SQL Stored Procedure instead of inline queries
    • Determine the best method for reading data in SSIS
  8. Understand your resources by Exploring Performance Monitor
    • CPU
    • Memory
    • I/O
  9. Baseline Source Speed
    • How fast can you read data?
    • Rows/Sec = Row Count / Time Data Flow
    • Use the appropriate drivers
  10. In memory transformation
    • Avoid Disk Swapping – Monitor with performance monitor
    • Determine appropriate buffer size
    • Count your Row size to determine buffer size
    • Analyse various SSIS performance counters

Leave a comment

Filed under Uncategorized

SELECT Nth highest Salary

–Option 1 with sub query

SELECT

*

FROM

(SELECT

Name,

Amount,

ROW_NUMBER() OVER (ORDER BY Amount DESC) AS n

FROM Playground.dbo.Salary

) AS sal

WHERE sal.n = 2

–Option 2 with subquery

SELECT

Name,

Amount

FROM Playground.dbo.Salary a

WHERE 1 = (SELECT COUNT(DISTINCT(Amount)) AS Amount

FROM Salary b

WHERE a.Amount <= b.Amount)

–a.Amount <=b.Amount (this can be used to retrieve max record)

–a.Amount >=b.Amount( this can be used to retrieve min record)

–Option 3 with CTE

WITH TopSal AS

( SELECT  Name, Amount, DENSE_RANK() OVER (ORDER BY Amount DESC) AS n FROM Playground.dbo.Salary)

SELECT * FROM TopSal WHERE n = 1

Leave a comment

Filed under Uncategorized

T-SQL Remove non-printable characters

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID (‘dbo.fn_Clean_NonPrintable_String’, ‘function’) IS NOT NULL
DROP FUNCTION dbo.fn_Clean_NonPrintable_String;

GO

CREATE FUNCTION [dbo].[fn_Clean_NonPrintable_String](
@strInput AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
/*——————————————————————————————————————————————————————————–

Returns the clean string without non-printable characters.

Example Call:
SELECT dbo.fn_Clean_NonPrintable_String(‘‭‭+61 432 351 121‬‬’)

Modifications
05/18/2017, Raja Gogoi, create function
——————————————————————————————————————————————————————————–*/
AS
BEGIN
DECLARE @iPtr AS INT
SET @iPtr = PATINDEX(‘%[^ -~0-9A-Z]%’, @strInput COLLATE LATIN1_GENERAL_BIN)
WHILE @iPtr > 0
BEGIN
SET @strInput = REPLACE(@strInput COLLATE LATIN1_GENERAL_BIN, SUBSTRING(@strInput, @iPtr, 1), ”)
SET @iPtr = PATINDEX(‘%[^ -~0-9A-Z]%’, @strInput COLLATE LATIN1_GENERAL_BIN)
END
RETURN @strInput
END

Leave a comment

Filed under Uncategorized

T-SQL: Delete duplicate rows

DELETE x FROM (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY AccountNumber)
FROM [Account]
) x
WHERE RN > 1;

Leave a comment

Filed under Uncategorized

Disable/Enable Constraints to perform DML statements


DECLARE
@table VARCHAR(100) = 'Table1'
,@catalog VARCHAR(100) = 'DB1'
,@schema VARCHAR(100) = 'dbo'
,@constraint VARCHAR(100) = ''
,@sql VARCHAR(2000) = ''
SELECT @constraint = CTU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU ON
CTU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE CTU.TABLE_NAME = @table
AND CTU.TABLE_CATALOG = @catalog
AND CTU.TABLE_SCHEMA = @schema

--SELECT @constraint

--Disable Constraints back
SET @sql = '
ALTER TABLE [' + @schema + '].[' + @table +'] NOCHECK CONSTRAINT ' + @constraint

EXEC (@sql)

--Perform DML
SET @sql = '
DELETE ['+ @schema + '].['+ @table + ']'

EXEC (@sql)

--Enable Constraints back
SET @sql = '
ALTER TABLE [' + @schema + '].[' + @table +'] WITH CHECK CHECK CONSTRAINT '+ @constraint

EXEC (@sql)

Leave a comment

Filed under Uncategorized