Remotely connecting to SQL Server 2008 Integration Services – Access is denied error.

Very annoying error and I haven’t seen clear concise instructions for getting this to work. I will attempt to do that here.

1. Open Administrative Tools > Component Services
2. Navigate to DCOM Config and open the properties for MsDtsServer100
3. Click on the Security tab
4. Add the appropriate users/groups that you need and assign their permissions to BOTH “Launch and Activation Permissions” AND “Access Permisions” (Both of whom, select Customize then Edit)
5. Click Ok to save changes and close those windows.
6. Right click on Computer, select Manage.
7. Navigate to System Tools > Local Users and Groups > Groups
8. Double click on Distributed COM Users
9. Add the users/groups you wanted to give permissions to.

Advertisement

Powershell ~ Shorten Shortcut Names

Renaming Files

Here is something I just came up with. It is a really short Powershell one-liner to remove the annoying ‘- Shortcut’ from your shortcuts.

dir “*- Shortcut*” | foreach{ren -Path $_.Name -NewName $_.Name.Replace(” – Shortcut”,””)}

It is a good idea to execute it first with -whatif in the foreach loop.

Note: Be sure to be in the desktop directory when you run this.

SQL Server Error 823 Troubleshooting and Resolution

SQL Server Error 823

If you are getting this error, chances are you are having a hardware failure on your server, or perhaps someone deleted one of the database files.

Assuming you took care of that situation:

Do not Detach the Database

The very easy fix to do is to simply restore the database using your backups. But if you don’t have a good set of backups, Paul Randal ( he and his wife Kim, are SQL GODS) has a great set of steps to try in his article here.

Here is the short story of how to get your database up and running if your transaction log was damaged.

Error 823 Resolution

Switch the database into the emergency/single-user mode:

alter database <dbname> set emergency;
go
alter database <dbname> set single_user;
go
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
go
alter database <dbname> set multi_user;
go
alter database <dbname> set online;
go

The main thing to realize here is that if the transaction log is damaged or missing that the transactions are not going to be found in the transaction log. You are going to lose any active transactions that have not been commited to the database. The real area of interest is the REPAIR_ALLOW_DATA_LOSS. This particular arguement allows you to recreate the transaction log. Do not take this command lightly as it will lose data, so please use it only as a last resort.

 

DBA Test/Development Server Best Practices

These are things I learned from a hardware failure of a test server (you might also call it a staging area). So what’s the big deal? Developers should have all their code checked into source control and nothing critical should be on there right?

Sadly no. These are things you should do on your test server to limit your exposure as a DBA.

1. Treat the server as a production server. This includes transaction log backups, backups of user accounts, SSIS, SSRS, SQL Agent jobs, and Windows Tasks.

2. Trust, but verify. Trust the developers to be following best practices, after all your job is to make the company money and so is theirs. Periodically issue correspondence with the developers to verify that nothing is running in a production manner.

3. Get a good snapshot/system image from time to time. If someone or something destroys your server, you have something to fall back upon. This is especially true when developers are creating lots of dependencies in their code to OS level libraries.

How to Map to Skydrive

This is a great article to show you how to do it and I give him credit:

http://howto.cnet.com/8301-11310_39-57347395-285/how-to-map-your-skydrive-folder-in-windows-7/

But there is a problem why would you want those files to be exposed to the public???

\\docs.live.net@SSL\IDGOESHERE\^2Public

See the end that says public? Yeah, you don’t want that, change it to:

\\docs.live.net@SSL\IDGOESHERE\^2Documents

This will save it to your documents folder on your Skydrive, much more secure in that not everyone can see your files.

Setting Up SharePoint Search Error: The Web application at could not be found.

If you are receiving this error:
The Web application at <URL> could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

Take a look in Central Administration > Operations > Alternate Access Mappings.

If there is no DNS entry for the server, replace the server name with the IP address. This will get your search capabilities working in most cases.

Here is a little background in my case that might help you. I setup a TFS server and wanted to configure the underlying WSS (Windows SharePoint Services).

Hope this helps!

How To: Review Saved Perfmon (aka Performance Monitor) Binary Log (BLG) Files

Alright so this isn’t really obvious  in Perfmon.  This is what you do to review a log file.

Step 1: Right mouse click on Performance Monitor (figure 1).

Step 2: Select Properties.Step 3: Select Source then connect to the file, database, etc. (figure 2).

Figure 1

Figure 2

Control Alt Delete in Remote Desktop

Step 1: Maximize your RDP window.

Step 2: Ctrl+Alt+Esc

Step 3: Do what you must.

More on SQL Server Denali!

This guy has a great blog article on high availability on the latest version of SQL Server. I highly recommend reading it. Unlike previous versions, you can query all the nodes and you aren’t limited to a two node configuration like before! I have another article, a very terse one, here: http://wp.me/p175D9-3o

SQL Server Denali: HADRON ROCKS. | Brent Ozar – Too Much Information | Brent Ozar – Too Much Information.