Database Snapshots vs Snaphot replication

In the enterprise version of SQL there is the facility to do snapshot
replication which I have not tried.
How does this differ from snapshot replication?
What this does is capture a point in time image of a database which you can
connect to as a different database.
Database snapshots are local to the database and uses up considerable disk
space. Snapshot replication can replicate locally and/or remote. It can
replicate to multiple subscribers.
Hilary Cotter
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
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1170891510.578187.22220@.v33g2000cwv.googlegro ups.com...
> In the enterprise version of SQL there is the facility to do snapshot
> replication which I have not tried.
> How does this differ from snapshot replication?
>
|||Thanks Hilary
My client is considering using mirroring for fault tolerance under SQL
Server 2005 but they also require a version of the database that is
reasonably up to date for reporting purposes. They dont have the
Enterprise version of SQL, so I was wondering if mirroring and then
doing snapshot replication (from the mirror) would be a viable
solution. I suspect not as the database would be unavailable for too
long. In addition, I am not even sure if this is an available option.
NB: They are unable to use transactional or merge replication due to
the tables not having primary keys.
I know that snapshot replication can be extremely slow but I am not
sure how much faster database snapshots are? In addition, when you do
them, can you simply overwrite the previous one as you dont want the
clients to have to access a different database?
Bottom line, is it a viable solution to make multiple database
snapshots during the course of a production day (from the mirror)?
I dont want to propose going to Enterprise version if it isnt going to
work.
On Feb 8, 12:29 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> What this does is capture a point in time image of a database which you can
> connect to as a different database.
> Database snapshots are local to the database and uses up considerable disk
> space. Snapshot replication can replicate locally and/or remote. It can
> replicate to multiple subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1170891510.578187.22220@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||One problem, you can't do snapshot replication off a mirror.
Hilary Cotter
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
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1170908921.373111.263690@.v33g2000cwv.googlegr oups.com...
> Thanks Hilary
> My client is considering using mirroring for fault tolerance under SQL
> Server 2005 but they also require a version of the database that is
> reasonably up to date for reporting purposes. They dont have the
> Enterprise version of SQL, so I was wondering if mirroring and then
> doing snapshot replication (from the mirror) would be a viable
> solution. I suspect not as the database would be unavailable for too
> long. In addition, I am not even sure if this is an available option.
> NB: They are unable to use transactional or merge replication due to
> the tables not having primary keys.
> I know that snapshot replication can be extremely slow but I am not
> sure how much faster database snapshots are? In addition, when you do
> them, can you simply overwrite the previous one as you dont want the
> clients to have to access a different database?
> Bottom line, is it a viable solution to make multiple database
> snapshots during the course of a production day (from the mirror)?
> I dont want to propose going to Enterprise version if it isnt going to
> work.
> On Feb 8, 12:29 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
|||Thanks, well I guess I can rule out snapshot replication.
However, what about database snapshots. How feasible is it to take
them once every half hour (say) and overwrite the existing one and
then use that as the reporting database server?
On Feb 8, 8:18 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> One problem, you can't do snapshot replication off a mirror.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1170908921.373111.263690@.v33g2000cwv.googlegr oups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

database size

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:70959
hello all,
I have implemented a transactional replication (queued updating) that
creates a full replica of the main database. The size of the main db is 15GB
and the replicated db's size is 12GB. I would like to know why this
difference.
thank you,
peti
Peti,
a few questions:
are you replicating the complete database?
Are you comparing the actual size after taking account of shrinking etc?
Are the indexes contiguous?
Do you have exactly the same set of indexes?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul,
Sorry for delay.
I have some answers for your questions:
The complete database is replicated and I shrunk all dbs before to compare.
I have the same set of indexes that are fragmented, I supose, because they
were never rebuilt.
Cheers,
peti
"Paul Ibison" wrote:

> Peti,
> a few questions:
> are you replicating the complete database?
> Are you comparing the actual size after taking account of shrinking etc?
> Are the indexes contiguous?
> Do you have exactly the same set of indexes?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
MamunHi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
--
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:
> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
MamunHi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:

> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
Mamun
Hi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/pro.../dbmirror.mspx
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:

> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>
sql

Database Roles being modified by unknown process

