Wednesday, January 25, 2012

2012-03-07: SQL Server 2012 Virtual Launch Event

Yeah, at last we have a date! :)
http://www.sqlserverlaunch.com/ww/Home

Untrusted foreign keys in Reporting Services

This week one of my colleagues, Janos Berke mentioned a script from Brent Ozar. Since I have a quite fresh sandbox of SQL Server 2008 R2, I became curious what the takeover script will find.
This instance is on a dev box, so SSDE, SSAS, SSIS, and SSRS are installed as well.
The result wasn't shocking, however it was very interesting.
The ReportServer and ReportServerTempDB databases of SSRS contain untrusted foreign keys right after installation.

It is not a big issue, you might think. You might be right. But the fact that the result of Brent Ozar's script includes issues regarding your SSRS databases is very annoying :)
It's easy to correct the issue... Just an example:

ALTER TABLE [ReportServer].[dbo].[Catalog] WITH CHECK CHECK CONSTRAINT [FK_Catalog_ParentID]

..., etc. I hope you can fabricate the rest. (The double "CHECK" is not a typo.)

I reported this on Connect - hopefully no cleanup will be needed in future releases.

...ah, all right, here is a script which will have a result with all necessary statements :)

SELECT 'ALTER TABLE [ReportServer].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServer.sys.foreign_keys i INNER JOIN ReportServer.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServer.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
UNION ALL
SELECT 'ALTER TABLE [ReportServerTempDB].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServerTempDB.sys.foreign_keys i INNER JOIN ReportServerTempDB.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServerTempDB.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0

Monday, January 23, 2012

Analysis Server Properties A-Z: BackupDir

The official documentation says...

As the official documentation says here, the BackupDir property is "A string property that identifies the name of the directory where backup files are created by default, in the event a path is not specified as part of the the Backup command."

How and where it can be set?
In the General page of the Analysis Server Properties window. After connecting to your SSAS instance with SSMS, right-click on its node, and select Properties.

What happens if the value of this parameter changes?

Nothing immediately - except this change will be applied in the <BackupDir> tag of msmdsrv.ini. But later when you want to backup one of your databases with SSMS, you will be prompted for the backup file name:

Backup Database dialog
 
As you can see, no directory is explicitly set. It means that the backup file will be created in the folder specified in the BackupDir property. When you click the Browse button, a list will appear where you can select the location of the backup file:


Backup SSAS database - Save File As dialog box
The AllowedBrowsingFolders property controls this list, so only items defined in the Analysis Services Properties window at that property can be seen here. If you select an item here, it will be filled in the Backup file editbox:

Backup Database dialog with a directory path applied
Restart needed to apply changes:
Yes. Your changes are applied only after a restart of the Analysis Services instance.

Category:
Basic

Default Value:
None. The default value is empty.

How to set the location of the SSIS top-level folders

Look for the %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml file. Open it in any editor. By default, it 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>


You can change the "." (localhost) to any of your SQL Servers. It's especially useful when this name can't be resolved, e.g. a clustered environment :) Or, if you would like to change the location of the File System folder, just simply replace the "..\Packages" to any other location which is accessible by the Integration Services service account.
You can add, of course, additional top-level folders, if you follow the structure:

    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB-sqlserver2-1567</Name>
      <ServerName>sqlserver2,1567</ServerName>
    </Folder>

Of course, copying a <Folder> block, then changing its content means less effort :)
In this example, the number after the comma is the port (when you don't know the name of the SQL Server instance, or you don't want to use SQL Server Browser because there are only fixed ports set up). If you know the instance name, you can use the servername\instancename format as well.

After you've set up everything, save this xml file, then restart the SSIS service. Your new top-level folders will be available after then.