1 files changed, 110 insertions, 0 deletions
diff --git a/src/Migrating_SQL_Data.adoc b/src/Migrating_SQL_Data.adoc
new file mode 100644
index 0000000..7af806c
--- /dev/null
+++ b/src/Migrating_SQL_Data.adoc
@@ -0,0 +1,110 @@
+Migrating SQL Data
+==================
+:author: Aaron Ball
+:email: nullspoon@iohq.net
+
+
+== {doctitle}
+
+In my current project I have found a need to migrate data from one SQL server
+to another. For those of you SQL gurus out there, you know that there are many
+ways to migrate data from point A to point B in SQL, even when different
+versions is involved. Here's my setup
+
+*Server 1:*
+
+* SQL Server 2005 Standard (x86)
+* Windows Server 2008 (x86)
+
+*Server 2:*
+
+* SQL Server 2008 Standard (x64)
+* Windows Server 2008 (x64)
+
+As you can tell, I'm upgrading versions *and* processor architectures.
+Surprisingly enough, this didn't seem to cause any issues for me.
+
+Here are a few options one has to migrate SQL data between servers for those
+who don't find this post too useful.
+
+* SQL Copy Database Wizard
+* Detach, copy to new server, and reattach
+* Backup database, copy backup to the new server, convert backup to a
+  database, attach the converted database
+* Create a database mirror
+* Duplicate the database structure on server two and import the data
+  from server 1
+
+For my environment, only 1, 3, and 5 would work since the others leave more
+possibility for data integrity issues during the transfer or require that the
+SQL server be temporarily taken offline. I tried out a few of my options and
+decided that 1, the SQL Copy Database Wizard, was the best option. It's
+relatively straightforward and very efficient.
+
+For the last three days I have been struggling with it because of what looks
+like permissions issues, though I can't be sure since all the error says is
+that step 1 was the last step to run and that the job failed (give me ambiguity
+or give me something else!). All that being said, I decided I needed to find a
+new way to transfer the data.
+
+Through all of my troubleshooting I found quite a few SQL facets that I pieced
+together to get what I needed. Here's how I chose to migrate my 12 databases
+without too much trouble.
+
+image:files/01_SQL_Migration_ScriptDatabaseAs.png[height=300]
+
+To start, I used SQL Server's remarkable "**Script Database as**" functionality
+to write out the query that creates the database, all of the columns, and all
+of their constraints. For mine I just copied the script to the clipboard to
+make compiling all of the scripts together much faster. To sum it up, I had SQL
+generate the queries for each database I wanted to migrate and I pasted them
+all into notepad so I could run them all at the same time.
+
+image:files/02_SQL_Select_Import_Data.png[height=300]
+
+After running all of the afforementioned queries to create the all of the
+database structures on your destination server we're ready to start importing
+data. * the database to import data to. Go to *Tasks* -> **Import Data**...
+
+If you haven't done this before, you should receive a "Welcome to the SQL
+Server Import and Export Wizard" screen. **Next**.
+
+image:files/03_SQL_Import_Choose_DataSource.png[height=300]
+
+Here we are at the "Choose a Data Source" screen. For Server name type the
+hostname of the server you need to migrate from (the source server).  After
+that, select the database you want to copy to the new server. Once done with
+that, * **Next**.
+
+On the "Choose a Destination" screen, type in the name of the server to migrate
+the data to. Next, select the Database you want to copy the data to (this
+should corrospond to the DB name on the source server since we ran the create
+queries generated by SQL). In my case, I was running the Import and Export
+Wizard from the server I was importing the data to so SQL server already had
+the Server name and Database filled out.  **Next**.
+
+In my case, I wanted to copy the entire database to the destination server, so
+for the "Specify Table Copy or Query" screen, I elected to "Copy data from one
+or more tables or views". **Next**. On the following screen, check all the
+database tables you want to be copied (or just check the box at the top left
+for all of them).
+
+Nearing the end the "Save and Run Package" screen comes up. If you don't need
+to save the package to be run later or again at a later time, just leave Run
+immediately checked and click **Next**. Finally we review our settings and what
+will be copied. If everything looks right, click Finish. Once the transfers
+have completed, click **Close**. If any transfers failed or threw a warning,
+you can click the "Messages" text next to the table that did not succeed to see
+the log entries about it.
+
+Repeat the aforementioned steps until you have migrated every database you
+need.
+
+
+
+Category:Microsoft
+
+Category:MsSQL
+
+
+// vim: set syntax=asciidoc: