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]