Replication Subscription Standard Edition

So I set up a "Replication/Local Publication" on a Production SQL
2005
Database.
It publishes a Database on the production database.
I then set up a "Replication/Local Subscription" on another Database
we call "Report Database"
that will have reports running on it and querying the Subscription
which is feed from the
production database.
They are syncronizing fine and everthing looks great.
My question is how do I access the Subscription? Can I query it like
a
database
using a "Database Engine Query"? Do I have to create a Database
instance on the "Report Database" Server
feeding off the Subscription?
Am I not going to be able to access the subscription because I don't
have snap shots?
I have no idea what to do now? How can I query the subscription? Can
the subscription act like a database?
I am having problems understanding the question. You have transactional
replication set up correctly and it is syncing ok to the Report Database.
This database can now be queried as per usual. if you are concerned about
blocking, you could use read_committed_snapshot isolation level to help with
the concurrency of reports.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Replication makes copies of tables and their data which by default will be
identical to the tables in the publication database. You should be able to
query them in the same way you query them in the publication database.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
> So I set up a "Replication/Local Publication" on a Production SQL
> 2005
> Database.
> It publishes a Database on the production database.
> I then set up a "Replication/Local Subscription" on another Database
> we call "Report Database"
> that will have reports running on it and querying the Subscription
> which is feed from the
> production database.
>
> They are syncronizing fine and everthing looks great.
>
> My question is how do I access the Subscription? Can I query it like
> a
> database
> using a "Database Engine Query"? Do I have to create a Database
> instance on the "Report Database" Server
> feeding off the Subscription?
> Am I not going to be able to access the subscription because I don't
> have snap shots?
>
> I have no idea what to do now? How can I query the subscription? Can
> the subscription act like a database?
>
|||On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Replication makes copies of tables and their data which by default will be
> identical to the tables in the publication database. You should be able to
> query them in the same way you query them in the publication database.
> --
> 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
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I'm just wondering how to query the subscription.
When the subscription is created on the Report Database is a database
created on that server?
|||On Mar 14, 8:15 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server... Hide quoted text -
> - Show quoted text -
I had an existing database on my Report Server that was the same name
of the Database on the Production Server that is getting publicated.
I am going to do a new publication on a different database and see how
it goes. Thanks guys
|||No, its not, you have to pre-create it.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server?
>
|||On Mar 14, 10:58 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No, its not, you have to pre-create it.
> --
> 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
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
You guys are right. I don't know what the heck I was doing when I
created my first Publication and Subscription.
Seems that everything is awesome now!
Thanks everyone
sql

Replication Standard or Enterprise

In setting up replication between databases do both sql installations have to be enterprise or can it be done with both sql server standard edition.
thanks,Replication is included in Standard Edition.|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...

Tell the other ...admins ... to take a hike:


SQL Server 2000 Standard Edition

This is a more affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
Data Transformation Services (DTS)
Replication (snapshot, transactional, and merge)
Full-Text Search
English Query
Stored procedure development and debugging tools
SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.|||Kindly ask the "other admins" for proof of this. Perhaps, if you build the replication into the QA system (a good idea, anyway), you can prove to them it is possible.|||I appreciate the feedback... and will do a lot more research... any experienced suggestions or tips that a tutorial or book might not mention .... thanks again guys...|||Or maybe they would like to read the 2005 features for themselves:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||I have a solution that not only replicates the data (standard or enterprise, 2000 or 2005) but also load balances read-only statements and provides effective means of resynchronization. I work for a company, PCTI (Parallel Computers Technology Inc), out of the Philadelphia area. We have a product, DBx, that does Parallel Synchronous Real Time Replication (up to 16 servers, local or remote) guaranteeing zero-loss of transactions. Anyone looking for more information should visit http://www.pcticorp.com or send me a private message via dbforums.com.

replication question

can replication between sql server 2005 standard edition and express editionSQL Server 2005 Express can only act as the subscriber in a replication
process.
ML
http://milambda.blogspot.com/|||I think it's also the case that it can only subscribe to an sql 2005
publisher, is this right?|||Although this is not quite obvious from the following article, I think SQL
Server 2005 Express can act as a subscriber in a SQL 2000 replication (where
either the Publisher or the Distributer are SQL Sever 2000).
http://msdn2.microsoft.com/en-us/library/ms143699(SQL.90).aspx
I'm not certain.
ML
http://milambda.blogspot.com/|||aahh - I see it depends on the type of replication.
transactional it's fine:
"A read-only Subscriber to a transactional publication can be any
version within two versions of the Publisher version. For example: a
SQL Server version 7.0 Publisher running can have SQL Server 2005
Subscribers; and a SQL Server 2005 Publisher can have SQL Server
version 7.0 Subscribers."
but merge it isn't
"A Subscriber to a merge publication can be any version less than or
equal to the Publisher version."
thanks for the link

replication problem

Hello:

We are using SQL Server 2000 Standard Edition on Windows 2000. If we want to set up a DR site, we have 2 options:

a. transactional replication
b. log shipping

However since we do not have Enterprise Edition, maybe the best option we have is transactional replication.

My problem with transactional replication is when we set it up, we do not seem to have control over initial snapshot. When the database is huge, we prefer to manually take the snapshot and restore it at DR site, is it possible ?

rgds,
eric ngYes, you have a choice between initializing the database to subscriber or not. If you say no, then sql would assume you already have the same schema and data in the subscriber.|||Originally posted by joejcheng
Yes, you have a choice between initializing the database to subscriber or not. If you say no, then sql would assume you already have the same schema and data in the subscriber.

Thank you. The manual does not seem to have step-by-step guidelines on how to manually send the snapshot over to subscriber server before transactional replication starts continous replication.

Btw, I come across a lot of people discouraging people using transactional replication as DR method.

Is it true that transactional replication is very taxing to the production server ?

Is custom log shipping worth trying (if we are using standard edition) ?

Is third-party software eg DoubleTake would be much better in our context ?

rgds,
eric ng|||The other way around that is to disable the snapshot right after you setup the replication. You can disable it in the Replication Monitor or you can disable it as a scheduled task.

Transactional Replication can be very slow if you have large batches coming accross the network. But it provides you more concurrent processing than Log Shipping. Remember, Log Shipping is simply a backup/restore process, and every time you restore the database you would have disallow or kill all the user access. Just imagine this is done hourly, the users will be interrupted every hour. On the other hand, Log Shipping is easier to manage. And if you can restore only once a day in the evening, that could be your choice.

Replication Problem

Hi,
Now, I've installed SQL Server 2000 standard edition, and
I've registered the Remote Computer in my Enterprise
Manager. How ever when I run the "Create Publication
Wizard", I get an Information Message, that:
"SQL Server Agent on 'MyComputer' currently uses the
system account, which causes replication between servers
to fail. In the follwing dialog box, specify another
account for the service startup acccout".
Anyway, once I press OK, I come through a Dialog Box
called "SQL Server Agent Properties - MyComputer", where
in the General Tab "Service Startup Account" is disabled.
After I ignore it and come through another Dialog Box
called "Specify Snaphsot Folder". In that Dialog box, the
default value
was "\\RemoteComputer\C$.....\MSSQL\ReplData", but I do
get another Information Message as saying:
"'\\RemoteComputer\C$.....\MSSQL\ReplData' is not a valid
path, or it referes to a file isntead of folder. Enter
the Path to an existing folder." And this doesn't give to
go through the Wizard.
Why this is happening? how can I use Enterprise Manager
from another computer to configure Replication Scenario's
to a computer with MSDE 2000 Installed, Else If i Cant use
enterprise Manager, how can I use the osql tool to
configure replication. The SQL books online is Complicated
on osql, so please help me by telling how would you
configure this replication without Enterprise Manager on
Computer with only MSDE 2000 installed?
Regards,
Nazeer.
NOTE: My computers are connected to a LAN
Nazeer,
if you're replicating to another computer you'll need to do 2 things:
(1) go to control panel, administrative tools, services and select the sql
server agent service. Change the startup account to a domain user. To make
things easy, put this account in the local admin's group (assuming
builtin/administrators are in sysadmin also on sqlserver). Do the same on
the subscriber, preferably with the same account. This is not the most
granular way to set things up, and for more detailed info, have a look in
the replication, security section of BOL. Initially let's just ensure you
can get this up and working.
(2) on the publisher share the repldata folder as \\computername\repldata
and configure replication to use this share (right-click replication
monitor, distributor properties, publishers tab, publisher elipsis...).
HTH,
Paul bison
|||Hi Paul,
Thanx for your solution, it did work out correctly. OK,
there's one more problem,
In case, If the Publisher is a MSDE 2000, how am I gone to
create a publications, subsribtions etc.? and what is ment
by "Row guide column"?
Nazeer,
>--Original Message--
>Nazeer,
>if you're replicating to another computer you'll need to
do 2 things:
>(1) go to control panel, administrative tools, services
and select the sql
>server agent service. Change the startup account to a
domain user. To make
>things easy, put this account in the local admin's group
(assuming
>builtin/administrators are in sysadmin also on
sqlserver). Do the same on
>the subscriber, preferably with the same account. This is
not the most
>granular way to set things up, and for more detailed
info, have a look in
>the replication, security section of BOL. Initially let's
just ensure you
>can get this up and working.
>(2) on the publisher share the repldata folder as
\\computername\repldata
>and configure replication to use this share (right-click
replication
>monitor, distributor properties, publishers tab,
publisher elipsis...).
>HTH,
>Paul bison
>
>.
>
|||Nazeer,
if you're using MSDE, you could use SQLDMO if you wanted to create the
publication programatically. If you need to do it graphically, the MSDE can
potentially be administered from Enterprise Manager - however, please first
check the licensing documents as I'm not too sure if this is permitted or
not. If you need to create a subscription on the MSDE box , you could use
windows synchronization manager.
GUIDS are used in merge replication and updating subscriber scenarios. Merge
will create a rowguid column or use an existing column having the rowguid
attribute. In merge the guid doesn't change while it does for updating
subscribers but still, they are essentially just unique identifiers for a
row.
HTH,
Paul Ibison
|||I suggest you use the replication ActiveX controls as they have methods in
them which allow you to connect to the Publisher without having to register
the subscribers in EM or using Client Network Utility.
If you use SQL DMO you will still have to use Client Network Utility which
is a violation of your licensing agreement for MSDE.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23quBWZffEHA.712@.TK2MSFTNGP09.phx.gbl...
> Nazeer,
> if you're using MSDE, you could use SQLDMO if you wanted to create the
> publication programatically. If you need to do it graphically, the MSDE
can
> potentially be administered from Enterprise Manager - however, please
first
> check the licensing documents as I'm not too sure if this is permitted or
> not. If you need to create a subscription on the MSDE box , you could use
> windows synchronization manager.
> GUIDS are used in merge replication and updating subscriber scenarios.
Merge
> will create a rowguid column or use an existing column having the rowguid
> attribute. In merge the guid doesn't change while it does for updating
> subscribers but still, they are essentially just unique identifiers for a
> row.
> HTH,
> Paul Ibison
>
|||Hi,
I just want to know, is it possible to replicate some
certain entries only. Now say, that there is a Server
(assume that it's called as MiniServer) which
Maintains "Purchase Orders". This Purchase Orders' Primary
Key is "OrderID" and it's an autoincremental field. Also
this MiniServer's Purchase Order Table's Last OrderID
value is 50. This MiniServer Replicates it's Data to the
MainServer at the end of Each office days.
Just note that the MainServer's Purchase Order Table has
it's Last OrderID = 25, and the MainServer needs all the
OrderID more than 25 from the MiniServer.Is this possible
with Replication? If so, how can I set it in SQL Server
Replication?
Awaiting your Response in Anticipation.
Thanx in Advance.
HIFNI NAZEER

>--Original Message--
>Nazeer,
>if you're using MSDE, you could use SQLDMO if you wanted
to create the
>publication programatically. If you need to do it
graphically, the MSDE can
>potentially be administered from Enterprise Manager -
however, please first
>check the licensing documents as I'm not too sure if this
is permitted or
>not. If you need to create a subscription on the MSDE
box , you could use
>windows synchronization manager.
>GUIDS are used in merge replication and updating
subscriber scenarios. Merge
>will create a rowguid column or use an existing column
having the rowguid
>attribute. In merge the guid doesn't change while it does
for updating
>subscribers but still, they are essentially just unique
identifiers for a
>row.
>HTH,
>Paul Ibison
>
>.
>
|||Hifni,
with merge replication, the it is not normally so difficult to achieve this
setup.
If you were using merge replication, only those records not yet transferred
from MiniServer to MainServer will be replicated, so if 1-25 originated from
MiniServer and are now on MainServer, they won't be replicated again. If the
records 1-25 came originally from MainServer and are now on MiniServer, they
won't be cycled backwards to MainServer, unless they have been modified on
MiniServer.
BTW there is no possibility of (identity & PK) OrderIDs overlapping - they
are partitioned into ranges either during the publication setup or manually.
HTH,
Paul Ibison

Replication in Standard Edition

Hello,
I hope everybody is doing ok.
I'm trying to set up my first "replication". I have the standard edition
of SQL SERVER 2000. While reading the documentation I am trying to identify
the the objects in the Enterprise Manager that the documentation is refering
to. For example Replication Monitor. I don't see this object in my
Enterprise Manager. I am wondering if this is because of the version that I
have, or maybe there is something else that I have to install?
Thank you very much in advance
Marlene A. Roman
Marlene,
the replication monitor is a utility that will appear in EM directly under
the server registration once you have set up a distributor.
There's no need to reregister and the version you have is fine - it appears
on all editions inc Personal Edition which disallows transactional
replication publishing.
HTH,
Paul Ibison
|||the only time it will not appear on a publisher (AFAIK) is when you have a
remote distributor.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVSad8KEHA.892@.TK2MSFTNGP09.phx.gbl...
> Marlene,
> the replication monitor is a utility that will appear in EM directly under
> the server registration once you have set up a distributor.
> There's no need to reregister and the version you have is fine - it
appears
> on all editions inc Personal Edition which disallows transactional
> replication publishing.
> HTH,
> Paul Ibison
>