Create tables schema to another database using SMO

Create tables schema to another database using SMO.

I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Collector;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Collections.Generic;
#endregion

namespace ST_ab378c7f30204c94a2f49a2b0f7ac500
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            try
            {
                string TableName;
                TableName = (Dts.Variables["User::TableName"].Value.ToString());
                Server server = new Server();
                Database source_database = new Database();
                Database destination_database = new Database();
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "reliancesp";
                server.ConnectionContext.ServerInstance = "User8\\SQL";
                source_database = server.Databases["Adventureworks2012"];
                destination_database = server.Databases["ReceiverDB"];

                Table table = source_database.Tables[TableName];
                string tables = table.Name.ToString();

                //Define properties in scriptingOptions
                ScriptingOptions scriptingOptions = new ScriptingOptions();
                scriptingOptions.ClusteredIndexes = true;
                scriptingOptions.NonClusteredIndexes = true;
                scriptingOptions.Default = true;
                scriptingOptions.DriAll = true;
                scriptingOptions.Indexes = true;
                scriptingOptions.IncludeHeaders = true;
                scriptingOptions.ScriptSchema = true;
                scriptingOptions.ScriptDrops = false;
                scriptingOptions.Triggers = true;

                StringCollection result = table.Script(scriptingOptions);

                var script = "";
                foreach (var line in result)
                {
                    script += line + "\r\n";
                }

                System.IO.StreamWriter fs = System.IO.File.CreateText(@"D:\data\" + TableName + ".sql");
                fs.Write(script);
                fs.Close();
                destination_database.ExecuteNonQuery(script);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Script to Shrink Database

Script to Shrink Database.

You need to use Database Console Commands of SQL Server to get back the free space.

DBCC ShrinkDatabase : Shrink all files from database.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, 10)
Go

It will shrink 90% free space and remains 10% free for database files.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, TruncateOnly)
Go

It will shrink data and log files to the last allocated extent, while you use TruncateOnly the percentage parameter will be ignored.

DBCC ShrinkFile : Shrink only single file.

You have a choice to which data file or log file you want to shrink.

–> “AdventureWorks_MDF” and “AdventureWorks_LDF” is a logical name of data files and log files.


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_MDF,10)
Go


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_LDF,10)
Go

Regards,
Nirav Gajjar

SQL Server Mirroring with Certificate

SQL Server Mirroring with Certificate.

Step1 : Take a backup of Database and then backup of log as well, of the database which you want mirrored.
On Principal Server


USE [master]
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.TRN'

Step2 : Copy this both database and log backup from principal server to mirroring server and restore it in specific drive.

On Mirroring Server


USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.TRN' WITH  FILE = 1,  NORECOVERY

Step3 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the principal server. Copy certificate backup to mirroring server.

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO

Step4 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the mirroring server. Copy certificate backup to principal server.

On Mirrored Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://PrincipalServerName.DomainName:7024';
GO

On Principal Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://MirringServer.DomainName:7024';
GO

To Failover Partner

ALTER DATABASE AdventureWorks2012
    SET PARTNER FAILOVER;

To Stop the mirroring.

--Run below script on Principal server
ALTER DATABASE AdventureWorks2012
    SET PARTNER OFF;
--Run below script on Mirroring server
Restore Database AdventureWorks2012 With NoRecovery

Notes:
1) Windows user must have an access to both server sharing folder.
2) Port number 7024 must me exceptional port number in firewall.

Enable Windows Server Failover Clustering (WSFC)

Enable Windows Server Failover Clustering (WSFC).

Step1 :  On the server manager window click on “Add Features”.

WSFC1

Step2 : On the Add Feature Wizard select “Failover Clusterintg” and click on Next.WSFC2

Step3 : Confirm and click on Install.

WSFC3

Step4 : Check installed feature and click “Close” button to exit.

WSFC4

Thanks & Regards,

Nirav Gajjar

Identity reseed in SQL Server 2012

Identity reseed in SQL Server 2012

In SQL Server 2012, there is one issue regarding Identity column.

I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service.

You can find gaps in identity while you restart your sql server service, restart of server or failover.

Please review below code to try this.

USE ADVENTUREWORKS2012
GO
CREATE TABLE TestIdentJump
    (
         Id   INT IDENTITY (1, 1),
         Name VARCHAR(20)
    )
INSERT INTO TestIdentJump VALUES     ('testing1')
GO 5

SELECT *
FROM   TestIdentJump

IdentJump

Now restart your sql server service and run below code again and see the result.

INSERT INTO TestIdentJump VALUES     ('AfterRestart')
SELECT *
FROM   TestIdentJump

IdentJump1

Have you seen SQL server has started Identity column from 1002 instead of 6.

To resolve the problem there is two options

1) Run the checkpoint before you restart the sql server and check again.

This technique will solve the problem of jump in identity.

This technique has limitations, this will helps you out only in manual failover.

2) We need to open one trace flag to avoid this issue when server crisis and we don’t have a chance to run checkpoint.

Need to Open trace flag -t272

Right click on sql server service and click on property.

Select “Startup Parameters” tab.

Specify -t272 in textbox an click on add button and restart the service.

IdentJump3

Note : You need to this activity on principle server as well as mirror server.

Thanks & Regards,

Nirav Gajjar

Process Element XML and Attribute XML in single node

Process Element XML and Attribute XML in single node.


DECLARE @T VARCHAR(400)
SET @T = '<Log DeviceId="MainDevice001">
  <Info UId="100000" Date="05/05/2014">
    <Err>Error1</Err>
  </Info>
  <Info UId="100001" Date="05/05/2014">
    <Err>Error2</Err>
  </Info>
</Log>'
DECLARE @Errorlogsxml AS XML
SET @Errorlogsxml = Cast(@T AS XML)
SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid,
       X.Deviceid AS Deviceid,
       TAB1.COL1.value('@Date', 'DATETIME') AS Date,
       TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error
FROM   (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid
        FROM   @Errorlogsxml.nodes('/Log') AS Tab(COL)) X
       CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)