DB updates when limited to bak/trn files only

Hi,
I couldn't make a clear title without writing 2 lines.

My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.

The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.

I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).

Is there a way to do everything via SQL2005 script (job)?

Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.

Hope I'm clear.

Thanks for any help!Do you need the databases in read only mode, or fully recovered?

It would be difficult to use the tran log backups, because you could never recover the database.

However, saying that, I wonder if you could keep the receiving database in an unrecovered Read-Only mode, and transactionally publish that database to a third database that would be recovered?


Source Receiving Recovered
Server Full Backup Server DB Server
[----] ------> [----] [----]
[----] Tran Log backups [----] Transactional [----]
[----] ------> [----] Replication [----]
[----] [----] ------> [----]
[----] [----] [----]|||Database in read only mode should be enough as they are used for extraction.
The best and simpler solution again is having the rights on the remotes server but I need to do with it.

So as I won't be able to restore the DB with Trn log only, I still need to copy the Full Bak. I'm fine with that as having the full from 12 h ago is fine.
I guess the intermediary server solution in not needed for that. I know this is a widely used solution but maybe not for what I need (until SQL 2005 migration on remotes servers).

So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?

Backups are done on same Remote servers, then picked up from there.

Thanks|||So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?


Since I don't know all the info regarding your provider as regards backup retention period, etc ... here is an overview:

1. Download robocopy - it's free and very good - will auto-restart a file copy if interrupted

