Replication - is this the best way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Replication - is this the best way to do this?

Author

Topic

TPP2010
Starting Member

1 Post

Posted - 2010-05-29 : 03:27:19

I'm not a DBA. I'm involved in a project at work and I would like a second opinion on some of the advice that we've been given.

We have a SQL Server database containing approx 80,000 records. The records belong to 20 different groups within the organisation. Think of it as a directory containing 20 folders, with each folder 'belonging' to a different group. We want to set it up so that each group can only see its records. We would also like one additional group to see every record.

We have been told that the best\only way to do this would be to have all of the records in one database for the team that need to see everything.

We have been told that we should then replicate the database by the number of groups (20), with each replicated database containing only the records for a particular group. We have been told to replicate so we catch any new or amended records.

Basically, I'd like to know what is involved in replication. My understanding is that SQL 2005 has replication built in. Is this correct? If so, is it easy to setup? Do we effectively setup each replication job and then just let it run each night? If not, what is involved in doing this? Do we need programmers etc?

I should also point out that everything will be on the one server, the main database and all of the replicated 'copies".

Any advice or assistance would be appreciated

Regards

TPP2010

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 05:07:48

Yes, replication is built in. Dunno about "replicating to same server" though.

However, you should consider that replication adds a significant level of complexity. Things break (disk full, a scheduled task gets turned off and then no-one remembers to turn it back on, and so on which add to cost and hassle). If you change the database (add another column, or table, then that all has to be rolled out to the other databases - more opportunity for cock-up, more cost, more testing, more hassle.)

Of course all that is fine & dandy if you NEED replication

I'm not sure that Replication is the best way (my hesitation is only that there may be more to it than you have described - normally I'm very outspoken about advice that people have had if I don't agree with it!)

In MS SQL you have Server, Database, Schema/Owner, Table.

So I can select data from NewYorkServer.AccountsDatabase.SystemAdminSchema.CustomerTable (some poetic licence!)

Typically you are connected to a specific database, and thus just refer to a Table (and the Schema\Owner is provided "by default") but you can refer, explicitly, to tables in other databases on the same server, or even to tables on other servers' databases.

Of particularly importance here may be the Owner/Schema.

If I make an SQL query:

SELECT Name, Company, Age FROM Personnel

then I get the "Personnel" table for the Schema I am assigned. (and, by default, if there is no specific Schema assigned to me, or no table called "Personnel" within that Schema, then I get the table owned by the "dbo" schema (dbo stands for "DataBase Owner")

So ... if I am logged on as Kristen, and there is a schema called "Developers", which is assigned to my User ID [and other users too probably] then

SELECT Name, Company, Age FROM Personnel

actually gets me:

SELECT Name, Company, Age FROM Developers.Personnel

This is important because the application can just do "SELECT Name, Company, Age FROM Personnel" and the actual data varies according to whom is logged in, and which Schema they are assigned.

There is another trick that you can pull. This uses something called a "VIEW" which is a pseudo table.

CREATE VIEW Developers.Personnel
AS
SELECT Name, Company, Age
FROM MasterPersonnelTable
WHERE Department = 'DEVELOPERS'

CREATE VIEW Sales.Personnel
AS
SELECT Name, Company, Age
FROM MasterPersonnelTable
WHERE Department = 'SALES'

we now have the same named view "Personnel" in both the Sales and Developers schemas

People with User IDs which associate with either of those Schemas can query the "Personnel" table (which is actually a View, but the distinction is blurred at the point of making the query such that you don't need to know what is physically there - this is by design!) and you only see what you are entitled to see.

Furthermore you can put permissions on the VIEWs and TABLEs so I, as a Developer, can NOT type in the query:

SELECT Name, Company, Age
FROM Sales.Personnel

nor

SELECT Name, Company, Age
FROM MasterPersonnelTable

as I will just get a permission error.

So ... you could just have one table of all the data ("MasterPersonnelTable" in my example).

This could be queried by the central group that is allowed access to everything.

Then you could have 20 separate VIEWs, one for each Group, so when a Group query the data they only see their own data (i.e. because they are assigned a specific schema).

There is another way:

I don't know how you are going to access the data. If you are going to have free rein to connect to the database and "help yourself" to the data then the method above is what you need.

If, instead, you are going to have an application that you use to view / add / change / delete records, and that is your ONLY means of access, then it is more normal to have the application control the permissions.

So the application has a table of Users / Passwords / Group and a login form.

I login as Kristen/PASSWORD and the system then knows that I am in the "PROGRAMMER" group, so the only data it shows me is:

WHERE Group = 'PROGRAMMER'

there is opportunity for a program error to allow one group to accidentally see another groups data - that's down to testing, and fixing anything if that arises. If it is absolutely A.1. critical to you that this cannot happen (the groups are competitors or there are data confidentiality issues) then this may not be acceptable, but it IS common, so with good programming methodologies and testing it should not happen.

(You can still have the VIEWs / Schemas ... so you can have everything all-in-one, an application that allows data maintenance, and segregates the data according to the User / Password / Group, but you can also have the VIEWs for self-service read-only access to query the data)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources