Moving DB from Test to Production - SQL server 2005

Question

Moving DB from Test to Production - SQL server 2005

poratips

Hall of Fame

Points: 3994
More actions
January 8, 2009 at 9:09 am

#197960

Hi,
I am new for SQL server and we have test database with normal setting and planning to move into production.
Currently we don't have much data (let's say 10-20 records, total 5 tables).
I have created previously created DEV database (Dev server) and taken back up and restored using SSMS into TESt server.
So I have now DEV and TEST database.
We have built a production server nad i need to migrate DB into Production server.
Should I use the same technique ot move DB from TEST to PRODUCTION?
What's the ideal configuration parameters nad othe thing i need to keep in mind while migrating/creating into Production?
Eventually DB will grow in production but i am not aware this ppoint that what should be the growth level.
But I am expecting to 50k to 100k rows in all five tables.
We will be getting data through .Net application using web service and incoming file is in XML Format and will be loading into RDBMS sql server tables.
Please let me know which procedure i should follow with ideal production configuration.
Thanks and appreciate!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12315 More actions · Answer 1

20 lines of test data - and 5 tables? Is this a school project?

If so - please research this yourself. If not - please post details as requested in Jack Corbett's sig with expected data volumes

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

GSquared SSC Guru Points: 260824 More actions · Answer 2

The first thing I'd do is do some testing with more rows of test data. If you're expecting hundreds of thousands of rows, and you test with 5-10, you will almost certainly miss performance issues that will crop up as the tables increase in size. There are lots of ways to generate thousands of rows of test data (I think RedGate has a product that will do it for you - they have a link at the top of this page).

Once you've tested with a lot of data, then clear out the test data, back up the database, and go ahead and move it.

But definitely test with more than 5-10 rows. You'll save yourself and others a lot of headaches later.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

poratips Hall of Fame Points: 3994 More actions · Answer 3

Thanks much!

I know that its not real amount of data but could you please let me know what should be the ideal configuration?

I know oracle has small/medium DB size parameters in init.ora but i don't know if sql server has this kind of recomendation?

Also database migration from tets to production is using back & restore through SSMS will be fine, right?

appreciate your response!

Thanks once again!

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Agreed that you NEED to generate some test data so that you can get some size estimates and ultimately you will need some data so that you can do some tuning with the queries that are going to run against this database. So, make that step 1.

As for using backup / restore for your migration, that is a fine solution and works well. You should start to consider your file layout for production and plan for taking steps to separate the data and log files (as well as tempdb). Do some research on file placements.

Not too sure about the oracle configuration comparison for SQL Server. You are going to need to ensure that you are using the correct version of SQL Server and that will be based on several factors, but primarily what features you will need. My guess is that you will not bump your head on the memory using Standard Edition but again, you need to do some research and testing to find out what your configuration should be.

You can obviously post back with detailed questions regarding your scenario once you have done some further testing. To ask anything further from the group here with the information you have it really pointless. Not trying to be harsh but there really aren't answers until you have done some testing and research. Know what I mean?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

poratips Hall of Fame Points: 3994 More actions · Answer 5

David,

I am completely agreed with your thoguhts and really appreciate your response.

I will try to make sure with some testing data but i was just trying to foind out what should be ideal configuration setting while migrating to production for starting and later you adjust your parameter when its growing.

Thanks,

poratips Hall of Fame Points: 3994 More actions · Answer 6

Thanks lot.

If someone have any standard production setting, i need just to check it out.

poratips Hall of Fame Points: 3994 More actions · Answer 7

I just got the information that each table will grow atleast 100k -200k rows in next year.

Is it this information is enough to estimate DB configuration?

And also we will have Cluster.

Please let me know if i can get roughly DB file size and other configuration parameter to just start up as i read that if i create DB with few MB size with auto growth then eventually IO will be problem?

Thanks once again!