Thursday 20 January 2022

SQL Server Mirroring Failover / Failback Automation

 

SQL Server Mirroring Failover / Failback Automation:

 

1)      Failover/Failback Script for all databases From Reston to Chicago (or Vice Versa) DB Servers:

The below script will failover all the databases to Passive/ Mirroring Node. This will help to minimize the task of right click and perform manual failover for each database

This can only be initiated from the Principal/Active server, and the mirrored database must be synchronized (that is, when the database is in the SYNCHRONIZED state).

Performing such a fail-over, using T-SQL, comes in handy when you are installing Windows Updates and SQL Server Cumulative Updates, and you need to reboot the database server. It is recommended to fail-over the databases on your principal SQL Server to the Mirroring Server before you reboot the server.

-- Perform a fail-over for all databases in a mirroring set-up

 

        declare @databasename nvarchar(255)

        declare @alldatabases cursor

 

-- Only select principal databases (mirroring_role).

        set @alldatabases = cursor for

            select d.name

            from sys.databases d, sys.database_mirroring m

            where m.database_id = d.database_id

            and m.mirroring_role_desc = 'PRINCIPAL'

 

-- Execute the failover.

        open @alldatabases

        fetch next from @alldatabases into @databasename

        while @@FETCH_STATUS = 0

        begin

        print @databasename

        exec('alter database [' + @databasename + '] set partner failover')

        fetch next from @alldatabases into @databasename                                                                                                                                                         

end                                                                                                                                                                                                                

close @alldatabases                                                                                                                                                                                               

deallocate @alldatabases


 

2)      Step to check Active/ Principal DB in SQL Server Job:

We can add “Step1” to check Active/ Principal DB for SQL jobs on DB Servers which will check whether the DB is online/Principal every time job runs. This will only allow jobs to run Step2 (which is application job step) only on Active/Principal Server.

This step will reduce the time to Disable/Enable Jobs every time during Failover / Failback Activity.

Step Need to added in Jobs:

IF EXISTS(

SELECT  1  FROM sys.databases

WHERE state_desc = 'ONLINE'

AND collation_name IS NOT NULL

AND name = 'MirrorDBName')

BEGIN

PRINT 'Principal DB Good to Go'

END

Else

--PRINT 'EXITING GRACEFULLY';

THROW 51000, 'This is Mirror DB', 1;

 

Friday 7 January 2022

SQL Server - Choosing Between AWS EC2 vs AWS RDS

Database Solution - SQL Server Cloud Migration Strategy

The following table provides a side-by-side comparison of SQL Server features supported on Amazon RDS and Amazon EC2. Use this information to understand the differences between the two services and to choose the best approach for your use case.





Tuesday 15 December 2015

How to add Integration Services to an existing SQL Server Clustered Instance

Step 1

To add/install SSIS on an installed SQL Server Instance, we need to run setup again on each cluster node. First run SQL Server setup on your active node (Node 1). Follow all necessary steps in the setup windows. Make sure to run this setup to add SSIS on your existing instance rather than creating a new instance. Choose Integration Service on the feature selection page of the setup window. After we start the install, we will get an error because one of the rules will fail as shown below.



Step 2

When you get the above error, you cannot proceed with the install because the "Next" button will be disabled due to this failed rule.


Step 3

As we already saw that we cannot add SSIS to an existing SQL Server cluster, the solution is to run setup and skip the installation rules to install SQL Server Integration Services in an existing clustered instance.
Run the below command at the Windows command prompt to start SQL Server setup on the active node.  Make sure to run this command after changing the root directory of the command prompt to the location where you have placed the SQL Server setup files.
Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install






Once you press enter to run the command, the SQL Server product version will display on the command prompt, and an installation window named "Program Compatibility Assistant" will appear. Now click on "Run program" to proceed with this installation.


Step 4
Now follow the same process which you normally do in an installation. Again choose the existing instance to add SSIS and select Integration Services in the feature selection page which we need to install.



Now this time you can see the installation rule check passes without an error, because we skipped the installation rule process to make this installation possible.

Step 5
Here we can see the "Next" button is enabled, so click on Next to install SQL Server Reporting services on the active node.

Step 6
Once you are done with the installation on the active node (Node 1), follow the same process on each of the other nodes in the cluster.

Step 7
After SSIS installation it will by default point to Default instance, so you need to do below changes.
In the installation folder, You will find the configuration file.




For SQL 2005, by default, this path is: C:\Program Files\Microsoft SQL Server\90\DTS\Binn
For SQL 2008, this is C:\Program Files\Microsoft SQL Server\100\DTS\Binn. In either case, the name of the file is MsDtsSrvr.ini.xml. When first installed, this file will look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

Step 8
To be able to manage packages on any of those instances, you are going to have to modify your config file. To be able to manage packages on all cluster instances from any one machine, we would make modifications like I did above so that the config file will now look like this:


<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>Cluster2008r2\SQL2008r2</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

Step 9

So now, whatever machine I put that config file onto will see and be able to manage packages on those cluster machines, just as in the screenshot below, I can see the packages and manage them on those two instances. 




Tuesday 24 June 2014

Installation of SQL Server 2014

In this post, I will demonstrate the installation of SQL Server 2014 on a VMWare Workstation. We can learn how to configure and use the features of SQL Server 2014.

1)      Make sure you run the setup as “ Run as Administrator “



2)      The SQL Server Installation Center window will display. Along the left side of the window are the categories showing how the SQL Server Installation Center is organized. On the right side of the window are different actions the installer can take.


3)      Clicking on Installation on the left side will display the different installation options. For this tip, we will click on "New SQL Server stand-alone installation or add features to an existing installation". This will launch the SQL Server 2014 Setup application.



4)      The SQL Server 2014 Setup application lists the steps it will follow on the left side of the window. I am installing SQL Server 2014 Enterprise edition here.



5)      Review the license terms, click on "I accept the license terms", and then click "Next”




6)      The SQL Server 2014 Setup application will run multiple checks for rules during the installation process. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed.



7)      The setup application will also search for product updates.




8)      Another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue.



9)      On the Setup Role screen, we will click on the SQL Server Feature Installation radio button. This will allow us to install the database engine, Analysis Services, Reporting Services, Integration Services and other features of SQL Server 2014. Click on "Next " to continue.



10)  The next step is the feature selection. This allows the installer to pick and choose the features to be installed. On the right side of the window, the disk space requirements are displayed. At the bottom, the installer can choose the path for the instance root and shared feature directories. Click on "Next" to continue.



11)  Another rule check after feature selection will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue


12)  The Instance Configuration screen allows the installer to specify the name of the instance and its ID. This screen will also display other installed instances.



13)  The Server Configuration screen shows the services to be installed, the service account name and the Startup Type.


14)  On the collation Tab, If you want to change the collation of the server select from the given option. Otherwise by default it will take the collation settings.


15)  On the Server Configuration tab of the Database Engine Configuration screen, select your preferred Authentication Mode and specify your SQL Server administrators.



16)  On the Data Directories tab of the Database Engine Configuration screen, specify your preferred directories for the data root, system database, user database, user database log, temp database, temp database log, and backup. Click on "Next".



17)  On the File stream Tab, you can select enable File stream option. I am not selecting the option as of now. We can choose this option later on.


18)  On the Server Configuration tab of the Analysis Services Configuration screen, select your preferred Server Mode and specify your Analysis Services administrators.



19)  On the Data Directories tab of the Analysis Services Configuration screen, specify your preferred directories. Click on "Next".



20)  On the Reporting Services Configuration, select Install and configure. Click on "Next" to continue.



21)  We have to specify Administrator for the Distributed Relay Controller Service.


22)  Specify the Name and directories for DRC Service.


23)  Another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue.



24)  The Ready to Install screen displays all of the features and prerequisites to be installed. At this point, the installer can still go back to make changes or quit the process. Click on "Install" to begin the installation of the SQL Server 2014 components.



25)  The installation progress bar tracks the status of the installation.




26)  Upon completion of the installation, Check all the features are successfully installed and click on "Close" to exit the SQL Server 2014 Setup.



27)  After successful installation of SQL Server 2014, Check the Configuration Manager for features.






  
28)  Open SQL Server Management Studio.




29)  Expand the SQL Server and review all the components.



  
If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 

Have fun!