Migrating a MS-SQL Server to NS7

@MrE

Good morning
As I see, you’re in Mexico, I’m based in Switzerland. That’s a fairly big time difference!

An SQL-Dump is basically a big fat textfile. Incorporating all commands & data to replicate the existing data in another (or same DB in case of restore).
You’ll see stuff like:
create table xxxx
and so on, with all needed specs.
The Users part of the data would entail where the users come from, eg. avion.local.
This could be corrected manually to the corresponding user under eg. avion.lan.

What Pike says is true. MS-SQL can now be “officially supported” installed on various Linux flavours. But that’s just the SQL-Server. A lot of commercial Apps using MS-SQL Server also use DLLs & other stuff, most of which won’t work when the DB is on a Linux.
If you’re migrating an InHouse application, that would work, but in other cases it would be easier and stabler running it on a virtualized Windows.

If I have a free choice, I prefer to let things run in their optimal environment.
Let’s say for example I need for a client a machine for the client to do Excel VBA programming (heaven forbid!) - I’d choose the latest Office from MS, running on Windows10.
I wouldn’t choose Linux or Mac as a basis, even though I prefer those OSs to Windows.
Nor would I choose LiberOffice or any variant…

Your mileage may vary. :slight_smile:

My 2 cents
Andy

Thankyou @Andy_Wismer & @pike.

My bad choice of words are playing tricks with my post.
I don’t need to migrate a Sql Server running on windows 2012 to run on Linux, but to change/join the domain where that windows 2012r2+sqlserver runs from a windows 2000 domain to nethserver-samba-domain.

I only use full backups, the sql-dump (a lot of years ago I done some testing, now I recall) and using mysql I see it (a coworker do that) for an in house project.
I wonder how it will be for a DB with 53.1GB size :nerd_face:
When I migrate from one windows server to this, I restore the full backup, and recreate the users and permissions to run, but it was the same domain. Lucky me, now the users have the same account

(avion.local) AVION\user1 -> AVION\user1 (avion.lan)

sketch (incorrect)

sketch fixed (hopefully):

@MrE

Hi
I did understand you didn’t want to run MS-SQL on Linux, I just added in that interjection due to Pike’s legit info.

I’m in the process of doing a migration myself, but target server is 2016…

The Sketches do help understand where you come from, and in what direction you want to go.
But I doubt your goal is to have a “SQL server fails to start services”, as your target sketch implies… :wink:

Andy

:rofl: you got me there… I want to put 3 steps but … you see, that I hurry myself… let me fix it.

@MrE

Yup, and I just realized that this SQL was also acting as a “FileServer” and a 1TB RAID for backups. (Doing a backup locally may be faster, but using the same RAID controller doesn’t seem a wise way to go. If that fries, your backup may be cooked as well!)
I’ve vastly oversized the new server - file-server will be another box…
That means the backup takes way to long! (Full Proxmox Backup).
I’ll have to resize this vm.

Andy

Our win2012+sqlserver is not a file server (well, yes for a little proyect) and have 4 HD (each pair in raid1), I take a full backup, compress and copy outside the server. twice each day…

Just now, I working to get quotes for a NAS, I believe a Synology 4 disks and RAID6 and WD Gold HD to replace the file server on the windows 2000 server.

Hi

No, on the project I’m doing for some Doctors here in Switzerland. I failed to actually check what the second disk array was used for, assuming it was also files & stuff. So I dimensioned the new server way too big!

The previous server wasn’t my work, and docu could be better from the previous IT guys. A list of passwords is about all the docs they made.

As the saying goes: Mier*a happens… :wink:

Andy

My spanisch is just about “jo no hablar espaniol”.

My rule of thumb for Backups at client locations:

After work till midnight, local backups by the systems themselves. Windows using whatever (Storagecraft), Nethserver doing it’s backup and so on.
After Midnight til 06:00 Proxmox does it’s backups.

Yup, and we need to clean it.
I don’t speak/wrote ‘switzerlian’, in Switzerland you have at leat 4 languages German, French, Italian, Romansh (wikipedia) !

