SQL Server SSIS : Create the SSIS Catalog which is must Before you can deploy the projects to the Integration Services

I would like to highlight that There have been some rather significant changes made to SSIS in SQL Server 2012 and One of which is project/package deployment. In SQL Server 2005 / 2008, one could easily deploy packages to the MSDB but In 2012, this has been changed. Before you can deploy anything, there has to be an integration services catalog.

Create the SSIS Catalog :

As soon as you are done with design and test packages part in SQL Server Data Tools, you can deploy the projects that contain the packages to an Integration Services server. Before you can deploy the projects to the Integration Services server, the server must contain the SSISDB catalog coz the installation program for SQL Server 2012 does not automatically create the catalog; you need to manually create the catalog by using the following instructions.

You can create the SSISDB catalog in SQL Server Management Studio.

To create the SSISDB catalog in SQL Server Management Studio:

1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4. Click Enable CLR Integration.

    The catalog uses CLR stored procedures.

5. Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted.

Note:    The stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.

6. Enter a password, and then click Ok.

The password protects the database master key that is used for encrypting the catalog data. Save the password in a secure location. It is recommended that you also back up the database master key. 

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL SERVER – Change SA account password if you forgot and can only login via SA account with the help of local Windows admin account

Story starts with a Priority 1 (P1) issue when I got call for helpdesk stating that users are claiming that they are not able to connect to production database. As soon as I logged in to database server and tried to connect to SQL Server, I found that I am no more able to login to SQL Serer with sa password I have.

It was password-related issue and it feel that sa account password has been changed during recently happened audit activity due to poor password but I was not having the updated one. My collogue who was involved in audit activity was unreachable at that time and I can't let P1 issue hold in my hand for long time . I am sure that I am not the first person who has faced this issue. There may be many more where employees who have is having only sa account password to connect to SQL Server and AD or local windows account with admin rights on Database server but not in SQL Server so resetting the password of SA is can save him/her from rebuilding SQL Server which can further lead to an outage and data loss.

Now you can use the windows account which is having admin rights on server to reset your sa account password.

 

Steps to reset sa account password in above situation:

 

1. Stop SQL Server services

2. Start SQL Server in single user mode

To start SQL Server in single user mode is very simple:

Go to SQL Server Configuration Manager and click on  SQL Server 2008 Services. Click on SQL Server instance and right click go to properties. On the Advance table enter parameter ‘-m;‘ before existing parameters in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect.

Note: Make sure that SQL Agent service is stopped before performing above mentioned steps.

 

Thanks/

 

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Merge replication fails due to timeout errors

Solution for : Merge replication fails due to timeout errors

QUESTION:

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).

Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.

You need to reduce the occurrence of these timeout errors. What should you do?

Answer:

When you synchronize data rows with a large amount of data, such as rows with LOB columns, Web synchronization can require additional memory allocation and hurt performance. This occurs when the Merge Agent generates an XML message that contains too many data rows with large amounts of data. If the Merge Agent is consuming too many resources during Web synchronization, reduce the number of rows sent in a single message in one of the following ways:

• Use the slow link agent profile for the Merge Agent.
• Decrease the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch parameters for the Merge Agent to a value of 10 or less. The default value of these parameters is 50.

Note: Merge Agent has a "slow link" profile designed for low bandwidth connections.

How to allow merge agent to use "slow link profile" :

 

Change Existing Agents:

 

Select a profile (On the General page of the Distributor Properties - <Distributor> dialog box, click Profile Defaults), and then click Change Existing Agents to specify that all existing jobs for an agent of a given type should use the selected profile. For example, if you have created a number of subscriptions to a merge publication, and you want to change the profile to specify that the Merge Agent job for each of these subscriptions should use the Slow link agent profile, select that profile, and then click Change Existing Agents.

Hope this will help you .

Brgds,

Chhavinath Mishra

Microsoft Certified IT Professional (MCITP)

SQL Server: Make sure a SQL Server backup is useable

It is Always recommended that before restoring a database, Please check whether database is corrupt or not as it will save your time for sure.

What exactly we can gain from RESTORE VERIFYONLY:

The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable. The does not do an actual restore, but reads through the file to ensure that SQL Server can read it while you are actually restoring the database.
Case 1: Backup Is Useable

restore verifyonly from Disk='E:\Daily Backup\Local\master_backup_201005030500.bak'

Output: "The backup set is valid."

Case 2 : Backup is Corrupt

restore verifyonly from Disk='E:\Daily Backup\Local\master_backup_201005030500.bak'

Output A Or B:

A) Msg 3242, Level 16, State 2, Line 1
The file on device 'D:\SQL_DBBackup\MARINE_backup_201303040300.BAK' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

B)

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Remove/Delete Articles from Transactional Replication in SQL Server 2008/R2

While working on a project ,I've come across situations where I had to remove article from replication. AS soon as you remove the articles from publication,Replication will stop replicating the changes in data from publisher to subscriber but it will not remove tables from the subscriber database, you have to do it manually after removing object from replication. Also wish to highlight that if there are dependency on tables which you would like to delete could not be dropped unless dependency is removed or dependent tables are dropped. 

Please follow below mentioned steps to remove articles from transactional replication. 

1.) On publisher server, Navigate to replication option under your SSMS and move to the publication, right click on publication and click on “Properties” : 

2.) On Publication Properties dialog box, go to “Articles” option from the menu in left hand side and uncheck the checkbox of article you want to remove from replication. click on “ OK” button. Same can be used if you would like to stop replication. For this, uncheck all check-boxes which will ensure that no data will be replicated.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the underlying table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.



All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication. 

Thanks/

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)