2. create a sql script that will be executed by sqlcmd (command line interface) - script builds the filename to download (assumes provider is building filenames including date of backup in filename) and uses robocopy to perform the copy to your local storage. Also have that script create a small file called "CopyComplete.txt" after robocopy successfully finishes (you could copy it from another locatiopn"

3. Create a scheduled job that checks periodically for the "CopyComplete.txt" file. When it finds it, it deletes it and then does the database restore using the replace option.

This is one method ... there are many variations of this ... you chose one and debug it until it works, then use that as a template for all your databases.|||Bkp retention is 2 days (2 full and 6 trn) (so need to grag the last ones, always same time)

I'll try Robocopy with your steps then and will let u know how i is.

thanks again

DB restore with Full Text Catalogs

I have what I believe to be a bizarre scenario.
I have a series of databases that I've backed up and restored onto another
machine.
However, the full text catalogues were not included in the backup...should
they have been?
I see that the catalogues are actually stored in the folder
MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
data across or does it need to be rebuilt locally?
I ask because I am creating a nightly job to backup the databases on
serverA, copy them to serverB and then restore them. I could use ROBOCOPY
to copy the contents of /MSSQL/FTDATA/ too if required.
Thanks
Griffhave a look at this kb article for more information on restored full text
catalogs with databases
http://support.microsoft.com/default.aspx?scid=kb;en-us;240867
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:#0iD5B6#DHA.2804@.tk2msftngp13.phx.gbl...
> I have what I believe to be a bizarre scenario.
> I have a series of databases that I've backed up and restored onto another
> machine.
> However, the full text catalogues were not included in the backup...should
> they have been?
> I see that the catalogues are actually stored in the folder
> MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
> data across or does it need to be rebuilt locally?
> I ask because I am creating a nightly job to backup the databases on
> serverA, copy them to serverB and then restore them. I could use ROBOCOPY
> to copy the contents of /MSSQL/FTDATA/ too if required.
> Thanks
> Griff
>

DB restore with Full Text Catalogs

I have what I believe to be a bizarre scenario.
I have a series of databases that I've backed up and restored onto another
machine.
However, the full text catalogues were not included in the backup...should
they have been?
I see that the catalogues are actually stored in the folder
MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
data across or does it need to be rebuilt locally?
I ask because I am creating a nightly job to backup the databases on
serverA, copy them to serverB and then restore them. I could use ROBOCOPY
to copy the contents of /MSSQL/FTDATA/ too if required.
Thanks
Griffhave a look at this kb article for more information on restored full text
catalogs with databases
http://support.microsoft.com/defaul...kb;en-us;240867
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:#0iD5B6#DHA.2804@.tk2msftngp13.phx.gbl...
> I have what I believe to be a bizarre scenario.
> I have a series of databases that I've backed up and restored onto another
> machine.
> However, the full text catalogues were not included in the backup...should
> they have been?
> I see that the catalogues are actually stored in the folder
> MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
> data across or does it need to be rebuilt locally?
> I ask because I am creating a nightly job to backup the databases on
> serverA, copy them to serverB and then restore them. I could use ROBOCOPY
> to copy the contents of /MSSQL/FTDATA/ too if required.
> Thanks
> Griff
>

DB Replication or Table Replication via triggers?

Hello everyone,

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.

I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

Any feedback would be appreciated.

Regards!

AnthonyI'd recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,

Thank you for your response. I will look into SQL Server 2005's
replication and static row level filtering; are there any books or web-
sites you might recommend? I will need to be able to set up and modify
the row-filter criteria programatically, and the reviews @. Barnes and
Noble on "Pro SQL Server 2005 Replication" are pretty dismal.

Regards,

Anthony

On Apr 17, 3:09 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:

Quote:

Originally Posted by

I'd recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

|||Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.
BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,

On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:

Quote:

Originally Posted by

Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.

Will do.

Quote:

Originally Posted by

BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.

Thanks for your help Paul!

Anthony|||Anthony Paul wrote:

Quote:

Originally Posted by

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.
>
I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?|||Hello Ed,

That's a very good idea, if it turns out that the filter isn't
dynamically configurable then perhaps using a view as a filter and
replicating the view can compensate since views can be modified at any
time. However, I doubt that replication can be done on a view rather
than on a table. I'll have to check it out!

Regards,

Anthony

On Apr 17, 9:46 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

Anthony Paul wrote:

Quote:

Originally Posted by

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.

>

Quote:

Originally Posted by

I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

>
Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?- Hide quoted text -
>
- Show quoted text -

|||I just finished looking up using indexed views versus a filter for
replication and it turns out that the view is much slower than a
filter (about 3x as slow) because the log reader has to log each
transaction twice, once for the view and once for the table. In my
case performance is of utmost concern so the overhead involved in this
is not something they can live with. Sigh...

Quote:

Originally Posted by

Quote:

Originally Posted by

Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||On Apr 18, 12:24 am, Anthony Paul <anthonypa...@.gmail.comwrote:

Quote:

Originally Posted by

Hello Paul,
>
On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:
>

Quote:

Originally Posted by

Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.

>
Will do.
>

Quote:

Originally Posted by

BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

>
Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.
>
Thanks for your help Paul!
>
Anthony

This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).

It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.

Damien|||Hello Damien,

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

Regards,

Anthony

Quote:

Originally Posted by

This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).
>
It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.
>
Damien- Hide quoted text -
>
- Show quoted text -

|||Anthony Paul wrote:

Quote:

Originally Posted by

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

TPTB may start considering it an option if you give them a cost
analysis, depending on whether their previous motivation was "wouldn't
it be nice if" (yes, but) or "we think this is cheaper" (no it isn't)
or "this is required for security reasons" (ugh, okay) or whatever.|||Anthony Paul (anthonypaulo@.gmail.com) writes:

Quote:

Originally Posted by

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

From my meager experience of replication, it seems clear that the database
has to be really huge - several terabytes - to make a dynamic filtering
defensible from a cost perspective. It would be difficult to develop,
difficult to maintain and manage.

The only serious option I see to full replication is a static subset.
That is define what will be supported in replication V1. If a new reqiure-
ment that is not covered, it would have to wait to V2. The idea would
of course to only strip really big stuff with low proability to be included.

And this is what you should tell the powers that be: replicating the entire
database will be far less expensive than changing what is replicated
dynamically.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DB Replication or Table Replication via triggers?

Hello everyone,
I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.
I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?
Any feedback would be appreciated.
Regards!
Anthony
Hello Paul,
Thank you for your response. I will look into SQL Server 2005's
replication and static row level filtering; are there any books or web-
sites you might recommend? I will need to be able to set up and modify
the row-filter criteria programatically, and the reviews @. Barnes and
Noble on "Pro SQL Server 2005 Replication" are pretty dismal.
Regards,
Anthony
On Apr 17, 3:09 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend transactional replication with a nosync initialization. This
> is where the initial setup on the reporting server is achieved by using a
> restore of the database and after that, only subsequent changes are sent
> down. If you are using SQL Server 2005, greater concurrency can be achieved
> by using the read committed snapshot isolation level.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
|||Hello Paul,
On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Have a look at Hilary's book for snapshot and transactional, but if you're
> after more merge info and don't like the Pro book then it's really BOL that
> you need and then doing some scenarios for yourself to gain experience. As
> for websites, I have some useful info on the site below and there are other
> articles out on the various SQL Server sites you can get by googling, but
> nothing I think specific to your requirements.
Will do.

> BTW this doesn't really lend itself to modifying the filters dynamically. At
> least this is not as straightforward as you might think. Normally the
> partitions are well designed to start with. If you want something more
> dynamic, then I'd not filter at all in replication and I'd use filters on
> the client application instead.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.
Thanks for your help Paul!
Anthony
|||Anthony Paul wrote:

> I am involved in a scenario where there is a huge (SQL Server 2005)
> production database containing tables that are updated multiple times
> per second. End-user reports need to be generated against the data in
> this database, and so the powers-that-be came to the conclusion that a
> reporting database is necessary in order to offload report processing
> from production; of course, this means that data will have to be
> replicated to the reporting database. However, we do not need all of
> the data in the production database, and perhaps a filtering criteria
> can be established where only certain rows are replicated over to the
> reporting database as they're inserted (and possibly updated/deleted).
> The current though process is that the programmers designing the
> queries/reports will know exactly what data they need from production
> and be able to modify the replication criteria as needed. For example,
> programmer A might write a report where the data he needs can be
> expressed in a simple replication criteria for table T where column X
> = "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
> month later and write a report whose relies on the same table T where
> column X = "METAL" and column Z in (12, 24, 36). Programmer B will
> have to modify Programmer A's replication criteria in such a way as to
> accomodate both reports, in this case something like "Copy rows from
> table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
> "METAL" and col Z in (12, 24, 36))". The example I gave is really
> trivial of course but is sufficient to give you an idea of what the
> current thought-process is.
> I assume that this is a requirement that many of you may have
> encountered in the past and I am wondering what solutions you were
> able to come up with. Personally, I believe that the above method is
> prone to error (in this case the use of triggers to specify
> replication criteria) and I'd much rather use replication services to
> copy tables in their entirety. However, this does not seem to be an
> option in my case due to the sheer size of certain tables. Is there
> anything out there that performs replication based on complex
> programmer defined criteria? Are triggers a viable alternative? Any
> alternative out-of-the-box solutions?
Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?
|||Hello Ed,
That's a very good idea, if it turns out that the filter isn't
dynamically configurable then perhaps using a view as a filter and
replicating the view can compensate since views can be modified at any
time. However, I doubt that replication can be done on a view rather
than on a table. I'll have to check it out!
Regards,
Anthony
On Apr 17, 9:46 pm, Ed Murphy <emurph...@.socal.rr.com> wrote:
> Anthony Paul wrote:
>
> Is it possible to create views, then configure things so that just
> those views are replicated as tables on the second server... Hide quoted text -
> - Show quoted text -
|||I just finished looking up using indexed views versus a filter for
replication and it turns out that the view is much slower than a
filter (about 3x as slow) because the log reader has to log each
transaction twice, once for the view and once for the table. In my
case performance is of utmost concern so the overhead involved in this
is not something they can live with. Sigh...

>
> - Show quoted text -
|||On Apr 18, 12:24 am, Anthony Paul <anthonypa...@.gmail.com> wrote:
> Hello Paul,
> On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Will do.
>
> Ahhh... then that's a problem, I'd definitely need the ability to be
> able to programatically and dynamically change the filtering criteria
> as the need arises, in this case every time a new report is requested
> that needs a subset of data not being captured by the replication
> process. You would think that this is such a common scenario... Also,
> filtering on the client side is not an option either since that would
> mean that all of the data would get replicated to the reporting db. I
> could have sworn that I read in msdn that the filters could be changed
> via stored procs though... I'll have to look that up.
> Thanks for your help Paul!
> Anthony
This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).
It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.
Damien
|||Hello Damien,
I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.
Regards,
Anthony

> This may sound like a stupid question, but are you sure replicating
> the whole database isn't an option? I know you've described the
> database as huge, but one mans huge is another mans insignificant (or
> the other way around).
> It just sounds like you're putting in a lot of work when you may be
> able to keep it simple. Apologies if this is a path you've already
> worn smooth, just wondering what has made you sure that bog standard
> replication isn't the way to go.
> Damien- Hide quoted text -
> - Show quoted text -
|||Anthony Paul wrote:

> I would *love* to have a full replication going and not have to worry
> about the added complexity of creating dynamic filters or triggers,
> but the powers that be simply do not consider it an option. Since I'm
> not the one that makes the decisions, I can only go by whatever
> options are available. They want ONLY a subset of data to be captured,
> nothing more. That would be fine with me if the filter was static (ie.
> not subject to change every time a new report is requested) but given
> the requirements I am in the same camp as you that a full replication
> would be best.
TPTB may start considering it an option if you give them a cost
analysis, depending on whether their previous motivation was "wouldn't
it be nice if" (yes, but) or "we think this is cheaper" (no it isn't)
or "this is required for security reasons" (ugh, okay) or whatever.
|||Anthony Paul (anthonypaulo@.gmail.com) writes:
> I would *love* to have a full replication going and not have to worry
> about the added complexity of creating dynamic filters or triggers,
> but the powers that be simply do not consider it an option. Since I'm
> not the one that makes the decisions, I can only go by whatever
> options are available. They want ONLY a subset of data to be captured,
> nothing more. That would be fine with me if the filter was static (ie.
> not subject to change every time a new report is requested) but given
> the requirements I am in the same camp as you that a full replication
> would be best.
From my meager experience of replication, it seems clear that the database
has to be really huge - several terabytes - to make a dynamic filtering
defensible from a cost perspective. It would be difficult to develop,
difficult to maintain and manage.
The only serious option I see to full replication is a static subset.
That is define what will be supported in replication V1. If a new reqiure-
ment that is not covered, it would have to wait to V2. The idea would
of course to only strip really big stuff with low proability to be included.
And this is what you should tell the powers that be: replicating the entire
database will be far less expensive than changing what is replicated
dynamically.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

DB LOCKS

Scenario :
Highly transactional DB , with several tables having 100 thousand rows.
Tons of storedprocedures with 1000's of line of code using temp tables and table variables...
Issue :
awefully slow. (vage word, me dont like it either, but fact)
am getting very very intresting locks in the database ,
lot of index locks are being exclusively held on tempdb tables. ?
can you please let me know the reasons for this type of locks ?
the locks as seen in syslockinfo table are like below , dbid 2 is from tempdb.
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
[BULK-OP-LOG]
Do some of your stored procedures create temp. tables, index them, then
insert/delete/update these temp. tables? If yes, then you will see locks
in temp. DB.

> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
if I remember it right, the first number is the file #, 2nd is page #,
and 3rd is row #.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
> Scenario :
> Highly transactional DB , with several tables having 100 thousand rows.
> Tons of storedprocedures with 1000's of line of code using temp tables and table variables...
> Issue :
> awefully slow. (vage word, me dont like it either, but fact)
> am getting very very intresting locks in the database ,
> lot of index locks are being exclusively held on tempdb tables. ?
> can you please let me know the reasons for this type of locks ?
> the locks as seen in syslockinfo table are like below , dbid 2 is from tempdb.
> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
> [BULK-OP-LOG]
>
|||How to minimise these ?
Am also encountering locks such as
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
What is the best method to avoid these locks which are eventually causing the bottleneck in the db perf ?
"Eric.Li" wrote:

> Do some of your stored procedures create temp. tables, index them, then
> insert/delete/update these temp. tables? If yes, then you will see locks
> in temp. DB.
>
> if I remember it right, the first number is the file #, 2nd is page #,
> and 3rd is row #.
>
> --
> Eric Li
> SQL DBA
> MCDBA
>
> DallasBlue wrote:
>
|||"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing
the bottleneck in the db perf ?
>
Busy databases generate a lot of locking activity. What evidence you you
have that these locks are causing your performance problem?
David
|||To minimize it, you have to rewrite some of your stored procedure.
[BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and log
backup. Do you have any jobs backing up your temp. DB? It's pointless to
backup your temp. DB.
[Upd-Stat] lock is used by update statistics job. Don't understand why
you want to update your temp. DB statistics
Locking won't do you much harm in temp. DB unless some temp. tables are
shared accross differnet sessions. I don't think that's the reason why
your DB is so slow. There are many reasons, did you configure your disk
correctly? spread out your tables? use filegroups? Queries blocking each
other out?
You need a DBA to look at your server to figure out why.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
[vbcol=seagreen]
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing the bottleneck in the db perf ?
> "Eric.Li" wrote:
>
|||The database is damn slow. There is no blocking (from sp_who). Server has magnanimous configuration. Its a very very high transactional db.
I have worked on [compile] locks , by using option keep plan on temp tables. And those compile locks are reduced a lot.
Am looking out , if there is any known ways we can avoid below locks like [compile]
IDX: 2:2345 [INDEX_ID]
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
Thanks
"David Browne" wrote:

> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> the bottleneck in the db perf ?
> Busy databases generate a lot of locking activity. What evidence you you
> have that these locks are causing your performance problem?
> David
>
>
|||Agree with you totally. need to rewrite the procs. The volume of procs are very heavy(1000's of procs, 1000's of lines), business logic in these procs is being written from several years. hope you get the picture of complexity of the procs here.
Create index #temp tables are being used. in lot of procs...
Do you think these would cause [upd-Stat] lock apart from [index] lock ?
Do you think that we should avoid creating the indexes on temptables ?
Thanks!!
"Eric.Li" wrote:

> To minimize it, you have to rewrite some of your stored procedure.
> [BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and log
> backup. Do you have any jobs backing up your temp. DB? It's pointless to
> backup your temp. DB.
> [Upd-Stat] lock is used by update statistics job. Don't understand why
> you want to update your temp. DB statistics
>
> Locking won't do you much harm in temp. DB unless some temp. tables are
> shared accross differnet sessions. I don't think that's the reason why
> your DB is so slow. There are many reasons, did you configure your disk
> correctly? spread out your tables? use filegroups? Queries blocking each
> other out?
> You need a DBA to look at your server to figure out why.
> --
> Eric Li
> SQL DBA
> MCDBA
> DallasBlue wrote:
>
>
|||NO jobs are running to back up tempdb. no-way. or no other database at the time these locks were seen.
Thanks
"DallasBlue" wrote:
[vbcol=seagreen]
> Agree with you totally. need to rewrite the procs. The volume of procs are very heavy(1000's of procs, 1000's of lines), business logic in these procs is being written from several years. hope you get the picture of complexity of the procs here.
> Create index #temp tables are being used. in lot of procs...
> Do you think these would cause [upd-Stat] lock apart from [index] lock ?
> Do you think that we should avoid creating the indexes on temptables ?
>
> Thanks!!
>
> "Eric.Li" wrote:
|||I understand your frustration, >1000 stored proc. with >1000 lines each
is no fun to maintain, let alone change/enhance.
If your #temp tables are indexed, that explains why you see those
[upd-stat] locks. You may want to turn off auto update/create statistics
If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-DB]
may be caused by auto grow / shrink. These two locks are on DB level,
turn if off see if it helps
Eric
DallasBlue wrote:
[vbcol=seagreen]
> NO jobs are running to back up tempdb. no-way. or no other database at the time these locks were seen.
> Thanks
> "DallasBlue" wrote:
>
Eric Li
SQL DBA
MCDBA
|||How about creating indexes on the temp tables with fill factor, could this decrease the index reorganization locks and upd-stat locks?
Thanks
"Eric.Li" wrote:

> I understand your frustration, >1000 stored proc. with >1000 lines each
> is no fun to maintain, let alone change/enhance.
> If your #temp tables are indexed, that explains why you see those
> [upd-stat] locks. You may want to turn off auto update/create statistics
> If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-DB]
> may be caused by auto grow / shrink. These two locks are on DB level,
> turn if off see if it helps
> Eric
> DallasBlue wrote:
>
> --
> Eric Li
> SQL DBA
> MCDBA
>

DB LOCKS

Scenario :
Highly transactional DB , with several tables having 100 thousand rows.
Tons of storedprocedures with 1000's of line of code using temp tables and t
able variables...
Issue :
awefully slow. (vage word, me dont like it either, but fact)
am getting very very intresting locks in the database ,
lot of index locks are being exclusively held on tempdb tables. ?
can you please let me know the reasons for this type of locks ?
the locks as seen in syslockinfo table are like below , dbid 2 is from temp
db.
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
[BULK-OP-LOG]Do some of your stored procedures create temp. tables, index them, then
insert/delete/update these temp. tables? If yes, then you will see locks
in temp. DB.

> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
if I remember it right, the first number is the file #, 2nd is page #,
and 3rd is row #.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
> Scenario :
> Highly transactional DB , with several tables having 100 thousand rows.
> Tons of storedprocedures with 1000's of line of code using temp tables and
table variables...
> Issue :
> awefully slow. (vage word, me dont like it either, but fact)
> am getting very very intresting locks in the database ,
> lot of index locks are being exclusively held on tempdb tables. ?
> can you please let me know the reasons for this type of locks ?
> the locks as seen in syslockinfo table are like below , dbid 2 is from te
mpdb.
> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
> [BULK-OP-LOG]
>|||How to minimise these ?
Am also encountering locks such as
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
What is the best method to avoid these locks which are eventually causing th
e bottleneck in the db perf ?
"Eric.Li" wrote:

> Do some of your stored procedures create temp. tables, index them, then
> insert/delete/update these temp. tables? If yes, then you will see locks
> in temp. DB.
>
> if I remember it right, the first number is the file #, 2nd is page #,
> and 3rd is row #.
>
> --
> Eric Li
> SQL DBA
> MCDBA
>
> DallasBlue wrote:
>|||"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing
the bottleneck in the db perf ?
>
Busy databases generate a lot of locking activity. What evidence you you
have that these locks are causing your performance problem?
David|||To minimize it, you have to rewrite some of your stored procedure.
[BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and lo
g
backup. Do you have any jobs backing up your temp. DB? It's pointless to
backup your temp. DB.
[Upd-Stat] lock is used by update statistics job. Don't understand why
you want to update your temp. DB statistics
Locking won't do you much harm in temp. DB unless some temp. tables are
shared accross differnet sessions. I don't think that's the reason why
your DB is so slow. There are many reasons, did you configure your disk
correctly? spread out your tables? use filegroups? Queries blocking each
other out?
You need a DBA to look at your server to figure out why.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
[vbcol=seagreen]
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing
the bottleneck in the db perf ?
> "Eric.Li" wrote:
>|||The database is damn slow. There is no blocking (from sp_who). Server has m
agnanimous configuration. Its a very very high transactional db.
I have worked on [compile] locks , by using option keep plan on temp tab
les. And those compile locks are reduced a lot.
Am looking out , if there is any known ways we can avoid below locks like &
#91;compile]
IDX: 2:2345 [INDEX_ID]
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
Thanks
"David Browne" wrote:

> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> the bottleneck in the db perf ?
> Busy databases generate a lot of locking activity. What evidence you you
> have that these locks are causing your performance problem?
> David
>
>|||Agree with you totally. need to rewrite the procs. The volume of procs are
very heavy(1000's of procs, 1000's of lines), business logic in these procs
is being written from several years. hope you get the picture of complexity
of the procs here.
Create index #temp tables are being used. in lot of procs...
Do you think these would cause [upd-Stat] lock apart from [index] lo
ck '
Do you think that we should avoid creating the indexes on temptables '
Thanks!!
"Eric.Li" wrote:

> To minimize it, you have to rewrite some of your stored procedure.
> [BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and
log
> backup. Do you have any jobs backing up your temp. DB? It's pointless to
> backup your temp. DB.
> [Upd-Stat] lock is used by update statistics job. Don't understand why
> you want to update your temp. DB statistics
>
> Locking won't do you much harm in temp. DB unless some temp. tables are
> shared accross differnet sessions. I don't think that's the reason why
> your DB is so slow. There are many reasons, did you configure your disk
> correctly? spread out your tables? use filegroups? Queries blocking each
> other out?
> You need a DBA to look at your server to figure out why.
> --
> Eric Li
> SQL DBA
> MCDBA
> DallasBlue wrote:
>
>|||NO jobs are running to back up tempdb. no-way. or no other database at the t
ime these locks were seen.
Thanks
"DallasBlue" wrote:
[vbcol=seagreen]
> Agree with you totally. need to rewrite the procs. The volume of procs ar
e very heavy(1000's of procs, 1000's of lines), business logic in these proc
s is being written from several years. hope you get the picture of complexit
y of the procs here.
> Create index #temp tables are being used. in lot of procs...
> Do you think these would cause [upd-Stat] lock apart from [index]
lock '
> Do you think that we should avoid creating the indexes on temptables '
>
> Thanks!!
>
> "Eric.Li" wrote:
>|||I understand your frustration, >1000 stored proc. with >1000 lines each
is no fun to maintain, let alone change/enhance.
If your #temp tables are indexed, that explains why you see those
[upd-stat] locks. You may want to turn off auto update/create statistics
If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-DB
]
may be caused by auto grow / shrink. These two locks are on DB level,
turn if off see if it helps
Eric
DallasBlue wrote:
[vbcol=seagreen]
> NO jobs are running to back up tempdb. no-way. or no other database at the
time these locks were seen.
> Thanks
> "DallasBlue" wrote:
>
Eric Li
SQL DBA
MCDBA|||How about creating indexes on the temp tables with fill factor, could this d
ecrease the index reorganization locks and upd-stat locks?
Thanks
"Eric.Li" wrote:

> I understand your frustration, >1000 stored proc. with >1000 lines each
> is no fun to maintain, let alone change/enhance.
> If your #temp tables are indexed, that explains why you see those
> [upd-stat] locks. You may want to turn off auto update/create statisti
cs
> If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-
DB]
> may be caused by auto grow / shrink. These two locks are on DB level,
> turn if off see if it helps
> Eric
> DallasBlue wrote:
>
>
> --
> Eric Li
> SQL DBA
> MCDBA
>