Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell

As a DBA we all face the challenge of migration databases from one servers to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects. 

I have written a detailed article here. 

Enjoy ! 

SQL Server 2016 Express LocalDB

As Microsoft SQL Server 2016 comes with a lot of new and exciting features for SQL Server Database Administrators, the developers do not need to stay away. 
Microsoft SQL Server 2016 comes with the exciting new LocalDB feature.Its light, easy to install and no management required. So, easy to use with limitations as of SQL Express but no management required.
Microsoft SQL Server 2016 Express LocalDB is an execution mode of SQL Server Express targeted to program developers. LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once LocalDB is installed, developers initiate a connection by using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex or time consuming configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server. An instance of SQL Server Express LocalDB is managed by using theSqlLocalDB.exe utility. SQL Server Express LocalDB should be used in place of the SQL Server Express user instance feature which is deprecated.

Reference : https://msdn.microsoft.com/en-us/library/hh510202.aspx?f=255&MSPPError=-2147217396

Generate Publication Stored Procedure in SQL Server 2014

In Transaction Replication at times the Subscription Stored Procedures (inset, update or deleted) get out of sync with Publication. 
To get around that problem you need to generate the script of Publication Stored Procedure. 
And execute (alter) them on Subscription Databases. 


First You need to update the output limit of you SQL Server Management Studio. 
As follow : 

Secondly , Change the output to Text as follow : 

Then Execute the stored procedure on your published databases and copy the output and execute it on your subscriber database.


use PublicationDB
GO

EXEC sp_scriptpublicationcustomprocs 'PublicationName'



There is also a utility by Code Project : here

Get current running Job Schedule Id or Name in SQL Server

Here is a simple TSQL code that gives you the Schedule Name & ID which invoked he current job execution. 



DECLARE @Schedule_Name_ID nVARCHAR(1000)
DECLARE @Job_ID nVARCHAR(100)
DECLARE @State INT
DECLARE @Job_Name nVARCHAR(1000)

SET @Job_Name = 'Your Job Name Goes Here'

SELECT @Job_ID = job_id
FROM msdb.dbo.SysJobs
WHERE name = @Job_Name

IF(@Job_ID IS NOT NULL)
BEGIN


CREATE TABLE #job_current_state
(
 Job_ID UNIQUEIDENTIFIER,
 Last_Run_Date INT,
 Last_Run_Time INT,
 Next_Run_Date INT,
 Next_Run_Time INT,
 Next_Run_Schedule_ID INT,
 Requested_To_Run INT,
 Request_Source INT,
 Request_Source_ID VARCHAR(100),
 Running INT,
 Current_Step INT,
 Current_Retry_Attempt INT,
 State INT
)
INSERT  INTO #job_current_state
EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage
 
SELECT  @Schedule_Name_ID = Request_Source_ID ,@State = State
FROM    #job_current_state
where job_id = @Job_ID

SELECT ISNULL(@Schedule_Name_ID,'NA') Schedule_Name_ID
,CASE @State WHEN 1 THEN 'Running' ELSE 'Stopped' END State

DROP TABLE #job_current_state

END

ELSE

BEGIN

SELECT 'Job does not exist' output

END 

Execute a command for a batch in Windows 2008 Server

ForFiles

This is a command in windows 20008 for applying specified command on each file for a batch. 

For example if want to delete files older than a specified time period then this command will help me accomplishing this task. 

This is a very useful and easy to understand command. 

Command for an example is given below : 

The mentioned below command will delete files older than 15 days in the folder "D:\YOURFOLDERNAME" and any sub directories in it. 

forfiles /p d:\yourfoldername /s /m *.txt /c "cmd /c del @file" /d -15

forfiles --command name

/p -- specifies that next argument is path 

/s -- specifies that sub directories should be scanned

/m -- file type or search criteria 

/c -- actual command which will be executed for each file

"cmd /c" and "@file" are default values for the argument /c

"cmd /c  yourcommand @file"

/d --specifies number of days 

Further details can referenced on mentioned below technet link. 

http://technet.microsoft.com/en-us/library/cc753551(v=ws.10)

Thanks to TechNet.

Atlantis Schema Inspector - by "Atlantis Interactive"

Another handy tool to compare schema in SQL Server.

SQL Server Schema Comparison and Scripting Tool.

Atlantis Schema Inspector is an incredibly fast, flexible and complete (and free) SQL Server synchronization and SQL Script tool - use it to get your deployments right first time, every time.

Schema Inspector offers an easy and fast way to compare and synchronize schema elements of SQL Server databases whether live, test, snapshots or partial databases. Any changes can be checked speedily and easily without errors. It can save your development team lots of time and help you achieve your project delivery accurately and on time. Schema Inspector works in tandem with our Data Inspector and both combine with our SQL Everywhere to make a uniquely integrated “one stop” solution for all SQL developers and administrators alike.

You can confidently use Schema Inspector and Data Inspector to save hours of development time whether developing new SQL Server databases or migrating old systems over to new. The savings in development time will make a significant contribution to the ensuring your project will deliver solutions to your users on-time and on-budget. SQL Script can be a notoriously difficult way in which to perform SQL Server synchronization - Schema Inspector relieves the difficulties associated with your deployments.

Thanks to Atlantis Interactive UK Ltd.

Download from the link mentioned below : -

http://www.atlantis-interactive.co.uk/products/schemainspector/default.aspx

Please consider donating them. 

SQL Load Generator by CodePlex

A very handy tool to generate a good load, a must have tool. 

SQL Load Generator is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings. SLG was developed using C# 3.5.

Here’s a summary of the features:

Runs multiple queries against SQL Server. You can add as many as you like.

Each query can be either a SQL User or Domain User.

You can specify an Application name for the connection.

You can specify the new of concurrent threads to use for each query.

You can start all queries, stop all queries, remove all queries.

There is logging (you can toggle on and off… it isn’t precisely thread safe, and can cause crashes when there are lots of failures on multiple threads) for failed queries.

You can set all the defaults on a per user basis, and persist them.

Each query has a # of Runs and a # of Fails counter. You can use the ‘Reset Counters’ feature to reset the total counts (not the per query counts).

You can save your settings via the ‘Options’ menu. You can add default items to the different dropdowns, provide default query settings, change the log locations, etc. You can also modify the stock connection string… though keep in mind some of the settings (particularly ‘pooling=false’ will affect the way the application works… namely, the connections to SQL Server won’t be closed). 

Download from the link mentioned below :  

http://sqlloadgenerator.codeplex.com/

Thanks to Pinal Dev who tweeted this. 

http://blog.sqlauthority.com

http://sqlloadgenerator.codeplex.com/

Calculate difference of Time only between two dates while discarding difference of dates in SQL Server/ extract time and calculate difference between two dates in SQL Server

DECLARE @DateTimeOne DATETIME
DECLARE @DateTimeTwo DATETIME

SET @DateTimeOne = '1900-01-01 10:41:09.250'
SET @DateTimeTwo = GETDATE()


--hour = hh
--minute = mi, n
--second = ss, s
--millisecond = ms


SELECT  DATEDIFF(mi,SUBSTRING(CONVERT(VARCHAR,@DateTimeOne, 121), 12, 12)
,SUBSTRING(CONVERT(VARCHAR,@DateTimeTwo, 121), 12, 12) ) MinuteDifferenceinDate

Working with Email Address in SQL Server ( Validate Email Address )

at times working with email addresses in sql server becomes difficult so
here is a UDF you can use to validate the email address

It will validate mentioned below list

No Spaces are allowed
email cant start with '@'
email cant end at '.'
@must be in email and only once
domain name should be at the end and must be at least two characters
email cant habe patterns like '..' or '.@'

CREATE FUNCTION [dbo].[validateEmailAddress]
(
@EmailAddress nVARCHAR(4000)
)
RETURNS TINYINT AS
BEGIN


DECLARE @Result TINYINT
SELECT @Result =
CASE WHEN
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0
AND LEFT(LTRIM(@EmailAddress),1) <> '@'
AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1
AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3
AND (CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)
THEN 1 ELSE  0 END

RETURN @Result

END

Example:- 

it will return 1 for correct email address and 0 for incorrect 

:

SELECT  dbo.validateEmailAddress('myemail@mydomain.com')  -- Correct

SELECT  dbo.validateEmailAddress('@myemail@m  domain.com')  -- Incorrect 

Best Practices for using DATETIME column in sql server 2005 and 2008

Best Practices for using DATETIME column in sql server 2005 and 2008

In production environment we have seen that there are certain times when you make a good query by applying best practices but you don’t get good results at the time of sorting.

This might happen because of DATETIME columns which are mostly used for sorting purpose and indexes for these columns do not give you appropriate results.

Mentioned below is tip for using DATETIME column which can enhance the speed of your query while maintaining same number of indexes.

This technique requires mentioned below changes in your table: - 

1. One Persisted Computed Column (BIGINT) which will store DATETIME in integer format by removing special symbols from DATETIME
2. This computed column will be added in the covering index
3. Query will use this column instead of DATETIME column

Example :

/* Create Sample Table */

CREATE TABLE [dbo].[MyTable](

      [C1] [int] IDENTITY(1,1) NOT NULL,

      [C2] [DATETIME] NULL,

 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED

(

      [C1] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/* Data Insert */

SET IDENTITY_INSERT [dbo].[MyTable] ON

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (1, CAST(0x00009E5E00000000 AS DATETIME))

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (2, CAST(0x00009E5F00000000 AS DATETIME))

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (3, CAST(0x00009E6000000000 AS DATETIME))

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (4, CAST(0x00009E6100000000 AS DATETIME))

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (5, CAST(0x00009E7D00000000 AS DATETIME))

INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (6, CAST(0x00009E7E00000000 AS DATETIME))

SET IDENTITY_INSERT [dbo].[MyTable] OFF

/* Query */

DECLARE @D1 DATETIME

DECLARE @D2 DATETIME

SET         @D1 = GETDATE()-10

SET         @D2 = GETDATE()

SELECT      C1

            ,C2

FROM  MyTable

WHERE C2 BETWEEN @D1 AND @D2

/* Change in Table Add New Column that will store DATETIME in Integer Format */

ALTER TABLE MyTable

ADD [C3]  AS (CONVERT([bigint],CONVERT([varchar],[C2],(112))+replace(CONVERT([varchar],[C2],(114)),':',''),0)) PERSISTED

/* Existing Index for query */

CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable

(

      [C2] ASC,

      [C1] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

 IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/* Add New Column to Existing Index for query */

CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable

(

      [C3] ASC,

      [C2] ASC,

      [C1] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/*Updated Query using Integer Column instead of DATETIME having same result set*/

DECLARE @D1 BIGINT

DECLARE @D2 BIGINT

SET         @D1 = (CONVERT([bigint],CONVERT([varchar],GETDATE()-10,(112))+replace(CONVERT([varchar],GETDATE()-10,(114)),':',''),0))

SET         @D2 = (CONVERT([bigint],CONVERT([varchar],GETDATE(),(112))+replace(CONVERT([varchar],GETDATE(),(114)),':',''),0))

SELECT      C1

            ,C2

FROM  MyTable

WHERE C3 BETWEEN @D1 AND @D2

The above query will now use the new updated index and will be much faster as it will get records by using an integer column instead of DATETIME column.