MSSQLTREK

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable. “.

Couple of days ago we’ve seen how to fix DCOM permissions issue while connecting to SSIS remotely. See here.

In this Blog Post let’s see one more annoying error(RPC Server unavailable) we see developers complaining about, while connecting to SSIS 2008 Server from there client machines.

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable.
“.This error occurs when the computer host name does not exist, or cannot be reached.

I’ve SSIS Service running on “mirrorstand” machine and I’m trying to connect from another machine “node2”. Please note, I’ve not configured Firewall yet on my “Mirrorstand” machine(Windows Server 2008).

How to Fix this Error?
All we’ve to do is add couple of exceptions in our Firewall, basically to allow “MsDtsSrvr.exe” Program and SSIS Port(TCP 135). Let’s see how to do that in action.

Step 1: Logon to your SSIS Server and Open Firewall with Advanced Services and create a new Inbound Rule for TCP Port 135.

Step 2: Click on New Rule and select Port as shown below.

Step 3: Select TCP and enter 135 as port number as shown below.

Step 4:

Step 5:

Step 6:

Now, The most important part comes here. We’ve to add MsDtsSrvr.exe as an exception. Create a new Rule and follow the same screenshots, but You’ve to choose Program instead of Port and browse to the executable.

Typically, you should be able to locate this executable in “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” Folder as shown below.

And, my firewall Inbound Rule looks like below…

Once I added the Port and Program to my Inbound Rules on my SSIS Server, I’m able to successfully connect to SSIS(mirrorstand) from “Node2” machine as you can see below… 🙂

Hope this info helps when you get into this situation…Cheers!

Few Useful(often Ignored) DBCC Commands…

Thought of compiling all the useful DBCC Commands for DBA related to Memory Management within SQL Server. Well, I’ve seen many DBA’s unaware of the below very useful commands staying under assumption, CHECKDB is the only DBCC Command we’ve in SQL Server.

Okay! Let’s see what do SQL Server offer to DBA’s to do a diagnosis of SQL Server Memory.

DBCC DROPCLEANBUFFERS : This Command will remove all the Data Pages from the Buffer Cache. Basically, you can issue this if you want to clear your Buffer Cache.

DBCC FREEPROCCACHE: This command will remove all the execution plans from Procedure Cache.

DBCC FREESYSTEMCACHE(‘ALL’) : This command will remove all the Unused entries from all the available caches. It includes Plan cache as well!

DBCC FREESESSIONCACHE : This command will Flush the Distributed Query Connection(Linked Servers) Cache on SQL Instance.

DBCC FLUSHPROCINDB(DB_ID) : This is a pretty useful command which clears Query Plans related to a given Database.
Note: This is an Undocumented Command.

Hope this is useful! Be very cautious and understand what you are doing before issuing these Commands blindly on your Production SQL Instances to resolve an issue, especially on an Instance which is shared by Multiple applications with many Databases.

How to Move a Mirrored Database MDF and LDF Files?

Couple of days ago, I had a conversation with one of my colleagues, on how to move database files from one location to another which is has DB Mirroring enabled. In this blog post, let’s see that in action..

Let’s consider the below Scenario:

1. You’ve a Large Database(let’s say 500+ GB DB) which is participating in DB Mirroring as a ‘Principal’ Database.

2. That’s the only database sitting on this Instance, a Dedicated Instance for your Application(In other words, detaching-attaching DB will cause almost the same impact as SQL Services Restart)

3. You want to Move your MDF and LDF files to another LUN (because of space constraints or you are doing SAN Expansion or something else)

4. You do not want to Break Mirroring completely, because you do not want to re-configure everything from scratch…which takes considerable amt. of time and not acceptable to your Business, running without DR for that long period of time.

5. What is the option we have in this case?

Solution: First thing to note, we can’t detach a Database while it is participating in Database Mirroring. You’ll get the below error if you try to do so..

“The operation cannot be performed on database “Mirror_tst” because it is involved in a database mirroring session.
ALTER DATABASE statement failed.”

so what to do now..? Remember, we’ve ALTER DATABASE…..MODIFY FILE….to do this trick.

Demo:

I’ve a database called ‘Mirror_tst’ which is being mirrored from PROD Instance to DR Instance as you can see below. Also, please note the current database file locations in the below screenshot(As of now, they are in default location)

Now, Let’s update the database file(s) location in system catalogs to “C:\Backs\” folder. For this I’ll issue the below commands.

ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst,FILENAME=’C:\Backs\mirror_tst.mdf’)
GO
ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst_log, FILENAME= ‘C:\Backs\Mirror_tst_log.ldf’)
GO

and i received below messages:

The file “mirror_tst” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “mirror_tst_log” has been modified in the system catalog. The new path will be used the next time the database is started.

So, the database file locations were modified in system catalog, all we have to do now is Stop SQL Service, move the physical files and start SQL Services. Let me do that!

I issued SHUTDOWN WITH NOWAIT on my PRINCIPAL Server as you can see below.( Be very cautious with this statement, understand what exactly you are doing before running this statement on your Prod Instance, believe me guys, Running this single statement unknowingly might cost you, loosing your Job!!..)

As you can see SQL is stopped and now, I moved the Physical files to ‘C:\Backs” folder as you can see below.

Now, I’m starting SQL Services and let’s see what happens to our database and more importantly to our Mirroring Status, keeping my fingers crossed 🙂

Once Restarted, the SQL Server was happy to bring up this Mirrored Database and our Mirroring Process is running happily without any issues as you can see below:) 🙂 Notice Physical files new location and the Mirroring Status now. Everything is pretty Normal:)

Hope this post helps,if you get into same scenario….Cheers!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied

In this Blog post let’s see how to fix DCOM Permissions issue for an user who receives below error when trying to connect to “SSIS” Remotely from a remote machine.

The Error Message you’ll receive when you are not granted required DCOM permissions is..

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied.
“.

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.

Note: The SSIS Server which I’m trying to connect is “MirrorStand”. The login which I’m trying to connect is ‘SREE\HR1″

So, how to fix this Error?

Solution:

Step 1: Log on to SSIS Server and Go to Component Services in Administrative Tools as shown below.

Step2: Navigate to DCOM Config ->MsDtsServer100 as shown below.

Step 3: Right Click on MsDtsServer100 and go to Properties and navigate to “Security” tab as shown below.

Step 4: Click “EDIT” beside all the three Options(Launch and Activation Permissions, Access Permissions and Configuration Permissions) shown in the above screenshot…

Once you click on ‘Edit’ button you will get the below screen where you’ve to add the required login as shown below.

Once adding in DCOM – component services is completed, then navigate to “Users and Groups” in computer Management and add the login in “Distributed Com Users” Windows Group as shown below.

Once you are done with adding the above shown DCOM permissions, you have to Restart SSIS Service. Then the login(user) should be able to connect to SSIS remotely.

As you can see below in the screenshot, I’m able to connect to SSIS Remotely (from a different machine, “Node1” in my case ) as ‘Hr1’ Login.

Tadaaaaaaaaaaaaaaa……..We made it 🙂

Hope this Helps!

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Today I was setting up a Linked Server from one SQL Server 2008R2 Instance to another SQL Server 2005 Instance with one of the Databases as a Catalog and I was able to create the Linked Server and Tested it successfully, but the Developer was getting an annoying Error as shown below…

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

What does it mean? It is complaining about a missing permission from Resource Database, which really can’t be touched by any means in terms of Permissions from our SSMS. ( Remember, Resource DB can be accessed just from File System as mdf and ldf files from your Binaries Folder)

So, how to fix this annoying Error?

My Scenario:

1. I’ve a created a Linked Server using “Microsoft OLEDB Provider for SQL Server” as Provider by choosing Server type as Other Provider instead of going with SQL Server. Don’t ask me why 😀

2. I’ve logged in as a user which is not a SysAdmin and when i try to expand the Catalogs from Linked Server I got this below error message, as you can see in below Screenshot.