I have two databases on two separate servers, with Merge replication running
between them. DB1 and DB2 both have the same 3 roles I created for user
access. Twice within the last week, the roles on DB2, the subscriber, have
changed removing most of their table permissions, but not all. Does anyone
know of something that could be causing this?
Thanks
Profiler is your friend - yo can trace Grant, Revoke and Deny commands like
all other commands and procedures. Check the "Security Audit Event Classes"
topic in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kahunaVA" <kahunaVA@.discussions.microsoft.com> wrote in message
news:6A920DFD-EFDC-4D23-8D97-4B51E65F6F40@.microsoft.com...
> I have two databases on two separate servers, with Merge replication
running
> between them. DB1 and DB2 both have the same 3 roles I created for user
> access. Twice within the last week, the roles on DB2, the subscriber,
have
> changed removing most of their table permissions, but not all. Does anyone
> know of something that could be causing this?
> Thanks

database restored, publications gone

hi all,

our datbase was restore but the publications were all gone.

however replication monitor shows publication and subscription.

worst. can create the publication with the same name because

it raises an error saying that it can't drop publication becuase

there is a subscription in it.

how can i remove the publication when the are already gone in the publication

folder but exist in the replication monitor

thanks for the help

if you want to preserve replication when you restore a backup of a published database, specify "KEEP_REPLICATION" with your RESTORE command.

To remove replication from a published database, try sp_removedbreplication.

|||

thanks, i already resolve the issue.

i disabled the publications in the rep monitor and then delete its contents

Database Restore in Tran replication

Some problem happen from Appliction side & it was brought to my
attention that i have to restore the production database & same
production Database is used for transactional replication. i am not
sure if i restore the database it will break the replication or what
is better way to RESTORE. any help is appreciated & thanking in
advance.
If you restore to an earlier version of the production database, that means
that potentially you'll have non-convergence with the subscribers. You could
then do a comparison with a subscriber and decide which records to upload
from the subscriber, although this could end up being really complicated.
After that, I'd reinitialize the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for your reply, that is what i thought. thanks for your help
again.

Database Replication SQL 2000

I have two SQL 2000 servers. I want to make the primary and one the
secondary. I want to databases to stay replicated. If the Primary goes down
the Secondary would be used while we fix the Primary. Once the the Primary
is fixed it would be brought back online and the Secondary would replicate
it's data to the Primary. Then they would be in sync again.
What are my options here? What is the recomended way to accomplish this?
Thanks,
Scott
Scott,
there are many threads (and opinions!) on this sort of thing over the last
few months. I would recommend looking at queued updating subscribers
provided you aren't using BLOB datatypes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use Transaction Replication with updating Subscriber or use Log Shipping.

database replication / publication ?

Hi,
I'd like to keep an extra copy for database X on SQL server A to SQL server
B. The database X doesn't update very often during the day (but the computer
does have other jobs to run at tthe work hour). Both server A and B run SQL
server 2000 and on the same network. Currently the size of database X's MDF
and LDF are around 6 GB (3GB each). It would be nice to keep database X
always be in sync with its copy, but it is not a must. If the database
replication/publication takes a lot of CPU or is time comsuing, it is OK to
do it with a nightly job.
What is the good practice in the case? Thanks for sharing your thoughts.
Shawn,
for an extra copy of X on a standby server, and where latency is allowed,
I'd consider using Log Shipping. This article helps explain some of the key
differences: http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

database replication

Hi, newbie here.
I have an sql express database on my local machine (xp sp2) with many tables populated with plenty of data.
How can i duplicate/replicate this database?
Seems like it should be a straight forward thing, but i guess not.

Thanks!
-GarySQL Express can only subscribe to a replication, it can't publish I'm afraid.

Database replication

I'm new in the area of data replication.
I hope someone is willing to help me on this little thing I'm trying to accomplish.

Problem description
--
There is one central SQL Express database. And one (or more) SQL Express database(s) on remote locations. This approach of databases remote and central is mandatory due to some application features later-on.
The local database(s) on the remote location(s) will act as a sort of backup in case there is no network connection. Meaning that the data is stored locally at all times. And moved to the central database once the network connection is on.

Updates to the local database(s) should trigger a process where all data is moved to the central database if there is a network connection. So for now, it’s a one way stream: remote to central.
This approach gives us the option to delay the moves - if needed. For example, the daily data is kept local and moved to the central site over night. Which is a good thing to have if network resources are limited during office hours.

Solution
To make this happen, I was thinking allong the lines of using triggered stored procedures.
Where a local database update triggers a stored procedure that checks the network connection and performs the move to the central database.
If there is more than one record in the remote database, all records are moved to the central database. Also, an entry should be added to a log-file.
If the network connection is not there for whatever reason, an entry should be written in a log-file stating that there was no connection to the central database.

Options
-
Another way of solving this is using RMO-based publications and subscribers.
Where the database on the remote locations are publishers. And the central database are subscribers. However, according to Microsoft:
- SQL Express can only act as a subscriber.
- Data is copied. And not moved.
According to the SQL-BOL, the first limitation doesn’t exist when using programmatic, transactional replication.
However, the second needs to be solved as well. And in the examples of SQL-BOL, this is not mentioned. At least not that I’m aware of.

There are some advantages using RMO. For example deploying a new version of the application. Or deploying an updated database schema. Please let me know if this is proven to be really usefull. If so, this would be the preferred method – provided that the limitations mentioned earlier are solved. And if using this method, I would prefer stored procedures or Visual Basic for programming this.

Help wanted
--
Like I stated earlier, I’m a newbie on these replication matters.
So I really would like some help with this.
Who is willing to help with suggestions and examples?
Sofar, I’ve been able to:
- Install SQL Express with network connections enabled
- Running the Management Studio and create the data model I would like to use
- Add data manually and via an ODBC connection (using a system wide DSN).
- Run SQL select commands against the added data.
So the infrastructure is up and running….

Best regards,

Will Moonen

The major issue you face with your proposal is that you just can't do 'push' Replication with SQL Express.

So, you will be limited in approaches. Perhaps using stored procedures will work for you. I would suggest that you consider using a combination of Stored Procedures, SQLCmd.exe, and Windows scheduler; then schedule the stored procedures to run at a certain interval. In your proposal, you indicate that data will be 'pushed' up to the central server upon change, or stored locally if there is no connection, and then 'pushed' up on the next change. What if there isn't a 'next change' for quite some time -how would the local data be pushed to central?

|||

Many thanks for your feedback!

> The major issue you face with your proposal is that you just
> can't do 'push' Replication with SQL Express.

Agree - i.e. when you want to start things from the management studio.
But what about a 'push' by using the stored procedures? That is supported
according to the Books-On-Line.

> So, you will be limited in approaches. Perhaps using stored procedures will work for you.
> I would suggest that you consider using a combination of Stored Procedures, SQLCmd.exe,
> and Windows scheduler; then schedule the stored procedures to run at a certain interval.

Mmm, scheduling via Windows is an option. But only as a last resort.
There are to many things that can go wrong when using the Windows scheduler.

> In your proposal, you indicate that data will be 'pushed' up to the central server upon change,
> or stored locally if there is no connection, and then 'pushed' up on the next change.
> What if there isn't a 'next change' for quite some time -
> how would the local data be pushed to central?

The data model is rather small. It could hold the data for weeks.
There should be a push at least once per 24 hrs.
If not, the central site of the application will signal this.

Database Replication

Hi,
I'm learning Database Replication. what's the Database Replication for? what
applications use this feature? Please advice.
Hi
Have you read the section "Introducing Replication" in Books Online?
Hillary Cotter has a book on replication
http://www.nwsu.com/0974973602X.html which may help if you proceed along
this route.
John
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>
|||For having the same (or nearly the same) data in different places.
The most obvious example is a salesperson wanting to have a subset of the
the sales database on his laptop with the ability to make changes locally
and then have those changes get synchronized into the corporate database
when he re-connects to the LAN.
Also frequently used in Reporting Server situations, where the main
transactional database is too busy to serve up large queries for reports.
One way replication ships the data to a db that the reports are run off.
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>
|||Thanks all...
|||"Kevin3NF" wrote in message
> For having the same (or nearly the same) data in different places.
> The most obvious example is a salesperson wanting to have a subset of the
> the sales database on his laptop with the ability to make changes locally
> and then have those changes get synchronized into the corporate database
> when he re-connects to the LAN.
> Also frequently used in Reporting Server situations, where the main
> transactional database is too busy to serve up large queries for reports.
> One way replication ships the data to a db that the reports are run off.
>
Thanks Kevin, There are good sample, easy to understand.
How about Analysis Services? Can you please give me some samples too.
|||When the CEO wants to know how many widgets were sold in the NorthEast
district in Q4, 2004...he's going to open up Excel, connect to the Analysis
Services database and select those parameters. Very easy and he doesn't
have to know any programming.
Lots of work goes into making that AS database, form bringing the data in,
to getting it ready for the boss to start slicing and dicing.
I've never played in that area, so my knowledge is limited. I am pitching a
project that involves this, so ping me in early 2006...I should be much more
well-versed by then :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:uqiB5q1TFHA.3584@.TK2MSFTNGP14.phx.gbl...
> "Kevin3NF" wrote in message
> Thanks Kevin, There are good sample, easy to understand.
> How about Analysis Services? Can you please give me some samples too.
>
>
|||Thanks Kevin...

Database Replication

Hi,
I'm learning Database Replication. what's the Database Replication for? what
applications use this feature? Please advice.Hi
Have you read the section "Introducing Replication" in Books Online?
Hillary Cotter has a book on replication
http://www.nwsu.com/0974973602_.html which may help if you proceed along
this route.
John
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||For having the same (or nearly the same) data in different places.
The most obvious example is a salesperson wanting to have a subset of the
the sales database on his laptop with the ability to make changes locally
and then have those changes get synchronized into the corporate database
when he re-connects to the LAN.
Also frequently used in Reporting Server situations, where the main
transactional database is too busy to serve up large queries for reports.
One way replication ships the data to a db that the reports are run off.
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||Thanks all...|||"Kevin3NF" wrote in message
> For having the same (or nearly the same) data in different places.
> The most obvious example is a salesperson wanting to have a subset of the
> the sales database on his laptop with the ability to make changes locally
> and then have those changes get synchronized into the corporate database
> when he re-connects to the LAN.
> Also frequently used in Reporting Server situations, where the main
> transactional database is too busy to serve up large queries for reports.
> One way replication ships the data to a db that the reports are run off.
>
Thanks Kevin, There are good sample, easy to understand.
How about Analysis Services? Can you please give me some samples too.|||When the CEO wants to know how many widgets were sold in the NorthEast
district in Q4, 2004...he's going to open up Excel, connect to the Analysis
Services database and select those parameters. Very easy and he doesn't
have to know any programming.
Lots of work goes into making that AS database, form bringing the data in,
to getting it ready for the boss to start slicing and dicing.
I've never played in that area, so my knowledge is limited. I am pitching a
project that involves this, so ping me in early 2006...I should be much more
well-versed by then :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:uqiB5q1TFHA.3584@.TK2MSFTNGP14.phx.gbl...
> "Kevin3NF" wrote in message
> Thanks Kevin, There are good sample, easy to understand.
> How about Analysis Services? Can you please give me some samples too.
>
>|||Thanks Kevin...

database replication

I have one database(database1) in houston and the other database(database2) at Newjersy. SO if i wanted to replicate the data from the database1 to database2 how can I do that. I wanted to setup the replication to get the data into only one table. Infact both the tables are not identical in structure.



Thanks.There are several flavors of replication. Snapshot being the simplest, and which may work for you if the NJ data is read only. Snapshot rep is used mainly for bulk data movement, once a day, once a week type stuff. It is also used as a first step for the other rep types; transactional and merge replication.

Transaction rep, as it's name suggests, transfers data much more often. I don't think it's upon every db transaction but it's sure close to it.

Merge replication is when the folks in NJ need to update the data. This can get dicey.

BOL has very good info on replication.

Database Replication

I'm a newbie so bare with me. If I have two servers running SQL Server, a primary and a secondary, how can I make sure that both databases (One on each server) are always syncronized? In our situation the secondary server is a hot backup in case the primary fails. If the primary fails then data gets written to the secondary. When the primary comes back up then its database needs to be synced with the secondary. Can anyone tell me how to do this?Have a look on books online about Transactional Replication...

This is a form of replication that will make your two server in sync.

Any changes that are made on one server are automatically replicated over to your other server.

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.sql-server-performance.com/transactional_replication.asp

Hope this helps mate|||Thanks for the links! Do you know if you are doing transactional replication, if the other server is down, will the server que the transactions and keep trying to send them until it succeees?|||no,

As far as I know it will publish on an all or nothing basis.

If your primary server goes down then replication will stop because it has no server to replicate to (i.e no subscribers)

This is the time you flip the primary to your hot spare and fix the original server. After you have sorted the problem on the original server just kick off replication again.

Cheers|||Why don't you create a 2 node cluster?|||Not using Enterprise. I don't think it is available on standard.|||You're right, Only on Enterprize.

Database Replication

I follow the considerations definied by document, but I have an error
when I insert data into a table, because it has an identity field, and
the replication return the following error:
An explicit value for the identity column in table 'SAR_Callejero' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Then, I modified the insert sp and added the following:
SET IDENTITY_INSERT sar_callejero ON
But newly gave the same error.
Can you help me?
Thanks
Is your insert supplying an identity value when it shouldn't?
"cbaffigi@.apsf.com.ar" wrote:

> I follow the considerations definied by document, but I have an error
> when I insert data into a table, because it has an identity field, and
> the replication return the following error:
> An explicit value for the identity column in table 'SAR_Callejero' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Then, I modified the insert sp and added the following:
> SET IDENTITY_INSERT sar_callejero ON
> But newly gave the same error.
> Can you help me?
> Thanks
>

Database Replication

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:

> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Database Replication

We currently have multiple tables being replicated using merge snapshot. In
the majority of cases the whole table is required or a row filter is applied
based on a value in that table.
We have two sets of identical databases, except for their data, that
replicate their data from a backend database towards a frontend database.
The problem is that for database 1, all the necesarry data is replicated,
but for the other database 2, which has the same parameters, replication
settings etc.. certain blob files containing pdf files will not replicate
from the backend database towards the frontend database.
The replication only replicates the .doc files, so the replication is
partially done for the table that contains the .doc and .pdf files.
When I run the initial snapshot, the pdf files are replicated fine, but when
the agent runs, the pdf files are not replicated.
Has anyone encountered similar problems? Can someone identify what is
causing the problem by explaining why this is not working? My first idea
would be the sheduling the snapshot to run every night, instead of the
agent, but this would be a far from ideal solution and one that I would only
consider as a last choice. I fail to understand why the replication works
for database 1, but not for database 2.
parnold8104@.hotmail.com
can you run this command sp_configure 'max text repl size'
and then figure out what your maximum data size in the blob columns you are
replicating?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> We currently have multiple tables being replicated using merge snapshot.
In
> the majority of cases the whole table is required or a row filter is
applied
> based on a value in that table.
> We have two sets of identical databases, except for their data, that
> replicate their data from a backend database towards a frontend database.
> The problem is that for database 1, all the necesarry data is replicated,
> but for the other database 2, which has the same parameters, replication
> settings etc.. certain blob files containing pdf files will not replicate
> from the backend database towards the frontend database.
> The replication only replicates the .doc files, so the replication is
> partially done for the table that contains the .doc and .pdf files.
> When I run the initial snapshot, the pdf files are replicated fine, but
when
> the agent runs, the pdf files are not replicated.
> Has anyone encountered similar problems? Can someone identify what is
> causing the problem by explaining why this is not working? My first idea
> would be the sheduling the snapshot to run every night, instead of the
> agent, but this would be a far from ideal solution and one that I would
only
> consider as a last choice. I fail to understand why the replication works
> for database 1, but not for database 2.
> parnold8104@.hotmail.com
>
>
|||Hi Hilary,
thanks for replying, when I run the command sp_configure I get these values:
max text repl size (B) minimum: 0
maximum: 2147483647
config_value: 52428800
run_value: 52428800
I am not sure if the data size is the problem, because the .doc files that
are replicated are larger in size then the pdf files. Another thing I
noticed is that I can replicate my pdf records in the 'faulty' database, but
only if I do an update of the record in my backend database. So when the
record is 'new' the replication skips it, but if I update the record, by for
example changing a letter in a description field, the replication picks up
the record and pushes it towards the frontend database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> can you run this command sp_configure 'max text repl size'
> and then figure out what your maximum data size in the blob columns you
are[vbcol=seagreen]
> replicating?
> "Peter A" <parnold8104@.hotmail.com> wrote in message
> news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In
> applied
database.[vbcol=seagreen]
replicated,[vbcol=seagreen]
replicate[vbcol=seagreen]
> when
> only
works
>
|||can you post your schema and publication scripts here?
What version of SQL are you using? SP?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23whCdr3JEHA.232@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for replying, when I run the command sp_configure I get these
values:
> max text repl size (B) minimum: 0
> maximum: 2147483647
> config_value: 52428800
> run_value: 52428800
> I am not sure if the data size is the problem, because the .doc files that
> are replicated are larger in size then the pdf files. Another thing I
> noticed is that I can replicate my pdf records in the 'faulty' database,
but
> only if I do an update of the record in my backend database. So when the
> record is 'new' the replication skips it, but if I update the record, by
for[vbcol=seagreen]
> example changing a letter in a description field, the replication picks up
> the record and pushes it towards the frontend database.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> are
snapshot.[vbcol=seagreen]
> database.
> replicated,
replication[vbcol=seagreen]
> replicate
but[vbcol=seagreen]
idea[vbcol=seagreen]
would
> works
>

