July 18, 2008 at 10:00 am
We have purchased a 64 bit server to upgrade our production box. I could use a step-by-step guide to follow to make this transition smooth. Also, we have two 32-bit web servers (Reporting Services Report Servers) pointed to the original 32 sql server box. Can we still use the 32 bit web servers or will we need to purchase 64 bit web servers to synch with the 64 bit sql server?
Any help is much appreciated.
July 18, 2008 at 11:06 am
It's nice that you can start clean. There are probably several different suggestions that you could get but I would recommend building out the new 64Bit box with SQL Server 64 Bit Edition . Script out logins / passwords (see this site for Scripts to do that) and get those created on the new server. I would actually go as far as taking the most recent full backups after you built it out and restore those there just to get the process solidified and to perform some generic testing. You could also take this "testing" time to build out your backup plans, etc. Then I would schedule the downtime and use backup / restore to move the databases from the old server to their final home.
As for Reporting Services, the 32-Bit version running on your web server should still be able to connect to the 64-Bit database server to retrieve data. You will have to walk through the configuration wizard to point to the new database location on the new server for the ReportServer and ReportServerTempDB databases.
If you are planning on installing Reporting Services on the 64 Bit server then ensure you are using the 64 Bit SQL Server install. Trying to get the 32 Bit version to work on 64 Bit OS is no fun with many difficulties coming up with the .NET layer configuration.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 19, 2008 at 1:48 pm
you would need to move the databases first otherwise when the script runs to apply the logins it will most likely fail as it wont be able to set the users default database because it wont exist.
script off logins
script off database file path and use these to create attach statements for the databases
detach db's and copy disk files to the required locations
apply the logins
set up any custom sql port numbers, etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 19, 2008 at 9:14 pm
Perry, You would be correct about the logins. I make it a policy to leave default DB as master to avoid other issues in the event that the default is not online so I often don't consider that. Thanks for adding that!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 20, 2008 at 1:01 am
On the production servers i have migrated there have always been a substantial amount of windows\sql user logins that have a default database set. Just the way i prefer to do 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 21, 2008 at 2:34 am
If your Prod environment is 64-bit, then your pre-production environment also needs to be 64-bit. Ideally everything from Dev onwards needs to be 64-bit - the last thing you want to find is a 32-bit / 64-bit incompatibility after you have put something live. Be aware that any DLLs, etc that remain at 32-bit need to go in the \SysWOW64 folder instead of the \System32 folder when you move to 64-bit.
IMHO you should treat the upgrade to 64-bit in the same way you treat a major application upgrade. Check everthing works at 64-bit in Dev, then UAT, then Production. Almost everything will work unchanged, but you may find issues with some components. This does important tests of your deployment procedures so that things go in the right folders in 64-bit, as well as giving you 2 oportunities to find any 32-bit/64-bit issues before you hit Production.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 21, 2008 at 3:16 am
I would also suggest putting in a DNS Alias for your SQL Connection String; i.e. SQLReporting > 10.0.0.1; that way if you ever need to migrate your database out to another box; all you have to do is move the DB and check/reset permissions.
HTH
July 21, 2008 at 4:12 am
Watch out for any users from your old box who had non default language settings, when you script them onto you new hardware the language settings may be lost and then the phone will ring once their applications get confused over things like date formats.
July 21, 2008 at 8:07 am
Thank you everyone for your suggestions. I am typing up a plan that I will post when complete. However, I have another question...
The new server will have the same name as the old server when I bring up the new server. Now, even though this sounds relatively simple, I am sure it is not as easy as:
1. Take the old server (PROD) offline
2. Rename the old server (i.e. PROD_old)
3. Rename the new server(from PROD1 to PROD)
4. Bring up the new server.
Is it this simple or are there other steps to take?
Thank you for all of your help.
July 21, 2008 at 8:13 am
July 21, 2008 at 8:17 am
No. We currently do not have the resources for replication or mirroring.
July 21, 2008 at 9:39 am
That makes it pretty easy then. FWIW if you have the new server ready to go you've got a great opportunity to restore backups from your current production box and do some testing against the x64 box to find out if there are any problems. I've done an x86 to x64 migration 6 times in the last few months and have had no problems at all but it's good to test if you can to be sure. There shouldn't be an issue using the x86 reporting servers to talk to the x64 server. As previously stated by DavidB you may have to walk through configuration options post-move.
Assuming you're only moving OLTP databases here's what I would do. I did not include any steps pertaining to replication since you said it's not in play in this scenario:
1) Configure OS and install SQL on new server, then configure SQL appropriately (memory, ports, tempdb location, etc). Couple of things to keep in mind:
- Make sure to explicitly set the max server memory on x64 or else SQL will take all it can get and leave nothing for the OS.
- Don't forget about any custom extended stored procedures (e.g. LiteSpeed or RedGate), DB mail configuration, and server settings that deviate from the defaults (e.g. xp_cmdshell enabled)
- On your data drives create primary disk partitions with 64K offset and format with 64K block size (http://sqlblogcasts.com/blogs/ssqanet/archive/2008/04/28/sql-server-2005-and-disk-drive-allocation-unit-size-to-64k-any-benefit-or-performance.aspx)
- Configure SQL for fast database file creation (http://www.sql-server-performance.com/articles/dba/Fast_Database_File_Creation_p1.aspx)
- Apply all the other best practices that you can. You've got a golden chance to fix anything at the OS\server level that's wrong since you're starting from scratch on the new server.
2) Script out all logins (http://support.microsoft.com/kb/918992) and server level permissions (script below) and apply to the new server. Before applying the logins script you may need to change users' default databases to master if they point to something else as that something else might not exist yet.
[font="Courier New"]SELECT server_permissions.state_desc
+ ' ' + server_permissions.permission_name
+ ' TO [' + server_principals.name
+ ']'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S','U','G')
ORDER BY server_principals.name, server_permissions.state_desc, server_permissions.permission_name[/font]
3) Script out all jobs from current live box and keep handy for post-move.
4) Save SSIS packages (if you have any) and keep handy for post-move.
5) Take all applications that access the server offline, including SQL jobs (best to just stop and disable them). Kill all open connections except yours, or better yet figure out where the connections are coming from and shut them down gracefully. You don't want data changing on you in the middle of your move.
6) Move databases. You've got two choices here:
- Backup from current server and restore to new server (taking into account drive letters, paths, etc.). You can potentially reduce downtime at the time of the move by restoring a full backup ahead of time and when you do the move just take a differential on the current server.
- Take DB offline, copy data and log files from current server to new server, and attach the DB to the new server. The advantage to doing it this way is that with the DB offline you know data isn't going to change on you. This works best if you have fast connectivity (e.g. gigabit) between servers or the databases are small enough that a copy isn't going to take too long.
7) Change users' default database if necessary
8) Since you mentioned this was part of your plan...rename server, then run scripts to change server name in SQL:
[font="Courier New"]sp_dropserver old_name
GO
sp_addserver new_name, local
GO[/font]
9) Apply job scripts from step 3. If any of them were writing to files ensure that file paths exist on the new server.
10) Apply SSIS packages from step 4. Double check file paths, server names, etc. when you're applying them.
11) Bring applications back online when you're satisfied that the new server is up and the databases have been moved properly.
12) If your apps do bulk inserts and there's delegation involved you may have to double check your Active Directory settings and run the setspn utility. (http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx)
13) Be prepared for all of the other stuff no one thought of...because there's always something you forgot about.
Hope this helps. If I think of anything I missed I will post another reply. Good luck!
July 21, 2008 at 9:48 am
Michelle Potts (7/21/2008)
Thank you everyone for your suggestions. I am typing up a plan that I will post when complete. However, I have another question...The new server will have the same name as the old server when I bring up the new server. Now, even though this sounds relatively simple, I am sure it is not as easy as:
1. Take the old server (PROD) offline
2. Rename the old server (i.e. PROD_old)
3. Rename the new server(from PROD1 to PROD)
4. Bring up the new server.
Is it this simple or are there other steps to take?
Thank you for all of your help.
you would be wise to make sure the new server is appropriately named before installing SQL Server. Build it with the old name PROD and disconnect the network cable until you're ready
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply