Best strategy for multiple sites

I have a client that has a central database. Users are dispersed at regional
offices, and should only have access to data pertaining to their own office.
I'm trying to figure out the best way to secure data access for each group
of users. I thought of maintaining a table of users and the regional office
they belong to. Then a UDF would only return the appropriate data. This
means doing everything at the database level, regardless of the front end
(in this case, SSRS).
Another strategy would be to do all the filtering within SSRS, using the
user global variable, and filter the data dynamically based on the user
value (with a query expression). One thing I don't like about this approach
is that any other front end (web or Windows based) could access data without
filtering on a regional site basis.
Any thoughts?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comIt looks to me like you are currently planning to have a one to one match
between SQL Server logins and users. This can be a real pain. Also, you lose
the chance for connection pooling. For connection pooling to work the
connection must be exactly the same. If SQL Server can be run in mixed mode
I suggest creating a read only user that is used just for reporting. Then
when you have stored procedure you just have to give this execution rights.
Then combine that with using the user global variable. Don't filter it
locally, that will require a lot more data to be processed and you are much
better off to limit the amount of data coming to RS. Instead you can have a
hidden parameter that takes the user variable does a query and uses the
first aggregate to return the office. Then for all queries that need the
office you then pass that to the query.
Another possibility is you have a database per office. Then create views to
the database that has the data. Some views are one to one, others use the
appropriate office. Then use the user in an expression for the datasource.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23vUJ5XpNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>I have a client that has a central database. Users are dispersed at
>regional offices, and should only have access to data pertaining to their
>own office. I'm trying to figure out the best way to secure data access for
>each group of users. I thought of maintaining a table of users and the
>regional office they belong to. Then a UDF would only return the
>appropriate data. This means doing everything at the database level,
>regardless of the front end (in this case, SSRS).
> Another strategy would be to do all the filtering within SSRS, using the
> user global variable, and filter the data dynamically based on the user
> value (with a query expression). One thing I don't like about this
> approach is that any other front end (web or Windows based) could access
> data without filtering on a regional site basis.
> Any thoughts?
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Best Practice guidelines

Hi All,

We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.

I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.

What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.

I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .

Many thanks in advance

Richard R

I would say depending on the features you plan to use, Books Online s your best friend.

Let us know if that doesnt help you much and also let us know what specific feature areas you are looking at.

|||

Hi,

BOL is pretty good at describing the process, but doesn't list any caveats. It may of course be that there aren't any - but that would be unusual!

The main question I supppose is, can I just get on and design the system as if it were stand-alone, then build the replication parts afterwards?

I expect I will need some custom logic for reconcilliation, as there has to be a log for tracking part movements, and it is likely that users will synchronise their laptops in a different order to them physically moving the parts, thus generating gaps in the log that need to be filled, as well as there local copy of the data not reflecting the physical reality when they come to move parts.

Is there a best practice for doing this sort of thing without bothering the users?

Thanks for your help,

Richard

Besides Replication, what other alternatives to sync data?

I have 3 SQL databases: A, B and C. A and B are branch databases while C is the central (HQ) database. A & B will be constantly updated while C will updated occasionally and usually will be used for reporting/data browsing. Even though A & B are from different branch, they do have share some data records.

Every day, I will have to sync A & B's new/updated data to C and C to A/B. I know I can use Replication but I can't. You see, A & B are located in our own branch server while C was hosted on a third-party server. The webserver provider for C would not allow me to do any Replication on C.

What other alternatives that I can use to synchronise all 3?

DTS? XML transfer? It looks as if I have no choice but to write my own sync scripts? :((

Anyone has encountered similar situations? Any recommended SQL Tool programs?

Please help. I'm at a loss of what Im going to do.You can use Log Shipping but it is available in the Enterprise edition and the Developer edition which is Enterprise edition for five users only. Run a search for configuration info in the BOL(Books online). Hope this helps.

Kind regards,
Gift Peddie

benefits/disadvantages of activex/sql-dmo

i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the c
ommon conflicts i expect to occur and everything works ok.
Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people more
experienced than me.
Thanks for your help
SQL DMO is what the replication wizards use. Under the covers it runs replication stored procedures.
Think of the Active
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James P" wrote:

> i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the
common conflicts i expect to occur and everything works ok.
> Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
> Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
> Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people mo
re experienced than me.
> Thanks for your help
>
|||sorry that last message was send prematurely.
Think of the ActiveX controls as a lightweight version of SQL DMO. Windows Synchronization Manager uses the ActiveX controls.
Here is a brief rundown of the differences. BTW - I only use SQL DMO, although its more complex to code with, it is more feature rich.
1) If you are building publications, you must use SQL-DMO. You cannot build publications or push subscriptions with ActiveX replication controls.
2) The ActiveX replication controls' functionality is limited to copying subscription databases (but not attaching them), managing the Snapshot and Distribution Agents, creating pull subscriptions, and reinitializing subscriptions.
3) Despite their limitations, the ActiveX replication controls have proven to be far more popular than SQL-DMO is as they contain only three classes, and are simpler to work with .
4) you can't control the ActiveX agents through the agents folder in EM.
To answer your specific question regarding priority in SQL DMO its the priority property of the MergePublication class, in ActiveX its the SubscriptionPriority and SubscriptionPriorityType of the SQLMerge class.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James P" wrote:

> i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the
common conflicts i expect to occur and everything works ok.
> Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
> Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
> Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people mo
re experienced than me.
> Thanks for your help
>