Database Replication

I have to create a replication for a database thar have 7 Gb. The time
that consume is very important: almost 7 hs. Exist a procedure more
efficient?
How can I generate a replication restoring a backup, instead of make an
snapshop (like Informix).
Thanks in advance
In addition to what Paul said: when I generate my snapshots I use the
"Compress the snapshot..." option.
These snapshots are considerably smaller than a normal backup.
Jim.
"cbaffigi@.apsf.com.ar" wrote:

> I have to create a replication for a database thar have 7 Gb. The time
> that consume is very important: almost 7 hs. Exist a procedure more
> efficient?
> How can I generate a replication restoring a backup, instead of make an
> snapshop (like Informix).
>
> Thanks in advance
>
|||Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
should compress to <2GB but as the size increases you might have to consider
an alternative - WinZip or WinRar etc.
Rgds,
Paul Ibison
|||are you sure you aren't thinking of the msf files involved in subscription
copy? There was a 2 Gig limit on zip files some time ago, but I believe this
has changed now.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
> should compress to <2GB but as the size increases you might have to
consider
> an alternative - WinZip or WinRar etc.
> Rgds,
> Paul Ibison
>
|||I am afraid Paul is right with regard to the 2gig (overall compressed, per
uncompressed file) limitations in snapshot compression here. These
limitations partly came from our use of the Cabinet API and the fact that we
limit ourselves to a single cab file. The snapshot compression feature was
originally intended for folks trying to transfer a snapshot over (relatively
speaking) slow WAN and 2gig seemed plenty for that at the time. And if there
is a need to transfer a much larger amount of data, the recommendation is to
do what Paul described below.
I have pretty good idea of how we can retrofit the snapshot compression
feature to get around the 2gig limits and make it generally more pleasant to
use in terms of temporary storage requirements, but it is not easy to do and
will likely involve the use of a different set of compression apis other
than the cabinet API thereby causing backward compatibility breakages with
subscribers using existing version of replication binaries.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:efJEW139EHA.1452@.TK2MSFTNGP11.phx.gbl...
> are you sure you aren't thinking of the msf files involved in subscription
> copy? There was a 2 Gig limit on zip files some time ago, but I believe
this
> has changed now.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> consider
>
|||Raymond,
hopefully you're still monitoring this thread and can answer a related
question for me. If the compression option is used in an alternative
snapshot location, and there isn't the default location selected, I was
trying to find out where is the cab file created during processing? The
directory seems to be immediately created, but there is no CAB file there
during the initial processing and outputting of data - is it all done in
memory, or is there a temp file held in another path? I ask because on some
servers we have severe space issues and I have had to change the WinZip temp
directory several times because of a similar issue.
Rgds,
Paul Ibison
|||Hi Paul,
By default, the cabinet api will build the cab file in the temp directory
and will only copy the resulting cab to the "real" location at the end. This
should explain the disk space problems that you were seeing. Knowing what I
know now, I probably would have implemented the snapshot compression feature
differently but then again, we really didn't (and still don't) have time to
do it properly.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23I2NrZB%23EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Raymond,
> hopefully you're still monitoring this thread and can answer a related
> question for me. If the compression option is used in an alternative
> snapshot location, and there isn't the default location selected, I was
> trying to find out where is the cab file created during processing? The
> directory seems to be immediately created, but there is no CAB file there
> during the initial processing and outputting of data - is it all done in
> memory, or is there a temp file held in another path? I ask because on
some
> servers we have severe space issues and I have had to change the WinZip
temp
> directory several times because of a similar issue.
> Rgds,
> Paul Ibison
>
|||Thanks Raymond.
Rgds,
Paul