There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Showing posts with label hit. Show all posts
Showing posts with label hit. Show all posts
Tuesday, March 20, 2012
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciated
Massa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegro ups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>
|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciated
Massa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegro ups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>
|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? I
f
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the onl
y
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? I
f
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the onl
y
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Replication and Log shipping
Sql server 2005 heavily hit database
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Posts (Atom)