As you see, I write a little in English, but I think that I’m worse in my own language spanish. We have a lot of grammatical rules. Sometimes I can’t speak with my own ‘paisanos’.

Ok, I’ll try to test another migration, but first I need the NAS quotes today.

Thank you Andy, is nice to read and learn. I’ll update my findings and pain of this migration in here.
Thank you @davidep for creating this branch topic.

Hi
IT stuff should be clean - and well done. The IT cabinet looked clean at first sight, but:
One Mac Mini Server had 4 USB Disks AND a Firewire Disk plugged in.
(Way to many local shit, use a NAS for that stuff.)
A electrical mains extension, ending in a multi-way plug, hanging on the weight of several cables plugged in.
Lots more like that…

BTW: I’m based in north eastern Switzerland, in the border town of Kreuzlingen. On the other side, in Germany is Konstanz (Constance on the lake of Constance, in german Bodensee). The doctors are in Zug, a 2 hour train drive from here, and in the geographical center on Switzerland. Although the train services here are excellent, going back and forth is 4 hours… Do I love SSH & VPN & Internet. Some stuff like putting together a new server has to be done in situ, but the rest can be done by remote.

Where are you situated?

Best regards
Andy

Is a little state (Nayarit) the city is Tepic; is a nice place, but summer is coming (I prefer autumn+winter+spring), because summer here will be hot and sweat with a lot of thunder storms. In autumn we got storms with a lot of wind (rare but not of the good type).
image
image
image

By the way, I was researching this task and here are some links (one for now)

The issue that I got is the step ‘I’ of moving-sql-server-to-different-domain
Reading carefully I see what I need to do… but I need to test it and research how to drop/change the Service Principle Names (SPNs).

What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain? What steps definitely/might need to be done for SQL Server before and after the new domain is joined?


What do you need to take into account when migrating SQL Server to another domain?

The steps below presume:

IP address will also change
SQL Server is NOT clustered

Steps:

A. Backup:

BEFORE: backup the datases off-machine

B. Services:

BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
AFTER: Once things or up and running properly, return service start to its original setting

C. SA account:

BEFORE: If all administrator accounts are domain accounts or groups, temporarily enable the 'sa' account with a strong password
AFTER moving: once the domain-based accounts are setup in the new domain, 'sa' can be disabled again

D. Service Windows account:

BEFORE moving: for each SQL-Server-related Windows service, change the service to use a LOCAL windows account or one of the built-in accounts
AFTER moving: grant the necessary privileges to the service new domain accounts. When special permissions are not needed, the SQL Service Configuration Manager can be used to change the service account

E. Windows domain accounts used to login to SQL Server

Re-create the needed accounts or use corresponding accounts in the new domain.
BEFORE moving, script out permissions for OLD domain accounts.
AFTER moving, apply these scripts to the corresponding NEW domain accounts so they will have the same permissions

F. IP Address: SQL Server (unless clustered) will use the new IP address

AFTER: Client applications that reference the service by IP address will need to be configured with the new IP address.

G. Firewall:

AFTER: OLD firewall openings that are no longer used will need to be closed, NEW firewall openings may need to be created for SQL Server, OLAP services, SSRS between servers and clients

H. DNS entries:

AFTER: verify DNS has correctly updated
AFTER: Clients and services that reference by DNS name, may need to be restarted AND/OR their host systems may need their DNS cache flushed. For windows workstations, this can be done with "ipconfig /flushdns"

I. Service Principle Names (SPNs). Some standalone (and all clustered) instances use SPNs.

AFTER: The OLD SPN must be dropped and a NEW SPN must be created. Although it's not recommended to use a SQL Server service account to manage (its own) SPNs, if this is the case, the NEW domain service account will need to be granted WriteServicePrincipalName" privilege

J. Client Network Utility Alias.

AFTER: Update any clients that use these will need to updated

K. Client application and service connection configuration:

AFTER: Data Source Names (DSNs), connection strings, config files, Oracle TNS names for connections - will need to be udpated and applications and services may need to be restarted

L. Internal machine name.

AFTER: If the machine name is also changing, SQL Server's internal machine name entry may need to be udpated

sp_dropserver 'MyOldMachineName' go sp_addserver 'MyNewMachineName','local' go

M. Merge Replication - If merge replication is in use, it will also need to be reconfigured.

BEFORE: ensure all replicas are up-to-date
AFTER: re-configure merge replation

Attributions - some information added from these sources:

https://serverfault.com/questions/49681/change-ad-domain-membership-of-a-server-2008-running-ms-sql-08

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/f3e8ff83-8fcd-4335-87fe-ea5641ae6b88

More info: How to grant "Write ServicePrincipalName” and “Write validated SPN” rights to the directory for service account

How can I grant "Write ServicePrincipalName” and “Write validated SPN” rights to the directory for service account or computers?


Computers can already write thier own SPNs, you can use the following DSACLs snippet to grant a service account the right to write it’s own SPNs.

dsacls <DomainName_of_Service_Account> /G SELF:RPWP;"servicePrincipalName"


Right-Click on the OU and select Properties
Select the “Security” tab
Select the “Advanced” tab
Select the “Add” button
Enter the security principal name
security principal
Ok
Properties tab
Apply to:
Descendant User objects
Permissions:
Read servicePrincipalName - Allow
Write servicePrincipalName - Allow
Ok
Ok
Ok


I’m applying exactly what you describes in the above solution. I’m working on an Windows 2012 R2 with an AD and Forest Mode in Windows 2012. I’m logged on as Enterprise Admin. However, none of both, read or write serviceprincipalname are listed on the attribute sets?

I tried to add the SELF account, add the rights to a domain local group. I tried on the OU level and on the service account itself.


In my experience, you need to set:

Apply to: Descendant Computer objects


May be it would be useful for somebody. Read/write serviceprincipalnape are shown only for computers account using dsa.msc console. To set these acls for user account you need to use adsiedit.msc .


Use ADSIEdit.msc, you will find ServicePrincipalName for all descendant user


More to read:
https://everygreen4u.blogspot.com/2012/08/how-to-use-adsi-edit-adsieditmsc.html
or here How-to-Use-ADSI-Edit-adsieditmsc-05-10.

.

@MrE

Thx for the list!

Tepic is 10x larger than Kreuzlingen… :slight_smile:

1 Like

Is beautiful! I envy the care that Europeans have with their places. Our authorities here don’t plan for anything, just trow tar in the streets and wait for the better. I don’t want to complain, but hopefully some day we will have better places, more green less trash+pollution in our cities. Ahh our countryside will be green in this rain season and its nice to see.

@MrE
The panorama view on Wikipedia show lush beautiful green.

But I do appreciate living in Europe!
There are other nice places, say Singapore, where my brother lives…
But - I wouldn’t voluntarily trade.

If you ever do make it for a visit to Switzerland, see the “Aareschlucht”.
Put that in Google, and click on pictures…
All of my foreing guests I’ve accompanied have said, yes, that’s how we mentally pictured Switzerland!

Andy

Hi all, have you ever tested the mssql-server installation on Nethserver? - MSSQL HowTo

@fausp

Hi
It’s planned, but not right now. Just moved, and need to wait for my stuff to catch up with me.
:slight_smile:

Andy

1 Like

Hi André, please let me/us know your experience with it…

Thanks @fausp, but that is not planed on my side, not yet, looks very promising, and yes, I’ll try in some of my next test just to know if it can work for us, I wonder if your how to works for sql server 2008r2.

Right now my plate is full, and the need to move that windows 2012 + sql server to this new domain is top priority.

If I accomplish this task, then I will commissioned the old w2k server (need a NAS) to replace the file server or to add as an iSCSI disk for NethServer (the safest is what I’m looking for).

Regards

Will do, but might take 1-2 weeks…
Will see about a more or less full How-To.

Andy

OK folks, I wish you every success…