Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-06-18 : 01:38:03
|
| Hi,I have a few questions on Migration of new database server?We are in a process of rebulding/replace the exisiting db machine with a new machine with a better H/W.Here are the existing system details.-------------------------------------MachineName : ABC(say) and applications/Linked Servers are pointing to ABC as of now.RAM: 3 GBOS: Windows 2000 Server SQL:SQL 2000 Standard Edition with service pack 3CPU:2New Machine configuration details -------------------------------- MachinName : XYZ (Later after testing it will be replaced as ABC)RAM:12GBOS:Windows 2003 ServerSQL:SQL 2000 Standard Edition with service pack 3CPU:8Given the above details we need to prepare a checklist for the migration process.I am listing out what all i need, please tell if the path am following is correct or not.Also, suggest if am missing anything.1. Take Full backups of all System and Application databases on "ABC" machine.2. Script all logins 3. Script all jobs 4. List out all Maintenance plans and their schedules5. List out all Linked server(s) currently being used.6. Check the service pack7. Check the Edition8. document the existing server settings so that it should match the same (collation,version,...)Migration process---------------------1. Migrate logins2. Restore all App databases3. Fix the orphan users4. create the jobs5. create maintenance plans6. create Linked Servers 7. perform checkdb on all databases8. rebuild indexes 9. update the statistics 10.move all the DTS packages 11. Point the application to new server 12. Testing ....Is the above proccess in correct or am missing anything????Questions---------1. when replace the old server with new server, do we need to use cmd like sp_dropserver and restart the sql server as far as database is concerned??? Because we know that once everything is fine , IT Folks will rename the new server with old server name. Right? So, what all the checks we can make a DBA to ensure the renaming is not a problem and it went well!!! What comands can i use to cross check to ensure what we are doing is everything is fine(ping with IP also test with ping with machinename).2. How to check if any Firewall is blocking or causing problems? If so, how to fix it.3. Can we take advantage of AWE option for SQL 2000 Standard Edition in ccombination with Windows Server 2003, Enterprise Edition OS???4. I do have a doubt in mind, dono whether to ask or not, why we are re-creating logins with the generated scrpt from the source server to destination server ,can't we restore the master database from the source to dest server?? The same with the case for Jobs. we normally take the scripts and run it on new server , so that it makes entries in MSDB database?? What is the reason behind for following this practice??? Is there any specific reasoning behind that? Frankly speaking, i haven't tried restoring the system database on new server but this was question actually pricking my mind :-)5.Is there anyother server level settings we can make for better performance ??6. I would like to also ask a question that what all documents normally expects from a DBA during the migration process from one server to another server?7. Can we move the tempdb to a different drive for better performance ?8. Can we change the database growth percent in Autosize option?? if so, what measures we need to take / what ground work / what analysis we can make to change the autogrowth to so much %??? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-18 : 05:29:24
|
| 4. You could try stop SQL Service, rename master.mdf / ldf and copy over master from old machine. Must be the same Service Pack etc. and drive assignments will need to be the same too.If it doesn't work stop SQL Service amd copy the originals back5. Why you sticking with SQL 2000? New machine migration is a golden opportunity to upgrade SQL version. Databases can stay on old server until regression testing done on new server and migrate "when ready". Compatibility Mode 80 will probably work for anything that has code changes needed for native Compatibility Mode 100 and where you don't want to make any code changes.The extra memory usage would probably make a significant difference to performance (STD SQL 2008 will use all memory that operating system can make available - you would need something newer than Windows Server 2003!)7) Good idea. Another drive for backups would be good too8) Recommend you use fixed size growth, not percentage (especially with SQL2000 - a large database growth will stall all queries to the database, on a busy server we used to get all-query-slowdown for 20 minutes until we changed the autogrowth to a modest number of megabytes, rather than 10% which was many-megabytes). Set it to what you need for a week or two minimum. 25- 50 autogrowths a year should be OK. Or if you have the disk space expand the files to what you need for the year.N!!) We transfer using:Full backup on old machineRestore on target machine WITH NORECOVERYHolding page up, old server database to single user / restricted user / read-only etc Tail TLog backup (or DIFF) on old machineRestore Tlog to new machine WITH RECOVERY, unset Single user etc. on target machinePoint APP at the new serverRemove "holding page"so the actual switch over typically takes less than 5 minutesMore details here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44537#145142 |
 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-06-19 : 10:23:53
|
| Thank You Kristen!So many things to understand... |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-06-21 : 10:31:18
|
| Hi,Here are some more areas where i am seeking for answers.I have 15 processors running on my new machine. 12 GB RAM. And the new server is completely dedicated to database server. The SQL Server Standard Edition is installed on the new server along with SQL Service pack 3.1)I have gone through the instance properties of the SQL instance and found the below by default after installation.Under Processor Tab, i can see all the CPU's are checked by default.Max worker threads set as 255 (default)Boost SQL Server priority on windows is unchecked.USe Windows NT fibres unchecked.In the same tab, For parallelism , use all processors radio button is selected.Here, i have a question. On what basis we can set this affinity mask or number of processors for parallelism is decided???Is that a advantageous to use all processors for parallelism??? or we can configure only some processors saying -- use only 4 processors out of 15 or so.... On what basis we can reconfigure this option or else shall i leave it as default option i.e. use all processors.2) Similary, i have gone through the Memory tab. Total Avaiable memory on the machine = 12 GB OS is Windows 2003 Server Enterprise Edition , Service pack 2. PAE is enabled. SQL Server 2000 standard edition with SP3 is sitting on the machine. Memory tab contains the following values Dynamically managed memory is selected Min : 0MB Max is set to 12 GB i.e total available memory. Now my doubt is whether is SQL server can utilize this additional memory ???? Before that, can we assign all the memory to sql server ??? I have checked the AWE option but it is not enabled sp_configure name min max config_value run_value awe enabled 0 1 0 0 So, can i enable the AWE option and take advantage of additional memory ?? Also, to my knowledge since sql server is 32-bit,we have a limit upto maximum upto 4 GB it can address. right??? remaining it is of wastage. i beleive!!! correct me if am wrong. How much memory can i utilize from the given OS (Windows Server 2003 Enterprise Edition) for SQL Server 2000 Standard Edition?? boot.ini looks as follows ---------------------------[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetectWhat all modifications can i make for optimal utilization for this server???????Thanks in Advance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-21 : 13:34:32
|
| "Before that, can we assign all the memory to sql server ???"I think you need to reserve some for the O/SI can't remember how much for SQL 2000, but in SQL 2008 I think it is 1GB per CPU ... on that basis you would need to reserve 15GB for the O/S ... which is not going to work on your 12GB machine!Have you got 15 CPUs? or just multiple cores / CPU (which I doubt as you have an odd number)I still don't understand why you would install fancy new machine and not use SQL2008 and modern Windows version so you can take advantage of the extra memory |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-06-21 : 14:08:11
|
Hi Kristen,As you said, SQL 2008 migration is going to happen very soon. But still they are some few management decision making being done and would take some time. Meanwhile, the exisitng system for production is very down graded and having minimum resources. So, they immediately want this to happen at first phase.So, coming to CPU's is there any bench mark for setting the CPU's??I keeping 2 GB for OS... That's should nt be a problem.Thanks for all the immediate support!!!  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-21 : 14:55:40
|
| I can't remember how to do AWE memory management for SQL 2000, but if SQL can only use, say, 4GB absolute total on that machine then there will be plenty for the O/S I think ? |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-06-22 : 02:23:42
|
| I right click the properties of the sql 2000 instance and tried to make changes in the DATABASE SETTINGS properties tab,and Processor tab but the changes are not being into effect. Do we need to change these from command line only??Even After pressing OK button also, it didnt prompt me for a restart of sql server. For memory tab ichanged , it asked me for restart of sql service |
|
|
|
|
|
|
|
|
Database server migration checklist