ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles



  • Hi All,

    I don't have much experience with relational databases and my SQL knowledge is mainly built around resource provisioning for SQL servers (VMs).

    There is one small company with horribly messed up IT infrastructure I help out at the moment. While sorting their mess out, I want to trial a Zerotier VPN between the main office's server and a smallish (5 workstations) remote branch. The remote branch connects to the main office over a Site-to-Site VPN configured on the ancient (10 y.o.) home grade networking hardware on ADSL (both branches will get 100/40 HFC links soon). They already bought two Edge Router lite as I told them and I already configured those boxes with route-based site-to-site VPN (both sites have static public IPs). However, I want to test something more modern and flexible like ZeroTier for them so I've installed ZeroTier agents on the SQL Server (Physical Server 2008 R2 machine) and the Win10 test PC at the remote location. They can ping each other on the ZeroTier interfaces.

    The remote branch staff is accessing an SQL Express database at the main office using a custom built ERP client that talks to the database using an alias of 192.168.2.4/SQLExpress (it listens to a custom port too). Is it possible by design to add another alias with the new ZeroTier interface's IP and on the SQL server to connect the remote ERP clients to? Or is there another way for the SQL DB to also talk to the remote clients with Zerotier's IP without removing the current alias? (I want it for the local clients). I know there is a registry workaround to add a new NIC for the SQL server to listen to but it's configured with an alias and the server's 192.168.2.4 NIC is not even listed in the IP Addresses tab of the TCP/IP properties in SQL Configuration Manager.

    I appreciate your advice, folks.



  • @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    The remote branch staff is accessing an SQL Express database at the main office using a custom built ERP client that talks to the database using an alias of 192.168.2.4/SQLExpress (it listens to a custom port too).

    This is an ancient client/server architecture that really should never happen even on a LAN. Doing this over a WAN will be extremely bad. Not impossible, but it will such.

    Imagine how awful it would be to access an SMB mapped drive over a WAN link. Databases are similar. You want your app as close and low latency to the database as possible. If you are going to have a WAN link, it should be on the other side of the app in 99% of cases.



  • @scottalanmiller

    I totally agree with you, Scott. I already told them they need to migrate to an SaaS solution asap but the current app is so customised to their business that they're entirely locked themselves in with it. The owner is reluctant to change anything and the developer keeps assuring him that his app is great and it serves its purpose 100%. But the way the dev implemented it was simply awful. Currently it takes about 4 seconds from the remote branch just to access the DB!

    I see this situations almost every time I assess SMBs. Horribly old apps running on below par hardware.



  • @scottalanmiller

    So if they don't want to migrate to another solution, what would be the sensible option for them, then? Running it on a virtual host with two VMs - SQL and RDS with the ERP app installed? Thanks.



  • @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller

    So if they don't want to migrate to another solution, what would be the sensible option for them, then? Running it on a virtual host with two VMs - SQL and RDS with the ERP app installed? Thanks.

    Yes. Screen scrapes don't require much. Keep RDS in a DMZ if it will ever be open to the world, or accessible from a dirty BYOD. Otherwise, over VPN for security.



  • @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller

    So if they don't want to migrate to another solution, what would be the sensible option for them, then? Running it on a virtual host with two VMs - SQL and RDS with the ERP app installed? Thanks.

    Yup, RDS is the industry standard fallback for legacy apps moving to the remote work world. That's the purpose of RDS specifically. If you had modern apps that didn't have legacy issues, you'd effectively never need something like RDS.



  • Thanks @scottalanmiller and @bbigford. Not sure if they are willing to spend $$$ to do it right, though. OMG this SMB is so cheap, they're running this SQL DB on a 5 y.o. Dell tower server with no RAID and a consumer-grade HDD! They do have an internal IT guy but he has no business IT concepts whatsoever, he's more like an IT hobbyist with web design background.

    I keep telling them they're wasting productivity with their disastrous IT. However, the main problem with many small organisations here is they don't see IT as an investment more like an inevitable expense. I believe that now a successful organisation regardless of its size is always built around a reliable and efficient IT infrastructure that serves them as operational backbone.



  • @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Thanks @scottalanmiller and @bbigford. Not sure if they are willing to spend $$$ to do it right, though. OMG this SMB is so cheap, they're running this SQL DB on a ....

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.



  • @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    Their DB is relatively small (6 GB) and they're using free SQL Server Express with it, though. The whole thing should've really been open source and web-based, I agree.



  • @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    Their DB is relatively small (6 GB) and they're using free SQL Server Express with it, though. The whole thing should've really been open source and web-based, I agree.

    Even the free version... it requires the expensive Windows license, it takes extra effort to maintain, and it risks outgrowing the free version to become super expensive.



  • @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    Their DB is relatively small (6 GB) and they're using free SQL Server Express with it, though. The whole thing should've really been open source and web-based, I agree.

    Even the free version... it requires the expensive Windows license, it takes extra effort to maintain, and it risks outgrowing the free version to become super expensive.

    SQL Server Express is available on Linux so that clears up the Windows license.



  • @black3dynamite said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    Their DB is relatively small (6 GB) and they're using free SQL Server Express with it, though. The whole thing should've really been open source and web-based, I agree.

    Even the free version... it requires the expensive Windows license, it takes extra effort to maintain, and it risks outgrowing the free version to become super expensive.

    SQL Server Express is available on Linux so that clears up the Windows license.

    NOW it is, but it wasn't when the choices were made, and not the version that they are stuck on. So maybe going forward that is true, but retains the growth risk.



  • @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Thanks @scottalanmiller and @bbigford. Not sure if they are willing to spend $$$ to do it right, though. OMG this SMB is so cheap, they're running this SQL DB on a ....

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    I disagree with company lacking money doing IT well and running lean, man have I seen many companies with lack of money and IT is well an afterthought or not even run well at all.



  • @dbeato said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Thanks @scottalanmiller and @bbigford. Not sure if they are willing to spend $$$ to do it right, though. OMG this SMB is so cheap, they're running this SQL DB on a ....

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    I disagree with company lacking money doing IT well and running lean, man have I seen many companies with lack of money and IT is well an afterthought or not even run well at all.

    Even if it is an afterthought...

    1. Keeping IT till it is an afterthought is a form of wasting money. It means that at the time they were willing to throw money away for non-business reasons just because they preferred whatever else to having more money (e.g. rich enough that they no longer cared about profits)
    2. Even when it is an afterthought, IT doesn't have to be intentionally wasteful. But when it is, it's because there is more money than people care about.

    I think it's a different in how we perceive being "out of money."

    You guys mostly use an absolute measure of "they don't seem to have loads of cash by my personal standards."

    I use a relational measure of "they have so much money by their own standards that they don't care about throwing it away."



  • @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @dbeato said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @scottalanmiller said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    @taurex said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    Thanks @scottalanmiller and @bbigford. Not sure if they are willing to spend $$$ to do it right, though. OMG this SMB is so cheap, they're running this SQL DB on a ....

    Depends how you look at it. You see them as being cheap. I see them as burning money because it's a big joke to show how much they can throw away. SQL Server is only chosen by the ultra rich with money to flaunt. Companies lacking money do IT well and run lean, companies that laugh in the face of profits because they have so much money that it's all just silly to them select outrageously overpriced products for no reason and run them on old worthless hardware... just because it's funny.

    I disagree with company lacking money doing IT well and running lean, man have I seen many companies with lack of money and IT is well an afterthought or not even run well at all.

    Even if it is an afterthought...

    1. Keeping IT till it is an afterthought is a form of wasting money. It means that at the time they were willing to throw money away for non-business reasons just because they preferred whatever else to having more money (e.g. rich enough that they no longer cared about profits)
    2. Even when it is an afterthought, IT doesn't have to be intentionally wasteful. But when it is, it's because there is more money than people care about.

    I think it's a different in how we perceive being "out of money."

    You guys mostly use an absolute measure of "they don't seem to have loads of cash by my personal standards."

    I use a relational measure of "they have so much money by their own standards that they don't care about throwing it away."

    Who is "you guys"? because now there is a faction of some sort?

    When I say there is no money, I meant it in an absolute sense of them having to cut employees or departments where they invest their money because they cannot afford it.



  • @dbeato said in ZeroTier Virtual Adapter and SQL Express 2012 DB Troubles:

    When I say there is no money, I meant it in an absolute sense of them having to cut employees or departments where they invest their money because they cannot afford it.

    Cutting people while still throwing money away isn't the same as being out of money absolutely, it's just choosing expensive software over people, though.



  • Now it may be that they had money to flaunt that they could burn in the past, burnt it all, and are now living with the consequences of throwing that money away. But that's a little different. They still felt that throwing money away was worth it at the time. Today, sure, maybe they've run out of money, or many they just say that to justify decisions, but at the last time that they were making IT spending decisions....