Link to home
Start Free TrialLog in
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.
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

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
Avatar of marrowyung
marrowyung

ASKER

dbaduck,

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

DBA100.
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.
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
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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 ?
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
"