creating replication and not using default database name

We currently have transactional replication from our ERP system to our
datawarehouse. Our ERP system is being upgraded and will have a new database
name. I am going to break and recreate replication to our datawarehouse. I
would like to change the default database name to be the same as our current
(ie: the old version) database name so we don't have to go update all our
ODBC connection strings. Is there any reason why I should not do this? Any
problems this may cause? Also, I was thinking of not even dropping the old
database on the datawarehouse, but just simply replicating the new table to
this old database. Any red flags there? Thanks so much.
I'm confused, I realize your upgrade path requires you to rename your
database name on your ERP system.
How does the ODBC connection string fit in here? Is it pointing at the ERP
system, or the data warehouse? Replication couldn't care less what the name
of the subscriber database name is. So this shouldn't be a problem. You may
have to edit the publication scripts for the new database name.
One trick for you, the SQL Server account or the NT account used in the ODBC
DSN will have a default database. You should be able to change this on the
publisher or subscriber and avoid having to modify the ODBC DSN's.
You should also be able to leave the old database intact with the same name
on the dataware house/subscriber. Replication will fix the tables there the
way it wants them to be - which will likely be ok for you.
Things to watch out for
1) PK's are replicated as unique indexes which are very similar to PKs (only
they allow a single null). When you are creating your publication and get to
the specify articles dialog box, click on the browse button, snapshot tab,
and select include DRI to replicate the PK's as PK's and pray to your God
sysdepends is accurate on the publisher.
2) triggers on the subscriber will be dropped unless they are replicated
from the publisher (in the snapshot tab as well)
3) normally data warehouses require different sets of indexes on the
subscriber; you may have to rebuild these.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> We currently have transactional replication from our ERP system to our
> datawarehouse. Our ERP system is being upgraded and will have a new
database
> name. I am going to break and recreate replication to our datawarehouse.
I
> would like to change the default database name to be the same as our
current
> (ie: the old version) database name so we don't have to go update all our
> ODBC connection strings. Is there any reason why I should not do this?
Any
> problems this may cause? Also, I was thinking of not even dropping the
old
> database on the datawarehouse, but just simply replicating the new table
to
> this old database. Any red flags there? Thanks so much.
|||Sorry for the confusion. I was referring to all of our ASP code that hits
the datawh and has the db name in the ODBC connection. It's just a reason
why I want to keep the old name, it doesn't have anything to do with the
actual replication.
Thanks for all your answers. I don't really get #1 though. I've never done
that when I've started replication before. Is it necessary?
"Hilary Cotter" wrote:

> I'm confused, I realize your upgrade path requires you to rename your
> database name on your ERP system.
> How does the ODBC connection string fit in here? Is it pointing at the ERP
> system, or the data warehouse? Replication couldn't care less what the name
> of the subscriber database name is. So this shouldn't be a problem. You may
> have to edit the publication scripts for the new database name.
> One trick for you, the SQL Server account or the NT account used in the ODBC
> DSN will have a default database. You should be able to change this on the
> publisher or subscriber and avoid having to modify the ODBC DSN's.
> You should also be able to leave the old database intact with the same name
> on the dataware house/subscriber. Replication will fix the tables there the
> way it wants them to be - which will likely be ok for you.
> Things to watch out for
> 1) PK's are replicated as unique indexes which are very similar to PKs (only
> they allow a single null). When you are creating your publication and get to
> the specify articles dialog box, click on the browse button, snapshot tab,
> and select include DRI to replicate the PK's as PK's and pray to your God
> sysdepends is accurate on the publisher.
> 2) triggers on the subscriber will be dropped unless they are replicated
> from the publisher (in the snapshot tab as well)
> 3) normally data warehouses require different sets of indexes on the
> subscriber; you may have to rebuild these.
> --
> 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
> "AW" <AW@.discussions.microsoft.com> wrote in message
> news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> database
> I
> current
> Any
> old
> to
>
>
|||Probably not. Some apps do want to see PK's. Not many of them though, and if
they do they are probably badly written.
I think you should be ok changing the name of your ERP database, but keeping
the old name of your DataWarehouse database.
Next time you build an ODBC connection, don't specify a database name, or
use default database account to handle the database connection for you.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:13877D10-371E-4632-842F-35192B8E4CC5@.microsoft.com...
> Sorry for the confusion. I was referring to all of our ASP code that hits
> the datawh and has the db name in the ODBC connection. It's just a reason
> why I want to keep the old name, it doesn't have anything to do with the
> actual replication.
> Thanks for all your answers. I don't really get #1 though. I've never
done[vbcol=seagreen]
> that when I've started replication before. Is it necessary?
> "Hilary Cotter" wrote:
ERP[vbcol=seagreen]
name[vbcol=seagreen]
may[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
name[vbcol=seagreen]
the[vbcol=seagreen]
(only[vbcol=seagreen]
get to[vbcol=seagreen]
tab,[vbcol=seagreen]
God[vbcol=seagreen]
datawarehouse.[vbcol=seagreen]
our[vbcol=seagreen]
this?[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]

Creating New Views on SQL 2000

Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
new view in Enterprise Manager which pulls data from a linked server's
database. When I write the view...

CREATE VIEW dbo.VIEW1
AS
SELECT *
FROM LinkedServerName.DatabaseName.dbo.TableName

and I click on the run button it changes to:

CREATE VIEW dbo.VIEW1
AS
SELECT *
From LinkedServerName.DatabaseName.dbo.TableName TableName_1

I can still see the correct results, but the users can't- Once I
remove the TableName_1 they can see the results.

When I tried creating a view and did not use a linked server,
everything worked fine. Anyone have any answers?RJMAL (rjmal@.connecticare.com) writes:
> Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
> new view in Enterprise Manager which pulls data from a linked server's
> database. When I write the view...
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> FROM LinkedServerName.DatabaseName.dbo.TableName
> and I click on the run button it changes to:
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> From LinkedServerName.DatabaseName.dbo.TableName TableName_1
> I can still see the correct results, but the users can't- Once I
> remove the TableName_1 they can see the results.
> When I tried creating a view and did not use a linked server,
> everything worked fine. Anyone have any answers?

I was not able to reproduces this, because I was not even able to create
a view that accesses a linked server through Enterprise Manager. I came
as far that I saw the added alias, though.

Really why Enterprise Manager adds the alias, I don't know, but neither
do I understand why the alias would affect the common users. You say
they don't see the correct results - but what do they see? An error
message? No rows at all? Too few rows? Garbled data? From what sort of
application, to the users access the view?

Anyway, try using Query Analyzer to create the view instead. In my
opinion, Enterprise Manager plays too much behind your back to be
trustworthy.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp