Search This Blog

Sunday, April 22, 2018

Microsoft® SQL Server® Notes for Professionals

Tuesday, January 23, 2018

How do I manually add the SQL Server Agent to the cluster resource group?



                      I was trying to install SQL Server 2008 R2 on a Windows Server 2008 R2 Failover Cluster, but the setup did not complete successfully. Upon further investigation, the issue seems to be caused by a DNS entry that was then fixed by the systems administrator. While the SQL Server cluster resource group was successfully brought online after the DNS issue was fixed, I noticed that the SQL Server Agent was not listed as a resource type under the Other Resources section of the cluster resource group. How do I manually add the SQL Server Agent to the cluster resource group?





    Solution

Not seeing the SQL Server Agent on the Other Resources section of the SQL Server cluster resource group means that it has not been created successfully. This is usually caused by an incomplete or corrupt installation when you perform a single-node cluster installation of SQL Server. In this particular case, the single-node cluster installation failed because the virtual server name could not be registered to the DNS. You can verify this by trying to add a new resource in the clustered resource group. In the screenshot below, you do not see the SQL Server Agent in the list of available resources - only the SQL Server resource is available.

While searching for a Microsoft KB article that would provide a resolution for this issue, I saw a problem similar to mine that was posted on the MSDN Forums.
We can manually add the SQL Server Agent as a clustered resource type. But before we do this, we need to make sure that the SQAGTRES.DLL file has already been copied to the C:\WINDOWS\SYSTEM32 folder. This DLL file gets copied as part of the failover cluster installation together with the main DLL used by the SQL Server database engine - SQSRVRES.DLL. While the functions of these two Resource DLLs are beyond the scope of this tip, you can learn more from this SQL-Server-Performance.comarticle.
To fix the SQL Server Agent resource on the clustered resource group, you need to perform the following steps below:
NOTE: These steps can be performed either via the Failover Cluster Manager, cluster.exe or Windows PowerShell. Since we all have different preferences, you decide which method to use. I am including all three options where applicable. When choosing to use Windows PowerShell, make sure you import the Windows PowerShell modules, in particular, the FailoverClusters module using the Import-Module cmdlet as defined here.


Manually add the SQL Server Agent resource type to the SQL Server cluster resource groupStep 1 Create the SQL Server Agent resource type
Using cluster.exe,
cluster.exe restype "SQL Server Agent" /create /DLL:SQAGTRES.DLL


Using Windows PowerShell,
Add-ClusterResourceType "SQL Server Agent" C:\Windows\system32\SQAGTRES.DLL
 

Step 2
Add the SQL Server Agent resource to the SQL Server Cluster Resource Group.

Using the Failover Cluster Manager, right-click on the SQL Server cluster resource group, select Add a resource -> More resources ... -> A - Add SQL Server Agent
Using cluster.exe,
cluster resource "SQL Server Agent" /create /group:"SQL Server (MSSQLSERVER)" /type:"SQL Server Agent"


Using Windows PowerShell,
Add-ClusterResource -Name "SQL Server Agent" -ResourceType "SQL Server Agent" -Group "SQL Server (MSSQLSERVER)"

Step 3
Set the private properties of the SQL Server Agent resource.

We need to assign the VirtualServerName and InstanceName properties of the SQL Server Agent resource to match those of the SQL Server resource. In my environment, the VirtualServerName property is SQLCLUS and the InstanceName is MSSQLSERVERsince I am using a default instance.
Using the Failover Cluster Manager, double-click the SQL Server Agent resource to open up the Properties window. Click on the Properties tab to display the VirtualServerName and InstanceName properties. Enter the appropriate values for the properties and click OK.
Using cluster.exe,
cluster resource "SQL Server Agent" /priv VirtualServerName=SQLCLUS
cluster resource "SQL Server Agent" /priv InstanceName=MSSQLSERVER
Using Windows PowerShell,
Get-ClusterResource "SQL Server Agent" | Set-ClusterParameter VirtualServerName SQLCLUS
Get-ClusterResource "SQL Server Agent" | Set-ClusterParameter InstanceName MSSQLSERVER

Step 4
Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created.

This is the same as adding the SQL Server service as a dependency to the SQL Server Agent service in a stand-alone instance.
Using the Failover Cluster Manager, click on the Dependencies tab of the SQL Server Agent Properties dialog box and select SQL Server under the Resource drop-down list. Click OK.
Using cluster.exe,
cluster resource "SQL Server Agent" /adddep:"SQL Server"

Using Windows PowerShell,
Add-ClusterResourceDependency "SQL Server Agent" "SQL Server"



You can also verify if the SQL Server Agent resource has all of the nodes in the cluster listed as possible resource owner. Usually, if this is done correctly at the cluster resource group level, all of the resource types inherit the settings.
Using the Failover Cluster Manager, click on the Advanced Policies tab of the SQL Server Agent Properties dialog box to see the list of Possible Owners.
Using cluster.exe,
cluster resource "SQL Server Agent" /listowners"

Using Windows PowerShell,
Get-ClusterResource "SQL Server Agent" | Get-ClusterOwnerNode

Modifying SQL Server registry keys

Having an incomplete or corrupted SQL Server installation also means that there are registry keys that have not been properly written or updated. It is important to backup your registry prior to performing these tasks.
  1. Open the Registry Editor and navigate to the following registry hives.
For default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState
For a named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState
  1. Check the values of all the registry keys. If the value is greater than 1, it means that there was a failure either during the installation or configuration phase while running the setup process. In my environment, all of the registry keys have a value of 2.
           

Change all of the registry key values to 1.

    Run a Repair of the SQL Server 2008 R2 installation

After all of the ground work has been done, you can now perform a repair of the SQL Server instance. To do this, run the setup.exe from the SQL Server 2008 R2 installation media and click the Maintenance link on the left-hand side. You can then click the Repairlink to run the repair process. A more detailed procedure is defined here.
NOTE: Make sure that you run the repair process on the node that does not own the SQL Server cluster resource group, or in other words, the passive node. If you are, manually fail over the SQL Server cluster resource group to the other node before proceeding with the repair.

Bring the SQL Server Agent Resource Online

Once the repair process completes successfully, you can now bring the SQL Server Agent resource online.
Using the Failover Cluster Manager, right-click the SQL Server Agent resource and select Bring this resource online option.

Using cluster.exe,
cluster resource "SQL Server Agent" /online

Using Windows PowerShell,


Start-ClusterResource "SQL Server Agent"


That's it Now the SQL Agent is Online

Wednesday, November 16, 2016

How to Load the Data in Complex Schema using SSIS

Case Study - How to Load the Data in Complex Schema using SSIS


Scenario

  • Flat file which contains the Employee Information, and each row needs to move or insert multiple table/Schema in Parallel.
  • Schema is basically like parent and Child Architecture.
  • Parent table Primary Key will be foreign key of Child table.

Structure of the Flat File

  1. Employee Name
  2. Physical Address
  3. Digital Email Address
  4. Mobile Number
  5. Experience
  6. Occupation
  7. Basic pay
  8. PF
  9. Allowance
  10. (etc..)  

DB Schema      

Company Master is the Parent table and its Child tables are Employee Master, Salary Master and Address Master.
Employee Master is having 2 Child tables Employee Name, Occupation and Experience.
Salary Master is having 1 Child table Employee Salary Details.
Address Master is having 2 Childs Physical Address and Digital Address.
There are 2 Association tables.
  1. Salary and Employee master Association.
Primary key of Employee Master and Salary Master will be Stored in this Association table

  1. Address and Employee master Association.
Primary key of Employee Master and Address Master will be Stored in this Association table

Relationship between the tables and the data flow  


Task

Extract the Flat file and Load the data into multiple tables in parallel.

How to Achieve?

We can achieve this in 2 ways. One is using Script Component and another one is using tricky methods.

Solution – 1




--> In SSIS - DFT (Data Flow task) we have a Script Component.


Using the Script component write all the insert Script using C# or VB.net.
Configuring the Script component:-
Drag and drop the Script component into the dataflow of DFT it will show the following options.
















 Select the Destination option. Since we are going to insert the target table.









Select the connection manager and provide the Name . 















Once you configured your Source Component all the Columns will be listed in Input Column tab in Script component.

  


















Go to Script tab and select the Script language based on your option (VB or C#)
Click Edit Script to write your coding.
Once the script window is opened you can see 3 Methods.
  1. Sub PreExecute()
This method is called once, before rows begin to be processed in the data flow.

  1. Sub PostExecute()
This method is called after all the rows have passed through this component.

  1. Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
This method is called once for every row that passes through the component from Input0.
How to Acquire the Connection?
We have another public function in the Script window. Here the ADO is the Connection manager which we specified while configuring the Script component.
Refer the Second Image.
  Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

       connMgr = Me.Connections.ADO
       sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub
What to write in the 3 methods?
In the PreExecute method we need to define our Query and its Parameters. Also Refer the yellow marked query which will be provide the Auto generated ID of each row. 

In the Input0_ProcesInputRow method we need to pass the source column values to PreExecute method. Yellow marked Row represents the Source Column and Address_ID is returned the Auto generated Id which can be used for other Child tables foreign Key. 
We can use this Script component to insert all the tables when we have these kind of complex Schema’s.
Drawbacks:-
  1. Since we are using the Script component and Insert statement this will take long time to import all the records.
  2. This code will work only the ADO.Net Connection and it will not work OLEDB Connection.
  3. If we are using OLEDB Connection we need to change the .net Code accordingly.

Solution – 2

This is another method to achieve our goal which is tricky and cool method.
Once the File is loaded in to stage table. We need to generate our own RowID using the below Query
select ROW_NUMBER() OVER (ORDER BY (SELECT 1))
+(select isnull(max(EmployeeID),4000) from Employee with (nolock))  AS EmployeeID

  • Query initially select the maximum Employee id and added with the Row number of the Stage Table.
  • We need to Generate ID for all Parent ID and it be passed to Child tables Foreign Key.

Once we build our Query we need to pass as SQL Command in OLEDB Source Editor.



 


    And check the Keep identity in OLE DB Destination Editor.
 




This will load the Data into all the target tables in parallel.
Sample DFT


This will Load the data without any performance issue and it’s one of quicker method to load the data.
Drawbacks:-
  1. While this SSIS package is running we cannot insert any single record in the target table. Since the auto generated id is defined by us.

Conclusion

In One of our Project we have complex schema which is defined by Client. We approaches the 2 solutions and finally selected the second one. Since the Second solution is faster as expected.