Score:4

Can you temporarily turn off database changes in Microsoft SQL Server?

ru flag

In a coming update, I will need to convert multiple tables in our server from UTF-16 to UTF-8. I expect this will take multiple hours.

Is there a good way to turn off INSERTS and UPDATES, but still allow SELECTS in the database?

Or do I need to block changes at the business level / turn off the database while the update is happening?

joeqwerty avatar
cv flag
You can put the database into read only mode, but I have no idea what impact that will have on your application nor if that will prevent you from making your updates/changes.
Sir Swears-a-lot avatar
cw flag
It might help if you can clarify. 1. Is an outage possible/acceptable? If so for how long? How big is the db? How many GB? How many tables? How many inserts/updates would you expect per hour?
Sir Swears-a-lot avatar
cw flag
What version of SQL are you on? What collation is your db set for? Are you literally going to convert column collations? Or are you just changing from nvarchar to varchar?
Sir Swears-a-lot avatar
cw flag
Why are you changing to UTF8?
cn flag
@SirSwears-a-lot: UTF8 is a better fit for many data consumers. Similar to using datetime2 instead of datetime. For large volumes of data in a typical western encoding it saves storage as most characters are represented in only eight bits. https://techcommunity.microsoft.com/t5/sql-server/introducing-utf-8-support-for-sql-server/ba-p/734928
ru flag
@SirSwears-a-lot in my case, it's because I've been forced to entirely redesign which app reads from the database, which includes switching from a library that only reads/writes utf16 to one that only reads/writes utf8.
Score:17
cn flag

You might have an XY Problem. There are ways to do what you're attempting that don't require the tables being operated on to be unavailable for mutation for the duration of the operation. Here's how I'd swing it (high level):

  1. Add a new column with the desired definition (data type, collation, etc).
  2. Use 'after insert' triggers to make sure that data mutations coming from your application also mutate the new column.
  3. Backfill the existing data in the table.
  4. Drop the old column, rename the new column to have the old column's name.
Sir Swears-a-lot avatar
cw flag
XY Problem. Oh god thats beautiful. I almost wept... ;)
Score:7
in flag

Without further knowledge of your database schema, permissions, etc. and some idea of your strategy with respect to your application it is exceedingly difficult to be prescriptive. At a minimum, does the application even allow you to operate in a read-only mode with the table updates blocked?

At a very basic level, "Yes" you can do that... DENY INSERT, UPDATE, DELETE ON <table> TO <user> however it is impossible to say how your application will react. In my experience most applications will splatter errors everywhere and cry bloody murder if you do this, possibly even corrupting the data (unchecked errors, poor use of transactions, etc.). Rarely (possibly never) have I seen an application that gracefully switched to a read-only mode when database access was not as designed/expected.

So TEST! Test, test, test, in a controlled non-production environment until you have a documented process supporting your change.

If your requirements do not allow for an extended outage (or no outage at all) there are more sophisticated ways to handle this. One thought would be to add an after insert / after update trigger to automatically convert new /updated records in a new field while you run maintenance on smaller batches. Once all the data is converted, switch the application to the new field and drop the old one.

dave_thompson_085 avatar
jp flag
Somewhat ironically, Stack itself a few times a year does testing or experiments which make the (relevant) db readonly for a moderate period like half an hour, and as far as I have observed (from outside) they do it quite cleanly: there's a noticable but not obnoxious yellow box at the top of each page announcing the status and clickables that would require writing are disabled.
Doug avatar
in flag
Yes, but the magic is in how they do it. I've seen many applications in which there is a configuration button to stop updates, however it's only really read-only at the application layer rather than the database. I.e. the application stops allowing users to change records, but in fact the application could still write to the database. A complex system (StackExchange) would have a sophisticated application layer read-only switch. I've never to my recollection seen an application that gracefully switched to a read-only mode when it was expecting but denied write at the database layer.
Score:0
cw flag

UTF-8 and UTF-16 aside, I think the bigger question you are asking is how to deploy changes to a production system with minimal downtime. I'm sure there are a few different ways, but this is how I would approach it.

  1. Create a copy of your database.
  2. Run your schema updates on the copy.
  3. Use a product like Redgate SQL Compare, or write your own scripts to synchronise your data. Get them as close as you can
  4. Shut down app, final resynch.
  5. Change the database connection to new the database (Or rename databases).
  6. Restart the application.

I know this sounds simplistic and may not be possible for a variety of reasons, but it would be worth investigating.

I'd want to do this for a few reasons. Minimal outage and impact to users. Safe roll back option. An opportunity to test the impact of schema/data changes.

I appreciate this gets more complicated if there are other apps or integration connections to your database, but winding back a failed upgrade isn't much fun either.

sn flag
What is *"Redgate SQL"*? Part of *Redgate Data Tools*? *ReadyRoll Core*? *SQL Prompt Core*? *SQL Search*? Something else?
Sir Swears-a-lot avatar
cw flag
@PeterMortensen My apologies. "Redgate SQL compare" can be used for synchonising databases.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.