April 9, 2020 at 7:54 pm
We are FINALLY migrating off our SQL 2000 box to SQL 2017. Our current environment is a single SQL server with a database dedicated for our internal applications and then a separate database for our eCommerce site - with SQL replication running between the databases (both merge and distribution). Our SQL server is NOT in the DMZ. Our eCommerce front end is running IIS on a separate server and that IS in the DMZ with access through our firewall to the SQL box.
What are the current best practices for our move to SQL 2017? We have had different consultants tell us different things.
* Do we continue our current setup with two databases, one being dedicated to our eCommerce site and one for our internal applications? If so, what is the best way to replicate data between the two databases?
* Do we set up a separate SQL server instance for our eCommerce site and put that in the DMZ with our IIS server? If so, what is the best way to replicate data between the two SQL servers?
* Do we use a single database and have both our internal apps and eCommerce site hit that database? That eliminates the need for replication but are there security or performance concerns with this?
* Something else?
What are the pros and cons for each option in terms of security, system performance, costs, ...? Are there sites out there that go over this? Like I said we have had different consultants tell us different things. Whenever I try to search on this all I see are articles discussing if IIS should be on the same server as SQL - which doesn't apply in our case.
Any help is appreciated. I would think there white papers or site out there discussing this, I just haven't found anything too helpful.
April 16, 2020 at 8:10 am
Interesting questions.... and I'm sure many possible answers. My initial thoughts are:
Without going too off-topic about about Active Directories, service accounts, replication credentials, etc, think about this: you shouldn't expose your internal AD to the DMZ (neither private nor public, although sometimes exposing to the private DMZ is acceptable), so your eCommerce MSSQL won't be on the same AD as your internal bizapps (might be standalone, or a separate DMZ-only AD). That makes replication very hard to setup, and AlwaysOn Availability Groups almost impossible.
You could decide that your private DMZ is secure enough, and allow connections in from both the public DMZ (your IIS server) and your internal network (your bizapps). That might be a reasonable compromise, depending on your security requirements and your budget. But you would still have an issue of AD authentication.
Do you really need Merge Replication? Can you manage with a read-only replica of the eCommerce database, available to your bizapps?
These days, if you ask Microsoft, they will advise you to host your IIS apps and database natively on Azure 🙂
April 16, 2020 at 1:18 pm
Thank you so much for the detailed response. If we could get by without requiring merge replication, what would be the best way to accomplish distribution replication from our internal SQL server?
April 16, 2020 at 3:54 pm
You could look into "Always On Basic Availability Groups" but they don't allow a readable secondary (worth checking though).
Next up would be "Domain Independent Availability Groups" although I have never deployed one of them, so can't comment!
My old-school method is Log-Shipping, which I deploy if I only need a readable Secondary (the Secondary db also serves as a warm backup).
Would depend on which way around you want to distribute the data (internal to DMZ or DMZ to internal) and how frequent you require the data updated. There are third-party tools which would allow you push updates from your internal apps to the DMZ (our friends at RedGate would be happy to help).
April 23, 2020 at 5:59 pm
Thanks so much for all your advice. I think we are going to go with a separate SQL server in the DMZ. That way our web server and DMZ have no reason to reach back into our internal network and we can lock the firewall down.
What are your thoughts on SQL Server replication? I did some quick tests and you can set up one-way (transactional) replication from an internal SQL server out to one in the DMZ and it works fine. Is there any downside to this?
April 24, 2020 at 9:24 am
Sure, you could setup replication; but that will tie together your two MSSQL instances, perhaps in ways you don't want; for example, schema changes are difficult; permissions/accounts are awkward.
What do you want to push from your internal MSSQL to the DMZ MSSQL? If you want to push data, then maybe an SSIS ETL task is a better design?
Log-shipping can also do the job, and this keeps your two MSSQL instances separate (there is no direct interaction between the two instances; log files are created by your internal MSSQL, moved through the firewall to your DMZ MSSQL, and then processed). Although the DMZ database will be permanently "recovering" it can be readable (called STANDBY mode).
April 24, 2020 at 2:01 pm
This was removed by the editor as SPAM
April 24, 2020 at 2:10 pm
Sorry if this is re-post... When I first did it, my response got marked as SPAM (???)
What do you want to push from your internal MSSQL to the DMZ MSSQL?
We have probably 40 or so tables on our internal SQL box that need to be replicated out to the DMZ SQL box. Our plan was to use transactional replication for this. Schemas in these tables do change, but pretty infrequently. To the point where we just deal with the hassle of making them when they happen
We also have a separate database (60 tables) we need replicate out from our internal SQL box to the DMZ SQL box. It ranges from 5-10 GB and our plan was to do a replication snapshot for this. In my initial tests, generating the snapshot and applying it takes approx 10 minutes. Since we only need to do it once a day, we can live with that and just schedule it in the early morning.
Log-shipping can also do the job, and this keeps your two MSSQL instances separate (there is no direct interaction between the two instances; log files are created by your internal MSSQL, moved through the firewall to your DMZ MSSQL, and then processed). Although the DMZ database will be permanently "recovering" it can be readable (called STANDBY mode).
We need the DMZ SQL box to be more than read-only. Any transactions done on our eCommerce site need to be saved on there, and since the DMZ SQL box can't reach back internally to the internal SQL box (firewall rules) our plan was to have the internal box reach out every so often to the DMZ box, look for anything new and pull it back in (a poor mans merge replication). If we needed to be able to write to the database, does that mean Log-shipping would not be an option?
Or I suppose we could implement Log-shipping and have the database be read-only. Any updates made on the DMZ box could be saved to a separate database and the internal box could reach out to that to grab any changes. Not sure I like this strategy tho.
Again, thanks so much for your feedback
April 28, 2020 at 4:37 pm
Log-Shipping is per-database, not per MSSQL Instance; you would be able to read the DMZ log-shipped database 99% of the time (the 1% when reads are not allowed is whilst the new log is being applied). Generating a daily snapshot would achieve a similar result. Both have their pros and cons, I would choose continuous log-shipping. In both cases, you would not be able to write to the database in the DMZ (I guess you could if you only replicated some tables, then you could write to the non-replicated tables).
All transactions in the DMZ should be written to one or more separate databases; you then have fun of executing cross-database queries to retrieve the replicated/shipped data; not hard, but hard to setup securely; however, as the read-only databases are not critical (they can be thrown away and recreated) this shouldn't be a big hurdle.
To pull this data in from the DMZ; you could write your own queries to identify the new rows in the DMZ and pull them back into the internal MSSQL; probably the simplest way. Especially as you don't want any kind of push from the DMZ to internal. An SSIS package would achieve the same, and provide ready-made reporting/alerting. I have similar home-made configurations, where an Agent Job pulls all new records from an external MSSQL instance; works very reliably.
April 28, 2020 at 5:58 pm
What do you mean by this (the securely comment)
All transactions in the DMZ should be written to one or more separate databases; you then have fun of executing cross-database queries to retrieve the replicated/shipped data; not hard, but hard to setup securely;
April 28, 2020 at 6:39 pm
Also, from what I have read and what you have stated, the DMZ database that is receiving the logs is 99% readable. I am concerned about the 1% of the time it is not while it is applying logs. If a user is running a query or anything during that time will the query be cancelled? While 1% is low, I am concerned that even a percentage that low will still affect dozens or more users every day (unless I am mistaken how it works)
April 29, 2020 at 4:16 pm
Hi Again,
The 1% is a wild approximation; entirely dependent upon how often you apply the log files to the readable Secondary; whilst the log files are being applied, all other connections are kicked out. You have control over when the logs are applied, so you can manage this....
Cross-database queries: there is a lot to this subject; briefly (very).... a database-level principal in one database, querying a separate database, first has to go "up" to a server-level principal, and then "down" into the separate database and link to another, separate database-level principal. If you are in control of the entire setup, then not too much to worry about. Note, you might have to set TRUSTWORTHY on, for one or both databases, and probably ensure both databases have the same db_owner. There are a few different ways to achieve these queries, this is just one way; certificates and signed Stored Procedures is another way. Or, do like many developers do, and just run everything under "sa" 🙁
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply