Avatar of marrowyung
marrowyung
 asked on

SQL server 2008 still up and running during Schema change

Dear all expertist,

we are using SQL server 2008 standard, we might need to know is there any solution that enable the full function of SQL 2008 (read + write+replication) during any schema change?

For any kind of service pack upgrade, we can do rolling upgrade. But for schema change, we have no solution, please suggest.

DBA100.
Microsoft SQL Server 2008MySQL ServerOracle Database

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
DBAduck - Ben Miller

If you make schema changes when you are using Transactional Replication, then they will be sent to the subscriber.

http://msdn.microsoft.com/en-us/library/ms147331(SQL.105).aspx
marrowyung

ASKER
dbaduck,

No, my question is about upgrading SQL server schema while SQL server still keep running, not about replication.

DBA100.
DBAduck - Ben Miller

I may not completely understand the question, but if you are looking to do an online schema change while allowing other processes to write this cannot happen.

SQL Server takes locks on the Schema when doing a change, and if you tried to Write data to that table while a schema change was happening, this couldn't work because of database integrity that SQL must enforce.

While a Schema change happens, Reading can still be done if you have READ COMMITTED snapshot isolation enabled on the database.  Then reads can happen with the previous snapshot of data that exists before the schema change.

So during a schema change, Read, yes, write no.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
marrowyung

ASKER
dbaduck,

Great, it seems SQL 2012 AlwaysOn can solve that ?

I read something like this:

If a new NOT NULL column is added with a default value to a SQL Server 2012 database table, only a schema lock is required to update system metadata; all rows do not have to be populated during the ALTER TABLE statement.
SQL Server will physically persist the default column value only if a row is actually modified or re-indexed. Queries return the default value from metadata, unless an actual column value exists

But what is this means ?
ASKER CERTIFIED SOLUTION
DBAduck - Ben Miller

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
dbaduck,

I dont' understand, which wording is about schema ? the metadata?

Here are some kind of update from some forum:

can be achieved to most extend with SQL 2012 and to some extend in SQL 2008 but making sure that the incoming connections from application  are for read only and not for update.
So in SQL 2012 - Alwayson feature, one server will be engaged in servicing select request from users the other server can be used for schema upgradation

Do you think if this is true?
marrowyung

ASKER
dbaduck,

I can't see any link say that one is true:

can be achieved to most extend with SQL 2012 and to some extend in SQL 2008 but making sure that the incoming connections from application  are for read only and not for update.
So in SQL 2012 - Alwayson feature, one server will be engaged in servicing select request from users the other server can be used for schema upgradation

Do you have one ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
Can't fully answer my question but thanks dBduck for all help !


But what I found out is:

"This can be achieved to most extend with SQL 2012 and to some extend in SQL 2008 but making sure that the incoming connections from application  are for read only and not for update.
So in SQL 2012 - Alwayson feature, one server will be engaged in servicing select request from users the other server can be used for schema upgradation
"