Schema vs a separate database
-
Hi,
I'm in the process of designing a DB architecture for an application and wanted to get some input regarding a couple of dilemnas I am facing with some of SQL Server 2005's new features.
We have the main application datasource which is owned by a user with its own schema.
We also have a multi-language component that takes care of saving, editing etc... the multi-language data in the same schema using another user
We want to add a reporting component that will use its own tables etc... to generate reports using reporting services. this reporting component will need to have access to the multi-language data. It will own its own schema using another user
Originally, for separation of duty purposes and ease of backup and administration, we have set up the following architecture:
|============|
|MAIN DATABASE|
|============|
|APPLICATION |
|MULTI_LANG |
|============|
What I'm wondering is whether to go for a separate database for the reporting component or use just another schema in the main database. Either way I will need to give access to the reporting component to the multi-language data. But I just don't know if it's better to use another database or another schema only for that purpose. In other words:
|============|
|MAIN DATABASE|
|============|
|APPLICATION |
|MULTI_LANG |
|REPORT |
|============|
Or
|============|
|MAIN DATABASE|
|============|
|APPLICATION |
|MULTI_LANG |
|============|
|============|
|REPORT DB |
|============|
|REPORT |
|============|
Does anybody have any past experience with this type of situation and can give some insights? I'm basically torn between these two. I like separating the two databases but if schemas offer the same functionality and they are on different datafiles anyway, then we could back them up separately without compromising one another. But then it would be the same database.
-
I am using it now we are using dbo for stored procs and dbo schema for tables and one database for all, however I must add the localization is just in the application layer not the database because our application may run in more than 100 countries. The customer wants other areas completed before we get to that part of the application. And you don't need to write separate reporting localization because you can just set the properties the SSRS will take care of most languages. So the answer is you may need a separate database for localization. If you need help with that post your languages and I will be glad to help. Hope this helps
Kind regards,
Gift Peddie -
Actually, I'm glad you're mentioning the SSRS part of it. I'm looking at how to handle localization in this part as well. I'm trying to figure out how SSRS handles that. Would you have a concrete example or a link to provide me for examples? The MSDN forums and articles I've read on the Microsoft website haven't really helped me yet.
Our main application is written in Java (odd mix I know but not my choice...). Static multi-language data is handled via resourcebundles. I know in .NET there is an equivalent for that (ResourceManager) but I want to see if there's a way to abstract this without having to implement the ResourceManager in my Report Server to translate this static data. That's on the application layer. However, some attributes for some objects are multi-language (city names which can be added/removed from the system etc...). I am very curious to see how this can be localized easily without having to create different reports for different languages. I think I have read somewhere that you could have a column Attribute_en, another Attribute_fr and SSRS would know based on the locale which one to fetch? If you have any ideas regarding that would be amazing, I find the documentation very lacking for explaining such requirements (or I'm not looking properly for that information).
We will most likely need a separate database for the reporting part because we want to be able to put these on separate machines and completely segregate them. We're still trying to figure everything out though. If you could backup schemas separately we would probably been okay but only by datafiles is not enough. My only concern with 2 databases is that I will have to create synonyms in DB 2 pointing to the required tables in DB 1 in order to avoid having to write FQNs.
Thanks,
Greg
-
The SSRS localization is in the properties of the base report object or the textbox properties it ask what language you want to set it and the Reporting engine takes care of it. Now you have to do the translation SSRS just localize it, that is it takes care of the rendering. I don't use the wizard so go to reports add new item and then report add any item to it then go to properties and it has more langauges defined than SQL Server because I think it gets that from Visual Studio advanced save as which looks almost the same as the SSRS localization property. Here is what I can do tomorrow I can play with the localization property and post back if there is anything you need to know. Hope this helps.
Kind regards,
Gift Peddie -
Thanks a lot, I really appreciate it. I will play with it tomorrow as well and will post back any questions I get from that. In the meantime, if you have more tricks, don't hesitate to share them here
Greg
-
You have more options in the Textbox control than the base layout which just let you set language, in the Textbox you can set Calendar, left to right which is standard or right to left as in Arabic. All these options are under international properties in the Text Box control if you access the Text Box properties through the Base layout as drop down. Hope this helps.
Kind regards,
Gift Peddie -
Thank you very much for all the help. It's helped me greatly
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply