suspend or stop SQL service at the weekend

Not sure exactly how to do it with SQL Server, but in Oracle, the easiest way to do this would be with a logon trigger.  The logon trigger checks the date and time and if it is outside of the window you want to allow users on, then it throws an error and the login is denied.  SQL Server seems to support logon triggers, so you should be able to do it.  You just need a script that runs in addition to the trigger that will kill all user sessions, this prevents people from staying logged in.  It doesn't sound like you need to stop the service, you just need to keep people from logging in, and if they cannot log in, they cannot do anything.

A SQL job can stop SQL Server (you can issue a net stop command).

You'd need a NT task/job to restart it.  You can use net start for that.

@Scott
Thanks, But if the server restarted then it will be back to run again

You'd want it to automatically start sometime Monday morning, right?  So that it's ready for the work day?

The management won't allow users to access applications out of work time

I would just schedule enable and disable a GPO that blocks the application/SQL ports with the Windows Firewall. Do not stop and start SQL

This might be useful

Windows Firewall as Code
https://www.experts-exchange.com/articles/31687/Windows-Firewall-as-Code.html

Yes, i need to stop it on Saturday night and start it back in Monday morning.
but even if server rebooted for some reason the services will not back until Monday morning

That's really a business decision not a technical one.  I wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.  I'm assuming non-app business activity could still take place on the weekend, and blocking ports could prevent that.  But, again, the business needs to decide which they really need or prefer.

but even if server rebooted for some reason the services will not back until Monday morning

Good point.  I forgot to mention, be sure to set the service to manual over the weekend, and only back to any level of automatic on Monday.

That's really a business decision not a technical one.  I wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.

No, I would avoid stopping SQL unless I had absolutely no choice. It will prevent any SQL jobs, maintenance and backups from running. Blocking a port will have none of these effects

I'm assuming non-app business activity could still take place on the weekend, and blocking ports could prevent that.

You can configure Windows Firewall to allow/block specific users, groups, computers, app, ports etc. so, no, it won't prevent other business activities if properly configured

You can configure Windows Firewall to allow/block specific users, groups, computers, app, ports etc. so, no, it won't prevent other business activities if properly configured

And how much on-going configuration is that?  How many never-ending hours of adjusting users, groups, ports, etc., to keep everything straight?

You've also now prevent remote logins to the instance for any reason I would think.  How do you get in to address, say, a Windows issue?  How does SQL keep functioning if its ports can't be used?  If it can't use the ports it expects to use to communicate to remote resources?  The SQL Agent has jobs that no doubt reference all kinds of resources, except now they can't, so you have massive numbers of failed jobs, some no doubt with partially processed data.  What a nightmare.

I worked at International Paper, with literally 10s of Ks of users.  I can't imagine how we could have used ports instead of just temporarily making SQL unavailable.

I worked at International Paper, with literally 10s of Ks of users.  I can't imagine how we could have used ports instead of just temporarily making SQL unavailable.

Lol, just one of my clients is 10K. Fully CIS, firewall on every endpoint fully automated

Stopping SQL service is an all or nothing action.

You can do the same with a Port block without screwing with SQL and without any admin overhead as you put it.

Right, sure.  So how you keep the SQL Agent jobs from failing with references to things they can no longer get to?

@Scott Pletcher

Good point.  I forgot to mention, be sure to set the service to manual over the weekend, and only back to any level of automatic on Monday.

you mean to set SQL Service to manual start not automatically, if so then if server restarted any time during the business day SQL Service will not start again unless i started it, or i need to set a Scheduled job on startup every day except Sunday.
am i right

Right, sure.  So how you keep the SQL Agent jobs from failing with references to things they can no longer get to?

You allow access to those host that you need to get to. A simple IP or host name exception. Most environments already have an AD group with all SQL computers in

netsh advfirewall firewall add rule name="Allow all from 10.0.0.15" dir=in action=allow protocol=ANY remoteip=10.0.0.15

Select allOpen in new window

Interesting.  You just said you "block all ports" now you say you open them up?

It seemed the main idea is that data is NOT supposed to be loaded during this time.  I don't see how you block access to the data -- to prevent it from being loaded -- but still have jobs run.  I guess you have to modify all the job schedules to keep them from running on weekends: wow, I'm glad *I* don't have to try to do that!

All this is why I like the logon trigger idea.  You have a table with hours that you want the system available.  Run a script as a scheduled job that kills all sessions, then the logon trigger prevents anyone from connecting.  Since it is time based and table driven, if the server reboots, nobody cares.

You have to build in some type of backdoor to the login trigger, in case you do need to get in during the weekend for some special reason.

I think it's bizarre to keep SQL running but allow no one to come in.  Then you'd be constantly fighting to insure that no one gets in.  You'd be far better off to stop SQL and disable the SQL services until you wanted them run again.  No special code, no masses of port changes, just service changes.  Or, to allow reporting only, change all user dbs to read_only (which does not seem to be the case here, OP seems to want to stop all app activity).  [ Certain db tuning would also be easier to do with the instance down, such as defragging drives. ]

Btw, when the dbs move to Azure, how do you manage the port access then?  Wouldn't it just be easier to stop the db(s) on Azure (and save the money)? (Standard Azure doesn't have traditional instances, it's more dbs (and "servers")).

You can build in a back door, or just read the documentation.  There is a documented way to disable the logon trigger when it is preventing logins, by logging in as an administrator in a certain way.  Pretty well spelled out in the documentation link.

I wouldn't count on them being familiar with DAC, and the restrictions on its use.

You'd also have to create such a trigger on every instance.

Sorry for trying to suggest something different.  Certainly works for us, allows easily changeable blackout windows.  "Backdoor" is simply a user check, we have designated admin accounts and they are always allowed through.

I don't think anyone objects to alternative solutions. It's just a matter of deciding on the best alternative.

Interesting.  You just said you "block all ports" now you say you open them up?

You block SQL inbound (my article has like to dynamic ports for SQL) and everything outbound is allow. A trivial task except for someone stuck in their ways

It seemed the main idea is that data is NOT supposed to be loaded during this time

Show me. This is OP's comment. The management won't allow users to access applications out of work time

I don't think anyone objects to alternative solutions. It's just a matter of deciding on the best alternative.

Yes, you are.

As are you, determined that "port blocking" is the only way.

The OP has his/her choice.  I still view port blocking as way too complex here.  If you don't want people to use an app, shut down that app, don't play ring-around-the-rosey with it.

As are you, determined that "port blocking" is the only way.

I am not. I am responding your silly statements such as the ones below.

wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.

And how much on-going configuration is that?

Interesting.  You just said you "block all ports" now you say you open them up?...'m glad *I* don't have to try to do that!

I still view port blocking as way too complex here.  If you don't want people to use an app, shut down that app, don't play ring-around-the-rosey with it.

wouldn't mess with blocking and unblocking ports unless I had absolutely no choice, [because] it's  too much on-going configuration

What's wrong with that?  That's my view.  That's far more complex to me than just shutting down an app.

I'm staying out of this from now on.

The idea here is to keep users out.

Blocking ports and/or shutting down the database will keep users out, but what scheduled maintenance tasks will be affected?

Online line of configuration too much? Really?

Blocking ports ----- will keep users out, but what scheduled maintenance tasks will be affected?

None, you block all users, not servers. All outbound connections from the server is allowed

And you consider that way simpler than just shutting down one service or app??

No, but I can allow access selectively whilst allowing SQL to run and happily continue . Stopping the service cannot. Not going to repeat that to you again, clearly you are not open to other opinions.

When I implement SQL etc. database is firewalled off so that only application/web servers can access them. Clearly this is not a common practice in other places, good luck

Thanks All