Showing posts with label snapshot. Show all posts
Showing posts with label snapshot. Show all posts

Tuesday, March 20, 2012

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
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
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> 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
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

Monday, March 19, 2012

Copying inicial snapshot to the subscriber via CD

Hi!
I have a very large database that I replicate to 40 subscribers. The problem is that where I make a new push subscription and SQL is applying the Msmerge_contens, I obtain the message "The subscriber didn't respond in 10 minutes. Agent is suspect." I have
already increment de 10 minutes to 2 hours but the problem continues to exist. How can I put the snapshot in the subscriber via CD in order to avoid all then data transference over a slow network?
Regards
This message does not mean that your snapshot will not be applied
successfuly, it just means that it takes more than 10 minutes to transfer
some article data. This status of merge agent looks like an error (in EM),
but it is not. Just check if the merge agent job is runing - if it is, then
everything is fine, it just takes time..
If you want to avoid copying all the data to subscribers you can make a
backup of your publisher and restore it in remote location. Then create
subscription in such a way, that it would not synchronize data - after
restore data is identical anyway. But doing it in such a way you have to
make sure that during the interval between publisher backup and restore on
subscriber, no data changes are made on publisher.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Pedro" <pedro.lopes@.sqps.ivn.pt> wrote in message
news:C7D02833-9B35-4798-94F2-9B6FDED8808D@.microsoft.com...
> Hi!
> I have a very large database that I replicate to 40 subscribers. The
problem is that where I make a new push subscription and SQL is applying the
Msmerge_contens, I obtain the message "The subscriber didn't respond in 10
minutes. Agent is suspect." I have already increment de 10 minutes to 2
hours but the problem continues to exist. How can I put the snapshot in the
subscriber via CD in order to avoid all then data transference over a slow
network?
> Regards
>

Thursday, March 8, 2012

copying database

Twice a month we need to take a copy/snapshot of an existing DB on server 1
and place it on a server 2. Each copy/snapshot can overwrite the
pre-existing database on server 2. Right now we are doing this manually wit
h
backups and restores. We would like to automate this process... what are
some options in doing this?
-jonJon,
DTS - Transfer Databases Task or DTS (or script) detach db, copy file,
attach db.
HTH
Jerry
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||Scripts, scripts, and more scripts.
I do this weekly to refresh our development, qa, and demo environments from
production backups. I have tried other techniques and the backup-restore
one works best. I have scripts to fix logins and reset database properties
as part of the overall refresh process.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||> backups and restores. We would like to automate this process... what are
> some options in doing this?
Have you seen the section in BOL - Administering SQL Server / Automating
Administrative Tasks?|||What drawbacks do you see with DTS? Does your script take the backup and
create the copy along with fixing logins and resetting db properties?
Finally, where is a good place to go to regarding how to create these type o
f
scripts? Thanks in advance!
"Geoff N. Hiten" wrote:

> Scripts, scripts, and more scripts.
> I do this weekly to refresh our development, qa, and demo environments fro
m
> production backups. I have tried other techniques and the backup-restore
> one works best. I have scripts to fix logins and reset database properti
es
> as part of the overall refresh process.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jon" <Jon@.discussions.microsoft.com> wrote in message
> news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
>
>|||Nothing wrong with DTS. I needed to minimize the impact on the production
system so piggybacking on my normal backup rotation seemed to make sense.
Yes, my scripts use sp_change_users_login to remap users and logins
correctly. They handle replication properties, recovery models, and moving
files to the appropriate target folders. The restore script even kicks all
the users out of the target database so I can have exclusive access to
execute the restore command. They even work (with a few adjustments) if you
have backup compression tools like SQLLiteSpeed or SQLSafe.
I did it by noting all the tasks I did manually through Enterprise Mangler
and then creating a script for each task. If you get stuck you can look in
BOL for specifics on the task or you can search online or even ask again
right here. You can always cheat and use Profiler to determine what
Enterprise Mangler is doing "under the covers".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:D9577E91-BC9C-4536-B8EB-597544A4F355@.microsoft.com...[vbcol=seagreen]
> What drawbacks do you see with DTS? Does your script take the backup and
> create the copy along with fixing logins and resetting db properties?
> Finally, where is a good place to go to regarding how to create these type
> of
> scripts? Thanks in advance!
> "Geoff N. Hiten" wrote:
>

copying database

Twice a month we need to take a copy/snapshot of an existing DB on server 1
and place it on a server 2. Each copy/snapshot can overwrite the
pre-existing database on server 2. Right now we are doing this manually with
backups and restores. We would like to automate this process... what are
some options in doing this?
-jonJon,
DTS - Transfer Databases Task or DTS (or script) detach db, copy file,
attach db.
HTH
Jerry
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||Scripts, scripts, and more scripts. :)
I do this weekly to refresh our development, qa, and demo environments from
production backups. I have tried other techniques and the backup-restore
one works best. I have scripts to fix logins and reset database properties
as part of the overall refresh process.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||> backups and restores. We would like to automate this process... what are
> some options in doing this?
Have you seen the section in BOL - Administering SQL Server / Automating
Administrative Tasks?|||What drawbacks do you see with DTS? Does your script take the backup and
create the copy along with fixing logins and resetting db properties?
Finally, where is a good place to go to regarding how to create these type of
scripts? Thanks in advance!
"Geoff N. Hiten" wrote:
> Scripts, scripts, and more scripts. :)
> I do this weekly to refresh our development, qa, and demo environments from
> production backups. I have tried other techniques and the backup-restore
> one works best. I have scripts to fix logins and reset database properties
> as part of the overall refresh process.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jon" <Jon@.discussions.microsoft.com> wrote in message
> news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> > Twice a month we need to take a copy/snapshot of an existing DB on server
> > 1
> > and place it on a server 2. Each copy/snapshot can overwrite the
> > pre-existing database on server 2. Right now we are doing this manually
> > with
> > backups and restores. We would like to automate this process... what are
> > some options in doing this?
> >
> > -jon
>
>|||Nothing wrong with DTS. I needed to minimize the impact on the production
system so piggybacking on my normal backup rotation seemed to make sense.
Yes, my scripts use sp_change_users_login to remap users and logins
correctly. They handle replication properties, recovery models, and moving
files to the appropriate target folders. The restore script even kicks all
the users out of the target database so I can have exclusive access to
execute the restore command. They even work (with a few adjustments) if you
have backup compression tools like SQLLiteSpeed or SQLSafe.
I did it by noting all the tasks I did manually through Enterprise Mangler
and then creating a script for each task. If you get stuck you can look in
BOL for specifics on the task or you can search online or even ask again
right here. You can always cheat and use Profiler to determine what
Enterprise Mangler is doing "under the covers".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:D9577E91-BC9C-4536-B8EB-597544A4F355@.microsoft.com...
> What drawbacks do you see with DTS? Does your script take the backup and
> create the copy along with fixing logins and resetting db properties?
> Finally, where is a good place to go to regarding how to create these type
> of
> scripts? Thanks in advance!
> "Geoff N. Hiten" wrote:
>> Scripts, scripts, and more scripts. :)
>> I do this weekly to refresh our development, qa, and demo environments
>> from
>> production backups. I have tried other techniques and the backup-restore
>> one works best. I have scripts to fix logins and reset database
>> properties
>> as part of the overall refresh process.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Jon" <Jon@.discussions.microsoft.com> wrote in message
>> news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
>> > Twice a month we need to take a copy/snapshot of an existing DB on
>> > server
>> > 1
>> > and place it on a server 2. Each copy/snapshot can overwrite the
>> > pre-existing database on server 2. Right now we are doing this
>> > manually
>> > with
>> > backups and restores. We would like to automate this process... what
>> > are
>> > some options in doing this?
>> >
>> > -jon
>>

copying database

Twice a month we need to take a copy/snapshot of an existing DB on server 1
and place it on a server 2. Each copy/snapshot can overwrite the
pre-existing database on server 2. Right now we are doing this manually with
backups and restores. We would like to automate this process... what are
some options in doing this?
-jon
Jon,
DTS - Transfer Databases Task or DTS (or script) detach db, copy file,
attach db.
HTH
Jerry
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon
|||Scripts, scripts, and more scripts.
I do this weekly to refresh our development, qa, and demo environments from
production backups. I have tried other techniques and the backup-restore
one works best. I have scripts to fix logins and reset database properties
as part of the overall refresh process.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon
|||> backups and restores. We would like to automate this process... what are
> some options in doing this?
Have you seen the section in BOL - Administering SQL Server / Automating
Administrative Tasks?
|||What drawbacks do you see with DTS? Does your script take the backup and
create the copy along with fixing logins and resetting db properties?
Finally, where is a good place to go to regarding how to create these type of
scripts? Thanks in advance!
"Geoff N. Hiten" wrote:

> Scripts, scripts, and more scripts.
> I do this weekly to refresh our development, qa, and demo environments from
> production backups. I have tried other techniques and the backup-restore
> one works best. I have scripts to fix logins and reset database properties
> as part of the overall refresh process.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jon" <Jon@.discussions.microsoft.com> wrote in message
> news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
>
>
|||Nothing wrong with DTS. I needed to minimize the impact on the production
system so piggybacking on my normal backup rotation seemed to make sense.
Yes, my scripts use sp_change_users_login to remap users and logins
correctly. They handle replication properties, recovery models, and moving
files to the appropriate target folders. The restore script even kicks all
the users out of the target database so I can have exclusive access to
execute the restore command. They even work (with a few adjustments) if you
have backup compression tools like SQLLiteSpeed or SQLSafe.
I did it by noting all the tasks I did manually through Enterprise Mangler
and then creating a script for each task. If you get stuck you can look in
BOL for specifics on the task or you can search online or even ask again
right here. You can always cheat and use Profiler to determine what
Enterprise Mangler is doing "under the covers".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:D9577E91-BC9C-4536-B8EB-597544A4F355@.microsoft.com...[vbcol=seagreen]
> What drawbacks do you see with DTS? Does your script take the backup and
> create the copy along with fixing logins and resetting db properties?
> Finally, where is a good place to go to regarding how to create these type
> of
> scripts? Thanks in advance!
> "Geoff N. Hiten" wrote: