Database Import/Export Question

I tried to export a database which includes tables and stored procedures from development environment to a production environment. For unknown reasons only the tables were copied whereas stored procedures were not copied and no error message shown what happened. Does anybody have a clue? I did the same export to an XP machine and it went through properly.How did you export the data? Did you use DTS?
Maybe you will find my previous post listing the steps I use to export objects to be helpful:http://forums.asp.net/1037418/showpost.aspx
|||Thanks. I got it. Don't know why the import/export said 100% complete but nothing showed up even I refreshed the stroed procedures in Enterprise Manager. And after a while (actually I went to pull someone to see the situation) the SP shown. Weird?|||

vljw8202 wrote:

Thanks. I got it. Don't know why theimport/export said 100% complete but nothing showed up even I refreshedthe stroed procedures in Enterprise Manager. And after a while(actually I went to pull someone to see the situation) the SPshown. Weird?

Enterprise Manager is often annoyingly unaware of schema changes. What I typically do is right-click on the server name and chooseRefresh, do the same for the Database node, the specific database, andthen the Tables node and any other node which should berefreshed. The new objects will then show up.
|||I see. Thanks for your advice.

Database growth excessive when changing varchar length from 50 to 100.

Hi all,

I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>

I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!

Why is this happening and how can I prevent this?

TIA

NozFx

Hi all,

Ok made two huge mistakes here. One I have large text data along side other fields that when changes are made the structure changes dramatically. Secondly I have full transaction logging in which all changes are logged. Going to have to make sure I learn my lessons here. Got all the sizes down now but I needed double to do that.

NozFx

sql

Database Environment Naming Production -vs- Development

I have been looking for some documentation that would support or reject
my opinion on Production -vs- Development naming conventions. I
believe that each environment should be housed on separate servers with
identical names, access, users, stored procs...... If you either
agree or disagree with this methodology, I would appreciate your input.

TIA,
BillWith the same access? In most situations I don't want developers to
have the same access to production as they have in development. I
pretty much never want my users to have access to development either.

Other than that, I would agree. I think that every item that you have
to change between development and production is one more item that may
get you when moving changes from development to production.

In my environments I usually have a QA or testing server as well. This
one will more closely mirror production so that moving changes from QA
to production involves no manual changes at all - everything is
automated. This helps to ensure that no unforeseen bugs get moved into
production as the result of an errant upgrade script. The upgrade
scripts are run on QA, which is identical to production at the start
(often synchronized by a backup/restore unless the data is sensitive or
too large for the QA server). If the upgrade scripts work successfully
on QA and testing the changes is successful then confidence is pretty
high that they will work correctly in production.

HTH,
-Tom.|||I agree with Thomas. You cannot / should not test on a Dev server. So
you need at least three environments as near identical as possible.
Developers don't get access to Production except to troubleshoot a
problem that you can't repro elsewhere (unfortunately that happens).

--
David Portas
SQL Server MVP
--|||Thank you all for your responses.
And the access does change for Developers, we grant SELECT access to
view potential data issues and we also have Production control to move
both Databases and Interface methods (Executables and Web) from
Development to Production. There has been a desire to create a three
tier type environment, as we cycle out Production equipment I am keeping
it for the Development environments, so in another year or so I should
have the equipment for the 'QA' level.

Thanks again,
Bill

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Willyerd (bwillyerd@.dshs.wa.gov) writes:
> I have been looking for some documentation that would support or reject
> my opinion on Production -vs- Development naming conventions. I
> believe that each environment should be housed on separate servers with
> identical names, access, users, stored procs...... If you either
> agree or disagree with this methodology, I would appreciate your input.

If we were do that in our shop, we would have to have a load of servers!

More generally, it depends on what your situation is. The above could
be a good idea for in-house applications, where there is exactly one
production server. (We develop a product, and we have one development
environment and one test environment for each customer and for each
version in production, test and development. That's a lot of databases.)

I would say that the key point is that you have separate servers.
Testing on a second database on the production machine can lead
unpleasant incidents, because test reveals a query with a poor query
plan.

Wether the database should have the same name? Of course, it helps,
but what if you need more than one testing environment? One of our
customers at one point had 3-4 test databases, all for our application.
I can reveal that they did not have four test servers. Thus, it is a
good idea to make it easy to switch database in the application.

As for the same stored procedures etc, this is best achieved by having
a version-control system as the definition of your system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

If your servers have identical names then they can not be on the same
network!!

John

"Bill Willyerd" <bwillyerd@.dshs.wa.gov> wrote in message
news:1114176300.794189.215830@.g14g2000cwa.googlegr oups.com...
>I have been looking for some documentation that would support or reject
> my opinion on Production -vs- Development naming conventions. I
> believe that each environment should be housed on separate servers with
> identical names, access, users, stored procs...... If you either
> agree or disagree with this methodology, I would appreciate your input.
> TIA,
> Bill

Database Diff?

I have 2 databases:
1 from production and 1 from development.
None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).
Any Ideas on how to do this?A quick method is to compare selects from sysobjects, syscolumns, and sysindexes.

blindman|||Then give the output of those queries to the developers so they can sift through them. Only proper, I would imagine.|||A quick, first run is to compare the date modified of the objects. Of course this does not compare contents. I've done it this way and it wasn't as bad as it sounds. Good luck.|||...and a quick way to identify modified procedure code is to compare checksum(syscolumns.text) between the two databases.

blindman|||if this is recurring task and if your department has a few dollars to spare (as we all have ...), you can use software for this, e.g. http://www.red-gate.com/sql/summary.htm|||SQLDiff by ApexSQL is a good tool too. There are some free database compare scripts at http://www.sqlservercentral.com/. Just look in their script section on the site.|||Another way - create db scripts (use DMO) and compare by FC.exe (included in W2000).

database design question

Hi!

We are designing a big database with multiple parts (like Human Resources, Sales, Production) etc. in Sql server 2005. Should we devide the main parts into separate databases, or should we put all tables in a single database and use schemas to group the parts (like the AdventureWorks sample database?). Is there a general recommendation here?

We are considering multiple databases so one more easily could move one database to a diffrent server if needed.

Hi,

I would suggest you put all the tables in a database and devide them with schemas, just as AdventureWroks shows. Putting the tables across will result in many problems, and performance hits.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

If we devide into multiple databases, we will try to minimize the references between them. After all they represent different services within the enterprise solution.

I realize that the administrative tasks increase as the database count increase, but why would it be related with performance hits? If the traffic to one of the databases becomes an issue, we can easily move it to a new fresh server and thus gain performance.

Could you also be more specific on what other problems you refere to.

Thanks!