Oracle Database Duplication

I’ve noticed the conversion rate on my muskie hookups has not been good this year. I have hooked at least four muskies this year and have only landed one of them. The other three were hooked for a couple seconds at most, and all three threw the lure while jumping out of the water. Hooking up with a muskie is an exciting moment, and it can be tough to remember everything you need to do when it happens. In my case, I think I haven’t really been giving a good hookset. For some reason when using a long, stiff muskie rod my first instinct is not to give a big, sweeping hookset – but that is exactly what is needed. For the rest of this year’s fishing season, I’m going to really focus on giving fierce hooksets whenever I feel a bite. The important thing to remember is that practice makes perfect.

Me on the lake
I’m mainly a SQL Server DBA. I learned to be a DBA on SQL Server, so all their standards and methods make perfect sense to me. Recently, I had to roll out a test version of our Oracle server to a new server. I’ve had some Oracle training, and I also get to do Oracle tasks about every three months at work, but I don’t work with it frequently enough to really learn what I am doing. In this instance, I was able to figure out the correct steps, but if I don’t practice it, it won’t stick. If I have to try again in a year without any practice in between, it’ll go smoother than this time (my first time), but I’ll still have to hobble my way through it.
Before I go through the steps, I want to point out two areas of frustration on this process. The first is how unbelievably complicated this process is compared to how simple it is when using SQL Server. Restoring individual databases between instances in SQL Server couldn’t be any simpler – it’s basically just plug and play. Restore a backup or attach a copy of the data files and you’re good to go. In the case of trying to make a copy of an entire instance, you’d also need to restore the master, msdb, and possibly the model system databases, which is a bit more complicated, but nothing like trying to get this to run with an Oracle database. Secondly, all of Oracle’s documentation is centered around non-Windows operating systems (particluarly Linux). I realize that these operating systems are used much more commonly with the Oracle database, but when the Oracle support community’s usually response to help requests is a link to a big, long page of documentation, that documentation had better give me the info I need no matter what OS I’m using. For example, Oracle’s documentation on database duplication neglects to list an important step for Windows users – creating the auxiliary database’s service! This may be common knowledge to many full time Oracle DBAs, but for us part timers it isn’t. It’s hard to want to search through the documentation for answers instead of using Google when the documentation is missing key steps.

Following are the steps I took to duplicate the database to the new server. Make sure you are trying to duplicate to the same version! I first tried a 11.2.0.3 to 11.2.0.1 duplication and it failed causing me to have to start all over.

Take a full backup of the database you wish to copy.  I did my backup to a shared folder that was accessible to both servers.  Make sure to include a backup of your control file.

All the rest of the steps are done on the server with the auxiliary database.

Create a password file on the auxiliary server.  Put the password file in the ORACLE_HOME/dbs folder.

C:\orapwd file=D:\app\oracle\product\11.2.0\db_1\dbs\pwdtestsid.ora password=syspass entries=10

Make sure to make the password the same as the sys password on your target database.

I first attempted to do a duplication by connecting to the target database, but was not able to make that work so I used the database backup instead.  Since I was trying to connect directly to the target from the auxiliary, had to create an entry in TNSNames.ora:

# Added to the tnsnames.ora
TARGETDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hos.domain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

Create a pfile for the auxiliary database. This will usually only need to have the name of the auxiliary database. I also needed to include some parameters for changing the file locations since the auxiliary server had different drive letters than the target server.

db_name='testsid'

# Convert file names to allow for different directory structure if necessary.
DB_FILE_NAME_CONVERT='C:\APP\ORACLE\ORADATA\TARGETDB\','F:\Oracle_DB_Files\testsid'
LOG_FILE_NAME_CONVERT='D:\APP\ORACLE\ORADATA\TARGETDB\','F:\Oracle_Log_Files\testsid'

Now here’s the part that Oracle’s documentation leaves out. Create a service for the auxiliary database by using oradim:

oradim –NEW –SID testsid -INTPWD syspass

Again, use your target’s sys password. If it already isn’t started, start the server’s listener, then add the auxiliary instance to the listener.ora file. Reload the listener so it can point connections to your auxiliary database.

lsnrctl
start
reload

Here’s what the listener.ora file should look like:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = testsid)
      (ORACLE_HOME = E:\oracle\product\11.2.0\dbhome_1)
	  (GLOBAL_DBNAME = testsid)
  )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hos.domain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Connect to the idle, empty auxiliary database with SQLPlus. You may have to start or restart the service through the windows administration control to be able to connect. Once connected, shutdown immediate and then startup in nomount mode.

SQLPlus /nolog
Connect sys/syspass@testsid as sysdba;
Shutdown Immediate;
Startup Nomount;

The idea is to get the auxiliary database started in nomount mode. Next go into rman to run the actual duplication.

rman
connect auxiliary sys/syspass@testsid
RUN
{
SET NEWNAME FOR DATAFILE 1 TO 'F:\Oracle_DB_Files\testsid\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\Oracle_DB_Files\testsid\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\Oracle_DB_Files\testsid\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\Oracle_DB_Files\testsid\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\Oracle_DB_Files\testsid\AppFile.DBF';
DUPLICATE TARGET DATABASE TO testsid
BACKUP LOCATION '\\NAS\OracleSQLBackups\TargetDB\TestLoad'
SPFILE PARAMETER_VALUE_CONVERT 'c:\app', 'e:'
SET DB_RECOVERY_FILE_DEST='E:\Oracle\fast_recovery_area\testsid'
SET CONTROL_FILES='E:\Oracle\oradata\testsid\control01.ctl','F:\Oracle_DB_Files\control02.ctl'
LOGFILE
GROUP 1 ('E:\RedoLogs\Redo01.log') SIZE 1 G REUSE,
GROUP 2 ('E:\RedoLogs\Redo02.log') SIZE 1 G REUSE,
GROUP 3 ('E:\RedoLogs\Redo03.log') SIZE 1 G REUSE;
}

For me, it took well over an hour to finish duplicating the 150 GB database to the second server. Once finished, I had a complete duplicate of my target database into the auxiliary.

Advertisements

Why is TempDB Filling with Internal Object? Part 2

Last weekend, my brother-in-law and I fished a couple lakes in northern Wisconsin in anticipation of a tournament we will be competing in next month.  We did very well on the first lake, seeing several fish and hooking up with one of them, but the second lake we didn’t even see a fish.  The fish that we did hook up with has been weighing on my mind quite heavily.  My brother-in-law had just moved us to our second spot of the morning.  He fired off a cast while I was changing to a lure that was more appropriate for the location we were fishing.  Within a few seconds of his lure hitting the water a large muskie had come up and hit the lure.  He set the hook solidly and was soon fighting a very energetic fish.  The fish was only hooked in the beak and had a good chance of shaking the lure before we got it in the net.  The muskie darted through the water, moving left and then coming back right.  The second time we got it to the boat, I lowered the net into the water and stabbed toward the fish.  It, however, wasn’t ready to be netted.  It turned away from the net and, in the process, the hook slipped out.  As it swam away I pushed the net after it and got it halfway in, but I couldn’t keep up and it swam back down into the depths.  Just seeing such a large fish (I figure it was about 45″, based on the multiple views of it I got) was very exciting, but failing to get it in the net was crushing.  In retrospect, I did one important thing wrong.  I didn’t wait for my brother-in-law’s command to attempt the net job.  Since it was his fish, I should have waited until he said “Now!” to attempt to net the fish.  There is a chance the fish would have shook off before we got it into the net, but that was his call to make.  In the excitement and chaos of the moment, the thought didn’t even cross my mind.  I feel bad for having cost him the successful catch of a nice muskie, but it did teach me an important lesson.  I just wish the lesson could have been at my own expense instead of someone else’s.
In part 1 of this post I described how an upgrade of a third party application was causing problems in TempDB by allocating TempDB objects and not deallocating the objects via the code. Since the connections were staying open, TempDB continued to use up space until it ran out and crashed the SQL Server service. Here in part 2, I will describe how I found exactly what exactly was sitting in TempDB and what was causing the objects not to deallocate.
I began by considering the results of the SQL statement described in my part1. I noticed that several spids seemed to often allocate tempdb internal object space without deallocating it, while others deallocated it just as I’d expect.
20160922-fix3

I decided that if I could capture what those spids where doing when they allocated more space, I would be able to find code that I could provide the application support staff to prove they were not deallocating objects properly. My plan was to pick a spid or two that appeared to be consistent offenders, such as 213 above, and run a trace on them. At the same time I planned to use some SQL that would show internal objects being allocated and deallocated in TempDB. Microsoft has provided trace flag 1106, which logs info like this to the sys.dm_os_ring_buffers dmv when it is turned on. However, this trace flag has the potential to grind database processing to a halt, and should only be used in production with great care (Microsoft recommends not using in production at all). Once this is turned on, I would match the SQL commands caught in the profiler trace with the allocation events caught from the trace flag and shown via this query:

SELECT TOP 500
record.value('(Record/@id)[1]', 'int') AS record_id,
inf.cpu_ticks,
inf.ms_ticks,
CONVERT (VARCHAR, DATEADD (MINUTE, -1 * ((CAST(inf.cpu_ticks AS BIGINT) / CAST(inf.ms_ticks AS BIGINT)) - CAST([timestamp] AS BIGINT))/60000, GETDATE()), 126) AS EventTime,
[timestamp] ,
 record.value('(Record/@id)[1]', 'int') AS RingBuffer_Record_Id,
 record.value('(Record/ALLOC/Event)[1]', 'int') AS AllocationEventType,
 record.value('(Record/ALLOC/SpId)[1]', 'int') AS SpId,
 record.value('(Record/ALLOC/EcId)[1]', 'int') AS EcId,
 record.value('(Record/ALLOC/PageId)[1]', 'nvarchar(50)') AS AllocatedPageId,
 record.value('(Record/ALLOC/AuId)[1]', 'nvarchar(50)') AS AllocationUnitId,
 record.value('(Record/ALLOC/LsId)[1]', 'nvarchar(50)') AS LsId,
 t.record
FROM sys.dm_os_sys_info inf CROSS JOIN (
SELECT TIMESTAMP, CONVERT (XML, record) AS record 
FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = 'RING_BUFFER_ALLOC_TRACE'
AND ( record LIKE '%<Event>23</Event>%' -- uniform extent allocation
OR record LIKE '%<Event>22</Event>%' -- uniform extent deallocation
OR record LIKE '%<Event>24</Event>%' -- mixed extent allocation
OR record LIKE '%<Event>25</Event>%' -- mixed extent deallocation
OR record LIKE '%<Event>10</Event>%' -- page allocation
OR record LIKE '%<Event>11</Event>%' -- page deallocation
)) AS t
    ORDER BY record.value('(Record/@id)[1]', 'int') ASC 

My plan was to run this trace first thing in the morning. I needed to run it during the course of our normal business operations because this was when the internal object was building up, but I also didn’t want to run it during our peak time of day. As I began to prepare to run it the next morning, I was setting up the profiler trace and something caught my attention.
20160922-fix4

The application uses cursors for all database operations instead of the much superior method of stored procedures. I happen to notice that some transactions had sp_cursoropen and sp_cursorfetch, but no sp_cursorclose. Many did have sp_cursorclose lines, but many also didn’t. In the above screenshot, you can see the top line is the last statement in the previous transaction, 164507091. Then in transaction 164507096 you have an sp_cursoropen and an sp_cursorfetch, but there is not sp_cursorclose. The last line indicates a new transaction has begun. This led me to believe the problem was a bunch of open cursors for each of these connections.
My last step was to confirm this using the sys.dm_exec_cursors dynamic management function. When I checked SPID 213, which had been open about 2 1/2 days, I found a whopping 18,000 open cursors:
20150817 Tempdb 213 Open Cursors
When I checked SPID 181, which was on the top of the first screenshot above, it should no open cursors:
20150817 Tempdb 181 Open Cursors None
This is consistent with the fact that 181 has deallocated almost the same amount of Internal Object that it has allocated. In this Microsoft Technet article it states that TempDB internal objects are used to store query results from cursors.

I sent this explanation, along with the results of the trace, to the Application’s support staff. Using the trace they were able to find the places in their code where they were not closing their cursors and fix that code.

Why is TempDB Filling with Internal Object? Part 1

It is important to study the underwater landscape while fishing. There are always nuances to every location, and by finding these “spot on the spot” locations, you can know where to focus your efforts. For example, you might be fishing a large weedy bay that spans several acres. Before diving in and fishing the middle, you’d want to fish the weed edges, where the predators hide just inside the weeds, ready to ambush any bait fish that foolishly wanders into open water. An example of a spot on a spot would be a clump of weeds that extends out of the main weedbed and into the open water. This clump could conceal a large predator and at the same time give the predator a way to watch a lot of open water. Another spot might be a small hole in the main weedbed. A predator can wait for a baitfish to swim out into the hole where there is no cover, and then rush out and strike. If you aren’t paying close attention to the underwater environment while you are fishing, it can be easy to miss these spots.

We recently upgraded one of our third party applications (our biggest one, in fact), and we ran into some problems that involved a very close study of SQL Server to resolve. After the upgrade I was told by some of the application’s support staff that other customers who had upgraded were having problems (not a good sign). The main problem was TempDB filling up and not releasing it’s used space, and the only way to fix was to reboot SQL Server or recycle all of the application’s services. This was a problem that the application’s developers had not been able to figure out, so of course I was lured by the challenge of figuring out a tough problem.

The first problem was that the vendor could not see TempDB filling up. They only saw the files growing. They confirmed that they were monitoring the tempdb space used by clicking on the properties in the SSMS object explorer and looking at the space available in the General tab.
20150814 TempDB Pt1 Properties
I found that this uses the sp_spaceused system stored procedure to find these values.
20150814 TempDB Pt1 sp_spaceused
In this MSDN article, Microsoft explains that the unallocated_space considers only data pages. We can see that it does not consider internal objects by looking at the sys.dm_db_file_space_usage dynamic management view. This view is specific to TempDB and shows how much space is being used by each different TempDB object – Version Store, User Object, and Internal Object.
20150814 TempDB Pt1 space used DMV
By looking at this screenshot, you can already see that far more than 1 GB of space is being used. Each page hold 8KB of data and there are 520,440 pages being used by internal objects – that’s 4.16 GB being used. This explains why the support staff was not able to see TempDB filling up. I had already set up monitoring on our system to track this as well, and I was able to pull up the following graph:
20150814 TempDB Pt1 Space Used Graph
So I was able to establish that TempDB was filling up with Internal Objects – now the question was why. Microsoft explains here that the Internal Objects are created by the database engine and are used for, among other things, storing intermediate results for spools or sorting, as well as cursor results. Tempdb usually deallocates these objects after they are finished being used, but that is often triggered by a connection to the database being closed. So let’s look at the connections to SQL Server by the third party application. The app uses a connection pool which aims to reduce connection overhead by leaving connections open for a certain period of time. I created a job to look at our connections once every five minutes to see how long they’d been open and how much internal object had been allocated and deallocated. I did this through the use of the stored procedure sp_who2 joined to the dmv’s sys.dm_exec_sessions, sys.dm_db_task_space_usage, and sys.dm_db_session_space_usage. Here is the SQL statement that I put together:

    CREATE TABLE #sp_who2
        (
          SPID INT ,
          Status VARCHAR(1000) NULL ,
          Login SYSNAME NULL ,
          HostName SYSNAME NULL ,
          BlkBy SYSNAME NULL ,
          DBName SYSNAME NULL ,
          Command VARCHAR(1000) NULL ,
          CPUTime INT NULL ,
          DiskIO INT NULL ,
          LastBatch VARCHAR(1000) NULL ,
          ProgramName VARCHAR(1000) NULL ,
          SPID2 INT ,
          RequestID INT NULL ,
          rundate DATETIME DEFAULT ( GETDATE() )
        ) 


    INSERT  INTO #sp_who2
            ( SPID ,
              Status ,
              Login ,
              HostName ,
              BlkBy ,
              DBName ,
              Command ,
              CPUTime ,
              DiskIO ,
              LastBatch ,
              ProgramName ,
              SPID2 ,
              RequestID
            )
            EXEC sp_who2




    CREATE TABLE #DBCCOutput
        (
          EventType NVARCHAR(100) ,
          Parameters INT ,
          EventInfo VARCHAR(MAX)
        )
    CREATE TABLE #SQL
        (
          spid INT ,
          EventType NVARCHAR(100) ,
          Parameters INT ,
          EventInfo VARCHAR(MAX)
        )


    DECLARE @max INT;
    DECLARE @i INT;
    SELECT  @max = MAX(session_id)
    FROM    sys.dm_exec_sessions
    SET @i = 51
    WHILE @i <= @max
        BEGIN
            IF EXISTS ( SELECT  session_id
                        FROM    sys.dm_exec_sessions
                        WHERE   session_id = @i )
                INSERT  #DBCCOutput
                        EXEC ( 'DBCC INPUTBUFFER (' + @i + ')'
                            )
            INSERT  INTO #SQL
                    ( spid ,
                      EventType ,
                      Parameters ,
                      EventInfo
                   )
                    SELECT  @i ,
                            EventType ,
                            Parameters ,
                            EventInfo
                    FROM    #DBCCOutput;
            TRUNCATE TABLE #DBCCOutput;
            SET @i = @i + 1
        END;

   


    WITH    all_task_usage
              AS ( SELECT   session_id ,
                            SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count ,
                            SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
                   FROM     sys.dm_db_task_space_usage
                   GROUP BY session_id
                 ),
            InternalObjectPages
              AS ( SELECT   R1.session_id ,
                            R1.internal_objects_alloc_page_count
                            + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count ,
                            R1.internal_objects_dealloc_page_count
                            + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
                   FROM     sys.dm_db_session_space_usage AS R1
                            INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id
                   WHERE    R1.internal_objects_alloc_page_count
                            + R2.task_internal_objects_alloc_page_count <> 0
                            OR R1.internal_objects_dealloc_page_count
                            + R2.task_internal_objects_dealloc_page_count <> 0
                 )

                SELECT  sp_sql.spid ,
                        ses.login_time ,
                        ses.last_request_start_time ,
                        ses.last_request_end_time ,
                        sp_sql.EventInfo ,
                        spwho2.Login ,
                        spwho2.HostName ,
                        spwho2.DBName ,
                        InternalObjectPages.session_internal_objects_alloc_page_count ,
                        InternalObjjectPages.session_internal_objects_dealloc_page_count ,
                        ses.host_process_id,
                       GETDATE() AS CaptureDate
                FROM    InternalObjectPages
                        INNER JOIN #sp_who2 spwho2 ON spwho2.SPID = InternalObjectPages.session_id
                        INNER JOIN #SQL sp_sql ON sp_sql.spid = spwho2.SPID
                        INNER JOIN sys.dm_exec_sessions ses ON ses.session_id = InternalObjectPages.session_id;

    DROP TABLE #SQL
    DROP TABLE #DBCCOutput
    DROP TABLE #sp_who2

And here are the results:

20160922-fix
Note that these are all connections from one of the application servers to the database using the app’s SQL login. The EventInfo field is the last SQL that was run. If you compare the login time with the last request start and end times, you can see some connections have been open a long time. In the case of spid 182, the connection had been open for a day and a half but it had not be doing anything for the last 14 hours. You can also see that there were far more pages overall that had been allocated than had been deallocated. Pages can be deallocated directly by the code, or when the connection is closed. It appears the application’s code was not properly deallocating the internal object, and since the connections were being reused, the internal object was stuck indefinitely in a state of allocation, even though it was not really being used anymore.
I was able to prove this by doing a reboot of the Host Application2, then running the SQL statment again:
20160922-fix2
When comparing this screenshot to the one directly above, you can see that all the internal objects that were allocated by Application2 (primarily spids 182,74,242,234…) were now gone. The connections closed and the internal objects were immediately deallocated.
Here’s my overall TempDB space usage after the reboot:
20150814 TempDB Pt1 Graph After App2 Reboot
At this point I had proved that these connections were causing TempDB to blow up, but what exactly was sitting in TempDB taking up space? Look out for Part2 to see how I looked in even more detail to figure out what was really happening.

Run an SSIS job from SSRS

An important part of being a responsible boater is to prevent the spread of invasive species. When pulling your boat out of the water, plants and animals can “hitch a ride” by attaching themselves to your boat or trailer or sitting in your boat’s livewell or bilge area. Then when you move to the next lake they will get off or out of your boat and begin reproducing. This can introduce species of plants and animals to other lakes where they don’t exist natively. The ecosystem of each lake can be delicate, and introducing a new species can severely impact the species that already thrive in the lake. As an example, zebra muscles are an invasive species that are wrecking havoc on the lakes in Southeastern Wisconsin. Zebra muscles look like tiny clams with stripes, and they attach themselves to anything (rocks, sand, plants) and filter particulates out of the water. This makes the water much clearer which impacts the predators, who will now need to find more effective ways to ambush their prey.
I was surprised to find a local lake closed due to an invasive plant species, Starry Stonewort. From what I’ve read, this plant grows so thickly that poison is ineffective in trying to destroy it. It also hardens almost like coral, and reduces the amount of area that fish can use to spawn. It is very important to check your boat and trailer after pulling out of the lake but before leaving the launch to make sure you are not carrying any aquatic hitchhikers.
SSIS is a great tool for many tasks, especially moving data into and out of SQL Server. However, this tool is not easily accessible to the majority of the members of many organizations. One good way to let these types of users move data around is to set up the SSIS packages to be run in an SSRS report. Unfortunately there are a few steps to get this set up, but once you have it up and running you can cut yourself out of a lot of simple but time consuming tasks by letting users load or extract their own data to use in Excel or anywhere else.
The first step is to create an SSIS package to do the actual movement of data. The input or output will usually have to have a standard naming convention and standard field configuration.
20150810 SSIS from SSRS SSIS package
Next, create a SQL Agent job that will run that SSIS package.
20150810 SSIS from SSRS SQL Agent Job
The next step is to create a stored procedure that will run that SQL Agent job.

-- =============================================
-- Author:		Gugg
-- Create date: 2015-08-10
-- Description:	Runs the SQL Agent job that imports the IQR files
-- =============================================
CREATE PROCEDURE [dbo].[ImportIQRFiles]

AS
BEGIN

   SET NOCOUNT ON;

   EXEC msdb.dbo.sp_start_job  
    @job_name = N'AdHoc - DirectTech IQR Import'
       
END

GO

Lastly, create the SSRS report that will use that stored procedure as a data source.
20150810 SSIS from SSRS SSRS Report
Now when the user clicks to view the report, the data will be successfully imported or exported into SQL Server.
20150810 SSIS from SSRS Report Icon
It can a good idea to make the SSIS package as robust as possible with error emails sent to those who you expect to be running the report, that way if the import or export fails, the user can figure out why (often a misnamed file or missing field) without having to get you involved.

SQL Server Upgrade/Migration

Last week I was feeling burnt out. We’d been having numerous issues pop up at work and it was taking its toll on me. I felt constantly mentally fatigued and knew I needed some time to myself. My wife could sense I needed some relaxation, so she took the kids to her aunt’s house and let me have some solo time. I considered going down to Pewaukee to try and catch some muskies, but the water temp is peaking over 80 degrees, which is the max you should fish for muskies. Any warmer than that and they are unable to properly recover from the fight and could end up dying, even if it isn’t for a couple hours after they are released. Besides, I needed to just relax, and while muskie fishing is an awesome sport it really isn’t very relaxing. So I headed over to Little Cedar Lake to shoot for some walleye, largemouth bass, or northern pike. While I only managed one medium sized bass, it was great to just sit back and sip a few beers while enjoying the lake air and beautiful scenery. Since that night I have felt much more refreshed and ready to get back to work.


