Migrating a MS-SQL Server to NS7


(Enrique D) #1

Hello @Andy_Wismer

I’m in the process of migrating a SQL Server (running on Windows 2012r2) from AD (windows 2000) to NS (Samba-AD).
So I wonder if you have this kind of knowledge and if you aware of issues and steps to accomplish this task?

My first try some months ago fail, the SQL server trows some error (I can’t find my log to explain)

Right now, I’m testing a VM on Proxmox… need time to spare for this test, but I wonder if you have some tips.

Regards!


Adding the objective
sketch (incorrect)

sketch fixed (hopefully):


Thank you @davidep for creating this branch topic.


Active Directory BDC/slave role
(André Wismer) #2

@MrE

Hi
I do have a few SME (Small & Medium Enterprises) as clients, some use Applications which require a SQL-Server in the background.
A lot of these clients are generally less than ten users…

One such software is ReviPS, a financial software for taxes for bookkeepers. There’s a version using a File as DB, but once that gets to 10 GB size, it becomes barely useable. The same software also supports SQL-Server, which is classes faster at larger DBs.

Now, as we’re talking about ten or less connections at max, and the fact that MS-SQL-Server run’s on a Windows PC, why waste licencing money on an overkill server, when a Win10 Client OS is sufficient?

I’m also running this on Proxmox, also the NethServer (AD and Zabbix monitoring) is also on Proxmox. A few screenshots (This time after switching to english for understandable screenshots!) should help in the setup…

Here the Win10, as Domain Member:

The Zabbix view…

Seen in Proxmox:

The Win10 has been “optimized” for Proxmox running.
The Proxmox are configured as a cluster, all files are stored via NFS on a Synology NAS. Proxmox Backups are done on an older Thecus NAS.

This whole setup works very well, we have a second SQL-Server for a different Software (DrTax), basically with the same setup.

At other clients I do use say Win2008.Win2016 (Server Versions), also as Domain Members in a AD by Nethserver… :slight_smile:

Hope that helps.
Andy


(Enrique D) #3

Thank you @Andy_Wismer

I wasn’t very clear (I’m at work and a lot of interruptions/tasks)

  • This SQLServer is (2012R2) running on a Windows Server 2012 R2 (named ‘sql2’, with an ERP)
  • ‘sql2’ is joined to an AD in an old windows 2000 server (SBS) (named ‘serverA’, domain avion.local)
  • There is our NS running as VM in Proxmox (named ‘ADS’, and domain is avion.lan)
  • After proving that moving from one domain to another all my users can still use the ERP, y proceed to move users [avion.local -> avion.lan]
  • Almost all my windows users are now from ‘serverA’ domain to ‘ADS’ [avion.local -> avion.lan]

I’m aware, of something, sql2 authenticate the users on avion.local, I create the sames accounts, groups and passwords the same in both domains.

But, the problem occurs when I try to change the domain of ‘sql2’ from avion.local to avion.lan

  • (the error) The sql services shows and error and the services don’t start. I document the process but I can’t find it.
  • I know that after migrate the domain, I need to re-create sql accounts (well I think that is needed) so previously I backup the users+groups needed to recreate in sqlserver.

But I can proceed in the final step because the error.

So the correct question needs to be:

¿How to migrate a windows 2012r2 server running sql server 2008 r2, from an old windows 2000 domain to a new doman in NethServer/Samba/AD)?

As I say, I have the test VM replicating the “current” setup of server2012r2+sqlserver2008r2 joined in the old domain, I’m in the step of test that the ERP works for some users, then migrate to NS and test the ERP… hopefully I’ll find and solve the error that I have in the real setup.

Regards!


(André Wismer) #4

@MrE

I have to confess, in all cases the SQL contains the Users, not the AD.
That does makes things easier…
A lot of these are licenced applications, with only a very limited amount of users accessing the apps.

One important thing: Don’t make the mistake a good colleague of mine did: The NAS storing the VMs was set up as AD client, to facilitate users accessing a few shares. The day he shut down the AD to make a “offline” backup, he couldn’t access the NAS anymore, to copy the vzdump file to a usb disk hooked up to his notebook… :slight_smile:
Always use only local authentification on that NAS, unless AD has a “BDC”… and is well redundant. :slight_smile:

What might help in your case would be a simple sql dump…
A bit of editing can work wonders… :wink:
And then load that into the newer SQL…

Andy


(Enrique D) #5

There is no doubt about that, and sqlserver can authenticate sessions from users in the domain too.
So, this sqlserver is using that authentication, and is very ‘convenient’; and using User Groups, I can manage permissions, with this some groups can or not update some tables, etc. in our ERP that use sqlserver.

But, in this scenario, sqlserver need to use a domain user account to start the services and with their internal mechanism validate the user’s domain access.

That mistake that you inform is something to not forget. I plan to buy a NAS, to store our users files and use the AD in NethServer to authenticate permissions, etc.
I never use a NAS without internal accounts; one AD domain down for some hours can be a real nightmare.
But maybe, I can have some iSCSI target to store the Proxmox VMs but not using the domain to access the target by proxmox, proxmox don’t know nothing about NS in my case.

Don’t know about this, can you elaborate or post me a link to read.
I take backups and restore the DB when needed, but if I restore the DB in another place (not our domain) I need to grant access on that scenario (new users, etc) so I can test the ERP+DB

Of course is what I did to test a vm for the win2012r2+sqlserver migration.


(Michael Kicks) #6

Sometimes MS-SQL requiring apps do not cooperate very well with other kind of database, it’s important to have some written compatibility list from the developer/provider of the software.
Some versions of MS-SQL could be now installed on linux, but i am not sure that thiss will work with all softwares.


(André Wismer) #7

@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


(Enrique D) #8

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):


(André Wismer) #9

@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


(Enrique D) #10

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


(André Wismer) #11

@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


(Enrique D) #12

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.


(André Wismer) #13

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.


(Enrique D) #14

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.


(André Wismer) #15

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


(Enrique D) #16

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


(Enrique D) #17

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.

.


(André Wismer) #18

@MrE

Thx for the list!

Tepic is 10x larger than Kreuzlingen… :slight_smile:


(Enrique D) #19

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.


(André Wismer) #20

@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