We help IT Professionals succeed at work.

SQL server 2008 still up and running during Schema change

914 Views
Last Modified: 2012-06-13
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.
Comment
Watch Question

DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
dbaduck,

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

DBA100.
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
SQL Server Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
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?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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
"
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.