Evening Fishing
We have been doing some server upgrade/migrations the last several weeks. We purchased a new server and needed to move our production instance to it. We don’t have any kind of high availability, and were able to tolerate a system outage of four or less hours. The first thing I did was to practice extensively using our Test environment. I did the cutover in Test several times. I was confident going into the upgrade that I would be able to complete it successfully.  This upgrade was slightly more complicated in that in involved moving some databases to different drives during the upgrade process.  Not only was I going to move the system files from the C: drive to the data file and log file drives, but I also needed to move tempdb from the G: to the F: drive and move data and log files into differently named folders on the D: and E: drives.
I used the following procedure list to perform the upgrade:

  1. Stop the application services.  This will close existing connections to the user databases.
  2. Stop the data warehouse transfer process.  We use a third party tool to move data from our Production SQL Server instance to our Oracle data warehouse.
  3. Take full copy-only backups of each user database.  After taking each backup, take the database offline to prevent new connections and changing data.
  4. Take full backups of all system databases.  It is important to do them after the user databases so that msdb has records of the latest user database backups.
  5. Move all system database using SQL Script below.
  6. Create and restore all user databases using SQL Script demonstrated in other blog post.
  7. Shutdown new and old server.  System Admin changes the name of the old server to whatever, then changes the name of the new server to what the old server used to be.  He also updates the IP of the new to be what the old was.  This way we don’t have to change any application settings.
  8. Bring up Reporting Services on new server.  I will explain the intricacies of this step in a later post.
  9. Begin testing everything – application, SSIS, SSRS, and SQL Agent jobs.

Here are the SQL Scripts used to make the changes:

USE [master]
RESTORE DATABASE [model] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\model\FULL\OldServer_model_FULL_20150724_201857.bak' 
WITH  FILE = 1,  
--MOVE N'modeldev' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf',  
--MOVE N'modellog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\modellog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')

GO

--Stop SQL Server Agent
USE [master]
RESTORE DATABASE [msdb] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\msdb\FULL\OldServer_msdb_FULL_20150724_201858.bak' 
WITH  FILE = 1,  
--MOVE N'MSDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf',  
--MOVE N'MSDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\MSDBLog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')

GO
--Restart the SQL Service

--Stop the service
--Add -m to SQL Server startup parameters to start in single user mode
--Open command prompt
--sqlcmd
--Start the service
RESTORE DATABASE [master] 
FROM DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\master\FULL\OldServer_master_FULL_20150724_201856.bak' WITH REPLACE;

--Remove -m from SQL Server startup parameter
--Open command prompt
--Go to the SQL Server directory (on this machine, C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)
--run SQLServr.exe -T3608 -T3609
--		This will just open Master database.
--Run SQLCMD from a different command prompt and run these commands
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev01', FILENAME = 'F:\TempDB\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev02', FILENAME = 'F:\TempDB\tempdb2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev03', FILENAME = 'F:\TempDB\tempdb3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev04', FILENAME = 'F:\TempDB\tempdb4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev05', FILENAME = 'F:\TempDB\tempdb5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev06', FILENAME = 'F:\TempDB\tempdb6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev07', FILENAME = 'F:\TempDB\tempdb7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev08', FILENAME = 'F:\TempDB\tempdb8.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev09', FILENAME = 'F:\TempDB\tempdb9.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev10', FILENAME = 'F:\TempDB\tempdb10.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev11', FILENAME = 'F:\TempDB\tempdb11.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev12', FILENAME = 'F:\TempDB\tempdb12.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev13', FILENAME = 'F:\TempDB\tempdb13.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev14', FILENAME = 'F:\TempDB\tempdb14.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev15', FILENAME = 'F:\TempDB\tempdb15.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev16', FILENAME = 'F:\TempDB\tempdb16.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'F:\TempDB\templog.ldf')

--End SQL Service, then start the service normallyy.
--At this point all the system databases should be transfered to New Server.

As I said, the migration went pretty smoothly.  I did have a few hiccups that should have been tested weeks before the migration, but I missed them.  I was able to get them working within a day of the migration, and next time I’ll get them right away.  The first was the database mail, which took me an extra hour to get set up right after the migration.  The second was a linked server.  This was a bit tougher because I had to install the Oracle ODBC driver, and this required a SQL Server reboot.