Endpoint Security Architecture Diagram

Posted by yrushka on October 23, 2012 (51)

I recently was involved in a major migration project which included the move of few database production servers from one location to another. It was a very big project which implied a lot of inner coordination between teams and scheduling, so that the impact for the end-users would be minimal. In case you are in the position of moving an SQL Server from one machine to another make sure you stick to the below presented checklist. The main purpose of the migration is to create a clone SQL Server with the same configuration, databases up to date, security and SQL Agent jobs in place. For this I always chose to restore both master and msdb along with all user databases. It seemed a lot easier than moving users/passwords, resolving orphaned users, updating default databases, updating system properties (sp_configure), migrating jobs, SQL Agent operators, recreating DB mail profiles, change data capture functionality, restoring partitioning information, and whole other specific databaseoptions that where once enabled. After the migration everything must go smoothly and with no errors, otherwise, the migration didn’t go successfully. You have to make sure you cover all points and when you make the switch in connection strings everything is completed from DBA side of view. Please if you find there is something to be added to the list below, feel free to comment and I will update this post.   I. Steps to do on the current Production server – [Server A] Check SQL Server properties – These must stay the same on the future server.   use master GO select SERVERPROPERTY ('Collation') select SERVERPROPERTY ('Edition') select SERVERPROPERTY ('InstanceName') select SERVERPROPERTY ('ProductVersion') select SERVERPROPERTY ('ProductLevel') select SERVERPROPERTY ('ServerName') Check global configuration settings – These must stay the same on the future server.   use master go sp_configure 'show advanced options' , 1 reconfigure go sp_configure go Check DatabasesIntegrity   It is necessary in order to be sure that restored backups on migrated server will be valid. DBCC CHECKDB ('DATABASE_NAME') Note down model database growing options.   This setting is important for every new created database. My strong advice is that you never leave the default setting because it can be a big cause for poor performance. The default setting is 1 MB per data file and 10% per log file – imagine what it means for a database to allocate MB after MB if it needs to allocate say 50 MB or grow a log file that is 10 GB in size. Note down physical path for master, model, msdb, tempdb databases data and log files.   use master go select name, filename from sys.master_files where database_id in (1,2,3,4) order by database_id Note down physical path for mssqlsystemresource data and log files.   SQL 2008: It should be in :\Program Files\Microsoft SQL Server\MSSQL10_50. \MSSQL\Binn\ For SQL 2005: same as master database location. Query to run on SQL 2008 and upperversions in order to find the physical location:   use master go select name, filename,dbid from sys.master_files where database_id in (32767) Perform Full backup on master and msdb     II. Steps to do on the future Production server – [Server B]   Install same edition/version of SQL Server on the new machine.   Keep in mind the following when installing:   Make sure the new server has mounted the same drive letters where system databases reside on old SQL Server. Otherwise, after the restore of master SQL will not be able to start because SQL will want to start the rest of system databases on physical paths from [Server A]. If you want to restore system databases then you need to keep the same edition (Standard, Entreprise, Business Intelligence) and version (ex: 2008 RTM, 2008 SP1, 2008 R2 RTM etc.). You cannot restore a backup of a system database (master, model, msdb) on a server build that is different from the build on which the backup was originally performed. An workaroundwould be, to replace manually the system databases files copied from [Server A] to [Server B]. This can be done only if the migrated server can be stopped. If not, then you must follow the procedure with “restore database….” If you want to restore only user databases, then you can install the same or upper edition/version. If you install a lower edition/version than the current Production one, you might not be able to restore some of the databases having higher builds features which won’t be supported on lower builds. Keep the same collation when installing SQL Server. Create a Domain/Windows user account that will be used to start SQL Services. Backup system databases – master, model, msdb – and put in a safe location… in case you want to recover them. Stop SQL Server. Copy all system databases files and add them to the safe location. Create the physical locations for master, model, msdb, tempdb, mssqlsystemresource databases noted down at steps I.5 & I.6 Give to the user running SQLFull security rights on the new folders created. Copy system databases backups made on [Server A] to [Server B] (step I.7). Stop SQL Server. Copy existing model, msdb and mssqlsystemresource database files from the installed location on [SERVER B] to the new created ones (step II.4). Afterwards you will be able to change the location for these databases. For now SQL will need old locations in order to load msdb, model, tempdb and mssqlsystemresource. Start SQL Server in single user mode. From an elevated cmd (started with administrator rights), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn” There where cases when other services like SQL Agent or SQL Reporting took the single one connection and prohibited the administrator to restore master. This error is given: Reason: Server is in single user mode. Only one administrator can connect at thistime. Before starting the SQL in single user mode, make sure you have stopped every SQL service and there is no one that will make a default connection to it (application, reporting)   cd [drive]:\folder_name sqlservr.exe -c -m Restore master database from the backup copied from [SERVER A] using a NEW cmd line started with administrator permissions or using . -- connect to SQL sqlcmd -SMACHINENAME\SERVERINSTANCE -E -- make sure you are connected to right server: select @@servername GO RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE; GO Exit the cmd prompt and Start SQL from Services (in multi-user) Now the system databases (except master) will be loaded from the new created paths. Check new files location for system databases. master will point to the right location. msdb, tempdb and model must be changed. If you will restore msdb you can specify at that time, the new location for the files. So here I will show how to move modeland tempdb. For msdb is the same.   select * from sys.master_files use master go Alter database tempdb modify file (name=tempdev, filename='[drive]:\new_location\tempdb.mdf') Alter database tempdb modify file (name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf') Alter database tempdb modify file (name=templog, filename='[drive]:\new_location\templog.ldf') Alter database model modify file (name=modeldev, filename='[drive]:\new_location\model.mdf') Alter database model modify file (name=modellog, filename='[drive]:\new_location\modellog.ldf') Stop SQL Server and move the model files from old location to new location. tempdb will be re-created on the new specified location at every SQL restart so no need to move the files. Start SQL and make sure that the system database point to the right locations.   select * from sys.master_files [OPTIONAL] Restore msdb database. I personally encountered problems running Database mail after msdb restore on SQL 2008R2. I ended up leaving the msdb orginal files and migrated all jobs by scripting, created msdb.dbo.cdc_jobs table (because cdc was in place as well) and re-configured database mail. Remove folders created at step II.4. Test some SQL users for connection. Linked Servers will not work because their credentials where encrypted with Service master key from originated server. In order to fix this you need to backup service master key from [Server A], give permission on the file to your user and copy to [Server B] to restore it.   -- on [Server A] BACKUP SERVICE MASTER KEY TO FILE ='[Drive]:\share\master_key' ENCRYPTION BY PASSWORD ='password' -- right click on file, properties, security, permissions: Give full permissions to your user. -- on [Server B] RESTORE SERVICE MASTER KEY FROM FILE = '[Drive]:\master_keymaster_key' DECRYPTION BY PASSWORD = 'password' force Change the server name in system tables. @@servername variable will point to old server. It must be changed.  -- check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found. SELECT @@servername EXEC sp_helpserver 'SERVER B' EXEC sp_helpserver 'SERVER A' SELECT srvname FROM sysservers where srvid = 0 -- DELETE old references to old servername. EXEC sp_droplinkedsrvlogin 'SERVER A', null EXEC sp_dropserver 'SERVER A' -- ADD new server name: [SERVER B] EXEC sp_addserver [SERVER B] , 'local' ,'duplicate_OK' EXEC sp_addlinkedsrvlogin 'SERVER B', 'true' -- RESTART SQL. [SERVER B] will replace the old server name. SELECT @@servername EXEC sp_helpserver 'SERVER B' SELECT srvname FROM sysservers where srvid = 0 On versions below SQL 2008, you have to update also the originating server name from sysjobs as well. On higher versions, the reference in sysjobs is made by server_id which is always 0.   SELECT * FROM msdb.dbo.sysjobs UPDATE msdb.dbo.sysjobs SET originating_server = @@SERVERNAME WHEREoriginating_server <> @@SERVERNAME III. Remaining Steps to do on the current Production server – [Server A] Perform Full backups for all user databases. It is a good idea to use backup compression and backup verification (RESTORE VERIFYONLY) Perform Differential backups. Copy all backups on [SERVER B]. Restore Full backups and differential backups on [SERVER B] with ‘NO RECOVERY’ option. Perform Transactional backups. Copy all tran backups on [SERVER B] and restore them using with ‘Recovery’ option. And a last step, after you restore all user databases, keep in mind to change databases owners to ‘sa’ user, otherwise you will receive errors of executing sys schema objects like these ones: The EXECUTE permission was denied on the object ‘’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider) use Database_Name EXEC sp_changedbowner 'sa' go