How to apply database design changes?

Hi all, I am not sure if this question has a one line answer or I have to
read an entire book on the subject. Here is my problem... I am developing VB
..NET based web app with MS SQL Server 2000 as backend. The beta version of
the system is already in use from the main server. Meanwhile I keep changing
the database design (including editing stored proc and/or views) on my
development server. Now how could I migrate the changes I have made to the
main server without loosing the data already there?
Any reference to an online guide would also be helpful.
Thanks in advance
Raj
Since you posted the question in .replication forum. I guess the first
question everyone has is:
Does the database set up for replication?
"Raj" wrote:

> Hi all, I am not sure if this question has a one line answer or I have to
> read an entire book on the subject. Here is my problem... I am developing VB
> .NET based web app with MS SQL Server 2000 as backend. The beta version of
> the system is already in use from the main server. Meanwhile I keep changing
> the database design (including editing stored proc and/or views) on my
> development server. Now how could I migrate the changes I have made to the
> main server without loosing the data already there?
> Any reference to an online guide would also be helpful.
> Thanks in advance
> --
> Raj
>
|||Jack, I was not sure initially where to post this question, so posted here. I
just created a publisher of my development database and selected all the four
objects (tables, stored proc, views, and UDFs) for publishing.
I do not want to publish the data, but only the schema. The replication
wizard gave me a warning that columns with INT IDENTITY property would be
converted to just INT, which, if happens, will screw up my database.
Any guidance would be appreciated
Thanks
"Jack" wrote:
[vbcol=seagreen]
> Since you posted the question in .replication forum. I guess the first
> question everyone has is:
> Does the database set up for replication?
> "Raj" wrote:
|||If this is the case, then I would not recommend Replication. Tables, views,
stored procedures, and functions all can create and alter using SQL script.
You just need to keep track of the changes you want to make, put them in a
script or scripts, then apply the changes to production database on regular
basics (i.e. once a week).
"Raj" wrote:
[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted here. I
> just created a publisher of my development database and selected all the four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Raj,
It seems either you got mixed up or I don't understand your problem.
1. What are you using replication for? Just to manage schema?
2. What type of replication are you using? My guess is you are using
Transactional.
3. How many servers are you managing in production?
4. Have you got your development and production servers mixed up? If yes,
bad idea!
Please clarify the above.
Normally if you want to deploy database changes from development to
production you would be keeping change scripts so you could apply them in an
ordered fashion, especially if table changes are involved. Adding columns
requires special handling when applying to replicated database, and cannot
be done using the conventional way. Please let me know your exact
requirements
Sorry for not being able to help more at this moment.
Raj Moloye.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted
> here. I
> just created a publisher of my development database and selected all the
> four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Thanks Raj Moloye and Jack.
I only need to migrate the schema changes from development server to main
server. I do no need to synchronize the data.
I only created replication agent for self-study (just to know what the heck
is it..). Based on your reply I guess replication is not the answer to my
problem.
You rightly mentioned that I need to place all the changes in a script, and
apply them to the main server regularly. I just do not know how to do it,
particularly the changes I make to the tables.
Please advise me the best way to achieve this.
Thanks again
BTW: could you also guide me to any online replication docoment?
"Khooseeraj Moloye" wrote:

> Raj,
> It seems either you got mixed up or I don't understand your problem.
> 1. What are you using replication for? Just to manage schema?
> 2. What type of replication are you using? My guess is you are using
> Transactional.
> 3. How many servers are you managing in production?
> 4. Have you got your development and production servers mixed up? If yes,
> bad idea!
> Please clarify the above.
> Normally if you want to deploy database changes from development to
> production you would be keeping change scripts so you could apply them in an
> ordered fashion, especially if table changes are involved. Adding columns
> requires special handling when applying to replicated database, and cannot
> be done using the conventional way. Please let me know your exact
> requirements
> Sorry for not being able to help more at this moment.
> Raj Moloye.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...
>
>
|||If you make changes to a table. For example, you added a column:
ALTER TABLE table
ADD column DATATYPE ........
As far as stored procedures, views, and functions. You can simple use the
ALTER command to update them unless your company has rules for those changes.
The difference between changing the schema of a table versus others is that
you don't want to change the entire schema of the table, you just want to
apply the necessary changes to it.
Book On Line provides a lot of informatioin on Replication already. This
forum is a great resource also especially there are some replication experts
checking this forum all the time.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Raj Moloye and Jack.
> I only need to migrate the schema changes from development server to main
> server. I do no need to synchronize the data.
> I only created replication agent for self-study (just to know what the heck
> is it..). Based on your reply I guess replication is not the answer to my
> problem.
> You rightly mentioned that I need to place all the changes in a script, and
> apply them to the main server regularly. I just do not know how to do it,
> particularly the changes I make to the tables.
> Please advise me the best way to achieve this.
> Thanks again
> BTW: could you also guide me to any online replication docoment?
> "Khooseeraj Moloye" wrote:
|||To replicate schema changes only, I'd recommend Redgate's
SQLCompare tool.
Rgds,
Paul Ibison
|||Thanks Jack,
I use enterprise manager to edit tables, views etc. I think it will be a
pain to record all the changes I make to the tables as a script manually. I
am probably making changes to the tables, views etc all the time, then how is
it possible/feasible to record them all in a script?
Is there any alternative?
"Jack" wrote:
[vbcol=seagreen]
> If you make changes to a table. For example, you added a column:
> ALTER TABLE table
> ADD column DATATYPE ........
> As far as stored procedures, views, and functions. You can simple use the
> ALTER command to update them unless your company has rules for those changes.
> The difference between changing the schema of a table versus others is that
> you don't want to change the entire schema of the table, you just want to
> apply the necessary changes to it.
> Book On Line provides a lot of informatioin on Replication already. This
> forum is a great resource also especially there are some replication experts
> checking this forum all the time.
> "Raj" wrote:
|||It is very important to record all the changes you want to make to your
production database even though it's a pain. That's why the DBA's like
myself still have jobs. :-) Views, stored procedures, and functions should
not be a problem. You can just right-click on the object from EM > All Tasks
> Generate SQL script. But when it comes to tables, I think it's better for
you to learn how to use T-SQL to make modifications. You can generate the
SQL script on the tables from EM also if you want to get yourself familiar
with the syntax first.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Jack,
> I use enterprise manager to edit tables, views etc. I think it will be a
> pain to record all the changes I make to the tables as a script manually. I
> am probably making changes to the tables, views etc all the time, then how is
> it possible/feasible to record them all in a script?
> Is there any alternative?
> "Jack" wrote: