I would like to start out by saying im a few months away from my nsa degree although I have nearly 15 years experience dealing with hardware, technology + more, I have almost 0 experience with SQL, What was covered in the classes I've taken doesn't seem to cover anything I'm currently working with. Throughout the information I've learned recently I think there's to much for me personally and I would like to have someone with more knowledge point me in the correct direction, make sure I'm on the right course, and I guess primarily to make sure I'm not making this harder than it really is. Any and all help is greatly appreciated!
To start, at my business we deal with a program called Law PreDiscovery which is made by LexisNexis. The cases I handle are made as SQL cases so multiple users will be able to work on a case simultaneously. The Server PC is a dual core 2.33 Ghz 32GB of ram running Windows Server 2008 R2.
To keep to point, when this server was received was long before I had joined the company and the sql server was set up by another. in the program menu I can see SQL server 2008 as well as 2012 in the program menu. These are Sql Express editions
What I'm trying to do is copy this sql server to another server, although I don't know where to start and frankly I need a really fast crash course.
I need to know how to find the location of my sql server, verify if its running the 2008 or 2012, make a copy of that in some way, get it up and running on another server, and verify its properly working.
P.S. I really don't have any time to spare, so anything is appreciated
Check Services in Admin Tools for the instance names. You'll see SQL Server in that list if SQL Server is installed. Post the details here.
Regarding how to copy everything to another server, there are plenty of articles on that topic and not something we can just hash out in a forum. We can help you with any errors or problems you face while performing that move.
Is the server a virtual machine or a physical box?
I've taken a screenshot of the sql services area. (located below)
Everything I've found regarding copying to another server seems to be focused on either Paid Sql versions or linux platforms, neither of which I'm using.
The actual server is a physical box, Although I have restored a full backup of this server to a virtual machine which I would like to perform the process on first and then proceed with the actual machines.
My first idea was to try a master master set-up, although the article is focused on linux and everything I've searched up related to claims to require paid sql if I'm on windows.
Currently I would like to replicate the sql database to another platform so the main server can be disconnected and serviced. If in the future this setting would also be able to mirror and keep the sql database up to date constantly that would be fantastic.
from someone who knows a bit more about SQL than I do, I would be much appreciated if there are any guides that I can be pointed in the direction of.
The Linux articles are irrelevant as those are for MySql. You are using Microsoft SQL Server. You'll want to specifically be reading about Microsoft SQL Server, which runs on a Windows platform.
You've got two instances installed, one is SQLEXPRESS. I can't read the other instance name as the screenshot has the Name column cutoff. To connect to either though, you would do so in Management Studio and use the names ServerName\InstanceName, replace both with your actual values. Once in there, run a query to check the version SELECT @@VERSION. Do that for both.
My 2 instances of SQL are lexisnexislawsvr and SQLEXPRESSOME
I'm not able to connect to sqlexpressome, it says the server denied the request the server might not exist.
I am able to connect to the lexisnexislawsvr, which is the sql that stores our files from law. I'm not sure what importance if any the sqlexpressome could have.
The version for the lexisnexislawsvr is Microsft SQL Server 2012.
You can't connect to EXPRESSOME because the service is disabled and not in a started state. If that's a production box, then you can bet it's not in use since people would be screaming otherwise.
If the server you want to copy the databases to is the same version and build number plus the paths are the same, you can simply stop the SQL services on both boxes and then copy all of the mdfs and ldfs to the new server. Then startup the services on the new box. You should be good to go.
That's the easiest way. Another way is to use backup/restore for the user databases, but then you'll have to copy over anything not stored in the user databases, such as logins, jobs, alerts, operators, linked servers, etc. Typically it's just logins and jobs in my experience. Your mileage may vary.
@Kristen has an awesome thread in the old SQLTeam forums on this topic. I've tagged him here. I think it has to do with upgrades, but the same awesome sauce applies when moving to a new machine.
This thread is in the new forum. It contains a link to the old forum, but I think everything in the old forum's thread has been copied to the new forum - the formatting may have got a bit mucked up in the process, but all the "meat" should be there.
I would like to avoid headaches and keep the exact same version of sql, I can replicate the paths so everything remains the same. although the sql version on the server currently is 11.0.2218.0 (x64)
i've downloaded sql 2012 version 11.0.2100.60 as its the closest I could find. so at this point I've found lists of updates which may help me getting to the proper version. With my limited knowledge of sql, and the need to save time as well as getting this done effectively, I do believe this is my best option at this point
I'll go update by update to do my best to get the builds matching spot in.
I'm just curious if this is possible? Or if this may cause more headache in the longrun then doing a backup/restore into an updated 2012, upgrading past sql 2012 isn't an option due to incompatibility with LawPreDiscovery.
I've used the method I posted numerous times, even to setup new production systems so that I can get all of the stuff that isn't stored in the user databases. Depending on the size of the user databases and the amount of downtime the system can handle, I may backup/restore with logs to do the user databases during a maintenance window. How big is the database and how much downtime can it handle?
The size of my data folder where all the databases are located is only 1.5 GB.
Everytime we create a new case within law, it creates a database within mssql.
Now I've found guides showing how to move an mdf and ldf file... Although these only show how to move 1 at a time. I have thousands of these.... Would there be an easier way to move all the databases together?
As far as downtime goes, I can take it down anytime after hours as long as I let people know before hand. As stated earlier I'm making sure I fully complete this process in a virtual environment prior to a live one.
Number of files doesn't really matter, total size does. So with only 1.5GB, you should be able to stop SQL on both boxes, copy files, paste files, start SQL on new box all in just a few minutes.
Not sure if your application is custom or something you bought, but a new database per new case and with thousands of them is just screaming of a design issue. But that's outside of the scope of your question.
The program is designed for E-Discovery, we could put hundreds of thousands of boxes in seperate volumes and seperate jobs per "database" or as we call it per case.
I did stop the sql service, made a copy of my data folder, copied it over the data for the other pc, which both are running the same build same file locations now. After that the sql service wouldn't even start, I tried copying the old data back and it wouldn't restart still... at that point I uninstalled and reinstalled sql and it seems to be working fine now, although I still need to move the files over, just been busy with other things today.
If the old service didn't start, then you must have done something different. Never CUT the files. Always COPY them that way can just start the service if things don't go well.
I'd need to see the errors. Windows Application Log should give you why.
I tried the same thing again, only this time I did not overwrite the files already located within the data folder on the new machine. This time the sql started back up, and as I attach databases they seem to add successfully... I'll only need to test them to make sure they are working properly.
When I right click the name of my sql server and select attach, it seems to only let me attach 1 database at a time, is there something I'm missing here?
Oh you aren't grabbing the system databases too. Grab those and no need to attach. All the user databases will just be there when you start the service.
as soon as I copy those the sql service won't start back up, this time upon restoring the default data folder entries the sql service started up for me...
I found this code here which is supposed to add all the databases within the location, I edited it as appropriate
exec master..xp_cmdshell 'for %a in (C:\Path*.mdf) do @echo exec sp_attach_db ''%~na'', ''%a'', ''%~dpn_log.ldf'' '
and when I use it I get a list of everything saying it executed properly, although they arent added.... so I'm not sure what that's doing
also the error I get when it won't open is "FCB::Open failed: Could not open file S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 5(Access is denied.)."
Where am I able to check service accounts within sql?
All the accounts are authenticated through the domain, There may have been an account specific to sql set up in the beginning by an I.T. person which is no longer here, is there a way I can see what it is?
