How Do I Upgrade An Existing Database Schema?
May 8, 2006
I have written some software using .NET 2.0, a WinForms app, which uses SQL Server Express as a database. Although I've done my best to set up the database to take into account all of the features, etc., after releasing the first version, I need to make some changes to the database schema, but want to do so without wiping out the entire database for existing users. How do I go about upgrading the database schema for users who are already using the software? I'm thinking I should start by writing a helper app to do so whose sole purpose is to upgrade the schema. The app would be run at install-time, but I don't have any idea how to actually upgrade the schema. I am using C++/CLR .NET, but examples in other .NET languages are more than welcome. Any help is greatly appreciated. Thanks!
View 1 Replies
ADVERTISEMENT
Mar 21, 2007
Hello,
We utilise SQL scripts, executed via sqlcmd.exe, to upgrade the schema and common data of our database(s) when we deploy new versions of our software to a production site. At the moment we simply wait until after hours to do the upgrade and ask nicely for all users to not use the system for a while.
Obviously, asking nicely doesn't always work, and there is also the issue of scheduled server tasks and web services / web sites that operate against the database 24/7.
What are our options for putting the entire server (or preferrably just one database) into a semi-offline state so that users and services cannot connect to it while our script connects and performs the upgrade? I imagine there may be several approaches each with their own pros and cons.
If you could point me in the right direction or perhaps mention what strategy has worked for you, it would be greatly appreciated. We perform these upgrades from hundreds of kilometers away via VPNs and Remote Desktop so we can't just unplug the network cable :).
Thank you.
Regards,
- Jason
View 4 Replies
View Related
Mar 29, 2007
There is a great book on database refactoring that contains a comprehensive set or recipies on how to revise databases that are supposed to be always online and may have various clients that can't be upgraded at the same time. I guess this is a typical case with large databases and I would be surpised if Amazon stops their servers just to move a column from one table to another. The book describes necessary steps for such changes. Basically it's all about creating intermediate database schemas that would be used during transition period.
For example, if we need to move a column from one table to another:
Version 1.
Table A columns: Name, Price
Table B columns: Quantity, Date
Let's say we move Price to table B:
Version 2.
Table A columns: Name
Table B columns: Quantity, Date, Price
The book suggests an intermediate version:
Version 1_2.
Table A columns: Name, Price
Table B columns: Quantity, Date, Price
Additional trigger that will synchronize "Price" columns between A and B.
Version 1_2 can be used by both clients written for version 1 and 2. Software developers don't need to rush their upgrades, transition can last months and include several changes.
This technique requires accuracy in version control management, but looks very good to implement non-interruptible database schema upgrade. I wonder if this is the only option available for data schema upgrade with no downtime. I can't think about anything else - it this how large data warehouses updata their databases?
View 1 Replies
View Related
Sep 2, 2015
I have an existing, licensed installation of SQL 2008 R2, with one database instance.  I need to add a second instance, but do not currently have access to the installation media.  Can I use the Microsoft® SQL Server® 2008 Enterprise
Evaluation: Trial Experience for IT Professionals installer that's available for download from the Microsoft download site?
View 3 Replies
View Related
Oct 9, 2007
How to rename an existing schema in SQL 2005?
My guess is may be using ALTER SCHEMA but books online does not have any info or syntax.
Does all securables within that schema will reflect the new schema name?
View 7 Replies
View Related
Jul 20, 2005
What is the best method of creating schema creation scripts that can bestored into a version control system. The process of using em togenerate a script is not an appealing option. I am still learning theMS Sql sys tables and have not found a useful list of all the codes &types to join the tables etc.mike--Posted via http://dbforums.com
View 2 Replies
View Related
Apr 1, 2008
Hi, trying to affect schema changes on an SQL DB using however Im encountering a number of errors when trying to do this. Changes are are affected by running a consecutive number of scripts against the DB, see example of script below:
-- PREVIOUSDBVERSION=2.8.2.5
-- The above line must be present in all Upgrade SQL files to catch any gaps in the upgrade chain.
--
--ALL
--
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping Stats from A DB'
GO
DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name
FROM sysindexes
WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @tblname, @statname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM c INTO @tblname, @statname
END
CLOSE c
DEALLOCATE c
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
The database I am upgrading has a growth limit of 2GB, when running the scripts against the DB two things are happening.
1) DB size begins to increase rapidly and eventually breaches 2GB mark
2) LDF file swells to upto 3GB
My first thought is that the schema changes are gathering in the log file and not being commited to the DB, or that there is some kind of open transaction that the script is constantly trying to execute over and over.
Would like to point out im a novice SQL user, if the information above is sketchy please dont hesitate to say and I will endeavour to provide you with more accurate information.
Cheers
View 1 Replies
View Related
Aug 15, 2006
Hi,
I have the same database(3GB) in 2 physical locations in usa and europe
I need to create a publisher and subscriber between these 2 database without sending any snapshot because i have the same data in the 2 sides, is it possible ?
Thx
Jim
View 13 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
Aug 13, 2007
Hi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
View 3 Replies
View Related
Oct 8, 2006
I just downloaded the "SQL Server 2005 Express SP1", but I found that there is no upgrade option, it will just install a new instance of SQL 2005 Express. So, I just curious to know, how do I upgrade to SP1 smoothly?
Thank you!
View 1 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
Jun 10, 2007
Current set-up: three node cluster running Windows Server 2003 Enterprise Edition SP2 with two instances of SQL Server 2005 Standard Edition SP2.
Virtual node 1: Standard Edition, default instance, Database Engine only
Virtual node 2: Standard Edition, named instance, Database Engine, Analysis Services and Integration Services
Physical node A: Prefered node for virtual node 1
Physical node B: Failover node for both virtual node 1 and 2 (thus having two installations of Standard Edition)
Physical node C: Prefered node for virtual node 2
We started off with this set-up because there was no need for Enterprise features, and SQL 2005 supports failover clustering with two nodes in Standard Edition. Saved a bundle of cash, so everybody's happy. Now we need to run a solution on virtual node 2 that requires Enterprise features in Analysis and Integration Services, so an edition upgrade is required on virtual node 2. I have done some research and found several edition upgrade examples, but none that match the above scenario. We are running production on these machines, so I can't risk experimenting with command line options, I have to be right the first time.
Is it possible to mix two editions of SQL Server 2005 in one cluster? Can I upgrade virtual node 2 to Enterprise but leave virtual node 1 at Standard? What procedure do I have to follow to upgrade all three components of virtual node 2 to Enterprise Edition on both physical node B and C?
Any ideas will be greatly appreciated.
View 3 Replies
View Related
May 14, 2015
How can i add additional SQL server instance to existing clustered environment on SQL 2005.
View 7 Replies
View Related
Oct 7, 2015
If I install a new named instance of SQL Server 2008 R2 on a machine that already has 3 named instances that are upgraded to service pack 3, will the install of the new instance overwrite any of the current SP3 files? Will I have to re-apply SP 3 to the existing instances?
View 4 Replies
View Related
Jul 28, 2006
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
View 3 Replies
View Related
Jan 11, 2006
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies
View Related
Mar 21, 2007
The error message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
View 3 Replies
View Related
Aug 6, 2015
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
View 5 Replies
View Related
Feb 20, 2007
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
View 1 Replies
View Related
Sep 24, 2007
Hi guys,
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
Cheers
View 1 Replies
View Related
Sep 21, 2007
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data.
Does anyone know how to do that?
Many thanks for replying.
View 7 Replies
View Related
Feb 15, 2006
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master]
go
create database [test]
go
USE [test]
GO
CREATE ROLE [ReadOnlyRole]
GO
USE [test]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO
drop role [ReadOnlyRole]
go
View 12 Replies
View Related
Apr 17, 2012
I am new to sql and my boss want me to write the program for database and he wants to pull the info from existing table from sql server which is used by Microsoft GP Dynamics and write me code or some kind of front end so when he wants he can pull same data from GP dynamics table and generate some report with other custom table.
How i can do this task? What I have to do in sql so I can use same table to view info in real time so that means if i enter new data in the table it will show up same time in the front end as well.
View 4 Replies
View Related
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Apr 28, 2005
What would be the best way to get the Schema from a complete database.
Thanks.
View 1 Replies
View Related
Sep 9, 2005
hello,
here is my problem.
I have to rebuild database after crash. there is no backup. So
I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...
thanks lot for any help.
View 1 Replies
View Related
Mar 16, 2007
Hello All,
My project uses MS SQL server database and is not too big database (have aound 200 table).
Now I have to create Database schema for my database as my project needs to be integrated with some other product.
I don't know much about database schema and how to start with it.
Can someone please give me some inputs on following:
1) What exactly database schema should include?
2) How should I start creating the schema for my database?
3) Are there any tools for doing this?
Thanks in advance
View 12 Replies
View Related
Oct 24, 2005
Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???
View 14 Replies
View Related
May 24, 2007
We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.
Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).
Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?
Thad
View 4 Replies
View Related
Oct 26, 2007
We have a RS 2000 server with a over a hundred reports, and about half as many weekly and monthly subscriptions.
In reply to my request to upgrade to RS2005, the boss asked me today for a compete list of the reports, who subscribes to them, and their delivery frequency.
He was not interested in paying for a VB or C# development effort with the SOAP API that it would require to obtain a simple list from a SQL server database, since he already has SQL programmers on staff.
So how can I get this list? Anyone know of any demo code out there?
Forgive my sarcasm and Thanks in advance.
Herb
View 4 Replies
View Related
Oct 23, 2007
Does anyone know which database table/view one can query to get a list of all objects that have been changed by users accessing a database?
Is there any? or is there some other way of doing this?
View 1 Replies
View Related
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
