Is this considered scale-out?

I picked back up an effort to get our reporting services DR scenario in place and am having some issues. Our corporate approach for production databases is to back-up the database to the local server disk, copy the back-up files to a remote office and restore the databases on remote DR servers. This process is scripted to run overnight so in the case of failure of the production environment we lose at most one day of work.

Now, I would like to set up a parallel scenario for Reporting Services and started with reading a bit of documentation. I read "Moving a Report Server Database to Another Computer" and "Backing Up and Restoring Encryption Keys" and then set to work.

I have two instances of SQL Server 2005 Standard installed as the default instance on two servers, PROD and DR. I backed up the encryption key on PROD and restored it on DR using the Reporting Services Configuration Manager. Then I backed up the PROD/ReportServer and PROD/ReportServerTempDB databases and restored the databases to the DR instance. Next, I connected the DR Reporting Services instance to the newly restored databases and clicked 'Apply'.

I get this warning:

"Setting Connection Info for Reporting Server

You specified a connection to a report server database that contains encryption keys for another report server. If you are configuring a scale-out deployment, that feature is not supported by this edition of Reporting Services. If you want to use this report server database with the currecnt report server instance, remove the existing encryption keys first."

And as you would expect navigating to the DR web application tells me "The report server installation is not initialized."

So, a couple questions?

1) Would this be considered a scale-out installation?
2) Did I miss some configuration step?
3) If I want to continue down this DR scenario do I have to upgrade to Enterprise edition?
4) Are there any suggested DR approaches for Reporting Services 2005?

Thanks in advance for you help.

Calvin

It seems you do not need a scale-out installation. The problem is after you set db connection from PROD to DR, their is an extra key in the RS database that makes RS think you are doing scale-out. Check the Keys table in your ReportServer database on DR. There could be two reasons for this to happen:

1) You configured report server on DR to use RS databases , so that the Keys table contains an entry for DR. Basically you need to decide if you want to access the backup RS db from PROD or DR. You can't access RS from both, for it is a scale-out scenario. Remove the entry you don't need.

2) For some reason there was an entry for PROD that is left over in Keys table, and you get 2 PROD entries. This makes the RS thinks you are doing scale-out. Simply remove the one that is invalid.

|||

Thanks for your response.

1) When I click the "Change" button on the Encryption Key tab, I see there are two servers listed. So, I believe there are two keys in the database but I don't believe it is as simple as just removing the key I don't need. I want to access the backup RS database from DR but the DataSource connection strings and such are encrypted using the key from PROD. If not by restoring the key using the Configuration Manager, how can I make this happen? Will Reporting Services throw a fit if I manually update the Keys table in the restored RS database in DR to make it use the encryption key from PROD but the instance information from DR? Is this even possible?

2) I believe the two keys are present because I told DR reporting services to "Connect" to the RS database which contained a key from PROD.

Thanks again,

Calvin

|||

I tried it again this morning and got it working. I believe the sequencing is where I went wrong. I'll have to do some testing to figure out what the correct sequence is but at least now I know it does work.

Thanks again,

Calvin

|||When you backup the key on PROD, the key is decrypted using the machine key of PROD and then encrypted using the password you specify. When you restore the key DR, the key is decyrpted using the password and reencyrpted using the machine key of DR. You see the key is always in encrypted form, and is preserved when you move servers.

Is there such a thing as too many relationship?

Hi,

I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.

It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields like customer_id
or product_id.

But should I do that?

My question is: Is there such a thing as too many relationships? Can
I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected and simply use
JOINS in my business code?

Thanks,
HC>> I have a corporate database with about 60 different tables that
spans manufacturing, accounting, marketing, etc. <<

That is not that big for a corporate RDBMS ..

>> It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields [sic] customer_id
or product_id. But should I do that? <<

What do you mean by "establish a relationship"? Build a relationship
table among all the entities in the model?

>> My question is: Is there such a thing as too many relationships?
Can I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected [sic] and
simply use JOINS in my business code? <<

Conntected? You mean like in a network database with pointer chains?
You even talk about fields, not columns. Things in SQL are
referenced.

Yes, you need to get all of the business rules in your model. The
more you can enforce them with DRI actions and CHECK() constraints,
the better for you and the easier for the programmers that follow.
Otherwise, you data model is incomplete.|||jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0408131746.3edd63bc@.posting.google.com>...
> >> I have a corporate database with about 60 different tables that
> spans manufacturing, accounting, marketing, etc. <<
> That is not that big for a corporate RDBMS ..
> >> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields [sic] customer_id
> or product_id. But should I do that? <<
> What do you mean by "establish a relationship"? Build a relationship
> table among all the entities in the model?
> >> My question is: Is there such a thing as too many relationships?
> Can I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected [sic] and
> simply use JOINS in my business code? <<
> Conntected? You mean like in a network database with pointer chains?
> You even talk about fields, not columns. Things in SQL are
> referenced.
> Yes, you need to get all of the business rules in your model. The
> more you can enforce them with DRI actions and CHECK() constraints,
> the better for you and the easier for the programmers that follow.
> Otherwise, you data model is incomplete.

CELKO,

What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database?

Thanks.|||>> What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database? <<

Auxiliary tables, such as the calendar, would not be referenced by
another table.

Working tables used to scrub data before it goes into the schema would
not be referenced by another table. They might have few if any
constraints, so the raw data could be inspected.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It is possible to have 'too many' relationships... though I doubt that
will be an issue for you. In other words, as everyone knows DRI exists
to help ensure data integrity. It also servers the purpose, as
intimated by Celko, of showing relations between entities...

However, each CHECK/FK creates a bit of overhead. That overhead can
end up being noticeable on VERY large tables (in the GBs and tens of
millions of rows). So there's a fine balance between optimizing
performance in some OLTP environments and keeping data clean/intact.
Good indexing can go a long way to keep all of this in check.

Case in point on the TOO MANY relationships (given all the stuff I
blabbed about above concerning costs) some over-zealous architects
will do something dumb like have an order items table. In that table
you'd normally have a FK for orderID... and for the itemID (for each
item)... but you probably wouldn't need a customerID in that table,
etc... )

Moral of the story. More is usually better. Just don't over do it.

--Mike

harris_cohen@.yahoo.com (H Cohen) wrote in message news:<1545331c.0408131453.477b3872@.posting.google.com>...
> Hi,
> I have a corporate database with about 60 different tables that spans
> manufacturing, accounting, marketing, etc.
> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields like customer_id
> or product_id.
> But should I do that?
> My question is: Is there such a thing as too many relationships? Can
> I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected and simply use
> JOINS in my business code?
> Thanks,
> HC