4

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?

6
  • 3
    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.
    – joeqwerty
    Jul 16 at 21:13
  • 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? Jul 18 at 7:00
  • 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? Jul 18 at 7:42
  • Why are you changing to UTF8? Jul 18 at 11:44
  • @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. techcommunity.microsoft.com/t5/sql-server/…
    – Greg Askew
    Jul 18 at 12:52
17

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.
1
  • 3
    XY Problem. Oh god thats beautiful. I almost wept... ;) Jul 18 at 8:12
7

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.

2
  • 1
    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. Jul 17 at 20:45
  • 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.
    – Doug
    Oct 3 at 3:43
0

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.

2
  • What is "Redgate SQL"? Part of Redgate Data Tools? ReadyRoll Core? SQL Prompt Core? SQL Search? Something else? Jul 18 at 10:10
  • @PeterMortensen My apologies. "Redgate SQL compare" can be used for synchonising databases. Jul 18 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.