newbie question

Hello,
I'm new to SQL server and I've just inherited a SQL server with a single
user database. The company would like to replicate it to sql server used for
reporting services. I'm thinking that transaction replication is the way to
go as the database is over 40 Gb in size. The only requirements are that the
database be available for reporting.
I've been looking into the database schema and Ive found that there are
seven tables with identity columns and other tables that dont have a primary
key. Can I still use transaction replication? do I need to make any changes
to teh schema?
Thanks
p.s. can somebody point me to a doc that highlights how to implement
transaction replication and what the various components are?
Troy,
for a reporting setup, transactional replication is often used. Log shipping
is sometimes used, but the users have to be disconnected when the logs are
restored which means a big latency if this is done out of office hours. As
for transactional replication, books on line (BOL) is a good resource, as
well as Hilary's book and some web articles
(http://www.mssqlcity.com/Articles/Re...TR/SetupTR.htm).
The main components are the 2 agents: snapshot and distribution whiche are
essentially jobs that run the associated replication executable. The
snapshot agent will create several text files and place them in the
distribution working folder - a share that the subscriber can see. The
distribution agent reads the distribution database to see what commands are
waiting to be executed. It loads the text files and applies them to the
subscriber and then it'll read any subsequent commands involving changes to
the data, and apply these also. Plenty of details in BOL, but what I'd
recommend is to complinment the reading and set it up on one machine by
replicating a table from Northwind to Pubs and so begin to get used to the
whole setup.
Finally, don't worry about the identity columns - they're not relevant for
your purposes and the identity property will not be retained on the
subscriber.
Good luck
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Newbie Q: SQL Redundancy

Hi, If I have two machine in a cluster
- if I install SQL on one will the installation replicate on the other or do I have to install both seperatly,
- if I have SQL operating on both nodes and I write SQL to one will it replicate over automatically (does it remove the need for SQL redundancy)
- would WebPages do the same.
sorry for the newbie questions but was finding it hard to get specific answers, any help would be appreciated.
Steve
SQL Cluster installation puts the binaries on whatever host nodes you
specify. You can add or remove nodes later if you need to. The clustered
data store is accessable from both nodes, but is arbitrated so that only one
host at a time owns it owns. The entire data store shifts to another node
during a failover. A failover appears like a SQL stop/start to the outside
world. If you have a SQL instance running on a different host node in the
same cluster, it will have a different set of databases and will appear like
(because it is) a separate SQL server.
I do not understand your question about WebPages.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:E7F75FFB-A439-4931-B7E5-80868A324720@.microsoft.com...
> Hi, If I have two machine in a cluster
> - if I install SQL on one will the installation replicate on the other or
do I have to install both seperatly,
> - if I have SQL operating on both nodes and I write SQL to one will it
replicate over automatically (does it remove the need for SQL redundancy)
> - would WebPages do the same.
> sorry for the newbie questions but was finding it hard to get specific
answers, any help would be appreciated.
> Steve
|||Setup will install SQL Server 2000 on both nodes. That is, it will install
the binaries and utilities on a local drive on each node and the data and
log files on the shared drive.
There is only one set of shared databses between the nodes. There is no
data redundancy; only high availability. So if you want data redundancy you
will need to use something like replication or log shipping.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Even in a SQL cluster, you have only one set of data (mounted by one node or
the other, but not both).
If you lose the storage solution or corrupt the data, you're toast. That's
where replication comes in.
Look at either NSI product (www.nsisoftware.com).
GeoCluster splits the storage under MSCS, so each node has its own copy -
although only one is active at a time.
DoubleTake replicates data between servers, and also from the active MSCS
node, to another server. This can be built in such a way that you could do
backups or snaps from the redundant server (which gives you some additional
restore and rollback capabilities).
jason
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:E7F75FFB-A439-4931-B7E5-80868A324720@.microsoft.com...
> Hi, If I have two machine in a cluster
> - if I install SQL on one will the installation replicate on the other or
do I have to install both seperatly,
> - if I have SQL operating on both nodes and I write SQL to one will it
replicate over automatically (does it remove the need for SQL redundancy)
> - would WebPages do the same.
> sorry for the newbie questions but was finding it hard to get specific
answers, any help would be appreciated.
> Steve