DB Engine :: Upgrade Two Nodes Utilizing AlwaysOn Of Some Other Method Without Downtime?
Oct 15, 2015
Two servers are configured with Windows 2008 / SQL server 2012 utililizing Always-On for HA. We need to upgrade both servers to Windows 2012 / SQL Server 2014 with minimum downtime(Time for Always-On failover). The upgrade to SQL 2014 is straight forward with for minimum downtime.The Windows upgrade(2008 -> 2012) is the problem. From what I have observed and read in blogs.The Windows node to be upgraded must be removed from the Windows cluster before the node can be upgraded to Win 2012.A Win 2008 and Win 2012 node can not reside in the same cluster. If this is true then the only option I can think of is to dump the DB on WIN 2008 server and restore on Win 2012. This is an outage(time it takes to dump and restore).Is there any other method to upgrade these two nodes utilizing Always-On of some other method without downtime?
Hello,I'm upgrading from SQL 7 to SQL 2000 on another box. To minimize thedowntime I would like to1) backup my sql 7 database,2) copy it to the new box with SQL 2000 already installed,3) restore the database on the SQL 2000 box,4) Shutdown my sql 7 database,5) Copy the transaction logs to the SQL 2000 database,6) Restore the transaction logs to the SQl 2000 database,7) Bring up SQL 2000.My only concern with this is restoring the transaction logs that werecreated on SQL 7 to SQL 2000. Do you know if I can do this?Do you see any (other) problem(s) with my plan.Thanks, Scott
How many nodes can you have in a cluster with SQL 2012 alwaysOn.
I understand that availability groups are limited to 5 nodes but if you had a 10 node cluster and decided to create multiple availability groups using various nodes within the 10 nodes but never exceeding 5, is that possible?
Or is there a counter or some validation from SQL AlwaysOn that actually hard limits to a grand total of 5 nodes in a cluster?
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?
I have a 2012 AlwaysOn DB Mirroring environment set up with two nodes. Both have 5 installs of SQL named instances.
The issue we are having is when we patch one server and fail everything over, some of the applications will error. Some of the applications had to have their web.config files updated with hostinstance name because it seems to not work with DNS.
Dear ppl, I want to shred the following XML so that i can get all the Warranty(s) with id and text.
Required Output
id WarrantyText 1 1 year parts and labor 2 2 year parts and labor
XML
declare @myDoc xml set @myDoc = '<ProductDescription ProductID="1" ProductName="Road Bike"> <Features > <Warranty id="1">1 year parts and labor</Warranty> <Warranty id="2">2 year parts and labor</Warranty> <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> </Features> </ProductDescription>'
I am using the following query but it doesn't seem to work
select C.value('@id', 'nvarchar(max)' ) as ID, C.value('Warranty', 'nvarchar(max)' ) as WarrantyText from @myDoc.nodes('//Warranty') T(C)
I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.
I have a XML file (download XML File below at URL) stored in a SQL Column of XML type. The column with the XML file is named CurrentPackageXML in a table called dbo.
I am using alwayson on my SQL 2012 databases. I am using ola hallengren scripts for backing up databases. Full & diff db backups work fine, but the log is not getting backed up. The tran log backup job doesn't error out too. Trying to figure out what I may be missing?
We have a database with AlwaysOn enabled, where the database is getting mirrored using AlwaysOn to a secondary node in a two node cluster. Now we want to set up a DR site for that database in a different data center.
So trying to analyse what technologies. We can use for setting up the DR for that database. Can we use mirroring / replication or what's the best way of setting up DR on that database.
We have a reporting job that will query the mainDB and do so come calculation and insert into reportingDB, there are a lot of blocking and performance issue when it query the mainDB so we have plan to read the secondary replica and do the processing there later to insert back into primaryDB (reportingDB).. for this we have implemented linked server but it gets timeout, I do not want to change the remote query timeout on server level so is there anyway to do this, Any one making use of secondary replica to do get the data by job?
I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary Server 4 as Subscriber Server X as Remote Distributor ..
as of now , In the event of Disaster happens on Node 1 i will have to manage JOBS on Node 2 (backups, Maintenance, replication, day-day other Jobs)..Is there a way i can call all jobs to run from a remote server which executes on Active NODE(which ever is active)? so that in the event of disaster happens on node1, when node 2 becomes online and i don't have to do anything with SQL Agent or jobs on Node 2?
I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .
Is it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.
Just downloaded and installed mssql 2005 sp2 on one of our servers
All components upgrade apart from the database engine. After the first upgrade i rebooted then tried to update the db engine again it failed for a second time.
Below is the error from mssql setup, any help would be nice!
Event Type: Error Event Source: MsiInstaller Event Category: None Event ID: 1023 Date: 2/20/2007 Time: 12:08:11 AM User: DNETWORKdadmin Computer: SGC Description: Product: Microsoft SQL Server 2005 - Update 'Service Pack 2 for SQL Server Database Services 2005 ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
I need some help please. I've got SQL 2K (default instance) and SQL 2005 Express installed on Vista Ultimate. I want to upgrade the 2K instance to 2005 Developer. I ran this from the command prompt:
D:ENGLISHSQL2005DEVELOPERSQL Server x86Servers>setup.exe ADDLOCAL=All SKUUP GRADE=1 UPGRADE=SQL_Engine INSTANCENAME=MSSQLSERVER
This is the error message I received (from Summary):
-------------------------------------------------------------------------------- Machine : HPMEDIACENTER Product : SQL Server Database Services Error : SQL Server Setup cannot upgrade the instance because the uninstall file is missing or corrupted. The error code is (87) The parameter is incorrect. -------------------------------------------------------------------------------- Machine : HPMEDIACENTER Product : Microsoft SQL Server 2005 Product Version : 9.00.1399.06 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HPMEDIACENTER_SQL.log Last Action : ValidateUpgrade Error String : SQL Server Setup cannot upgrade the instance because the uninstall file is missing or corrupted. The error code is (87) The parameter is incorrect. Error Number : 29523 --------------------------------------------------------------------------------
From the Log:
Property(S): PrimaryVolumeSpaceRemaining = 0 Property(S): RSVirtualDirectoryManager = Reports Property(S): RSVirtualDirectoryServer = ReportServer Property(S): SqlActionManaged = 3 Property(S): SqlStateManaged = 2 Property(S): Sql_sqlSqlUpgradeSequence = 714057000 MSI (s) (14:5C) [22:19:35:714]: Product: Microsoft SQL Server 2005 - Update 'Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HPMEDIACENTER_SQL.log. MSI (s) (14:5C) [22:19:35:718]: Windows Installer installed an update. Product Name: Microsoft SQL Server 2005. Product Version: 9.00.1399.06. Product Language: 1033. Update Name: Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000). Installation success or error status: 1603. MSI (s) (14:5C) [22:19:35:719]: Note: 1: 1708 MSI (s) (14:5C) [22:19:35:719]: Product: Microsoft SQL Server 2005 -- Installation failed. MSI (s) (14:5C) [22:19:35:719]: Windows Installer installed the product. Product Name: Microsoft SQL Server 2005. Product Version: 9.00.1399.06. Product Language: 1033. Installation success or error status: 1603. MSI (s) (14:5C) [22:19:35:720]: Attempting to delete file C:WindowsInstaller1dcfcb3.msp MSI (s) (14:5C) [22:19:35:720]: Unable to delete the file. LastError = 32 MSI (s) (14:5C) [22:19:35:733]: Cleaning up uninstalled install packages, if any exist MSI (s) (14:5C) [22:19:35:733]: Attempting to delete file C:WindowsInstaller1dcfcb3.msp
I can't locate anything more about Hotfix 3024...maybe it's in Area 51. Or Studio 54. Or Blink 182...
I can't install the SQL Server 2012 Express version with tools downloaded from [URL]
I'm getting the following error:
Error installing SQL Server Database Engine Services Instance Features Could not find the Database Engine startup handle. Error code: 0x851A0019
I'm trying to install a new stand-alone version with a default instance but always getting this error. From the features I'm installing the database engine services (not the replication), all the tools, only the basic management toolds and the SQL Client connectivity SDK.
Further in the installation I'm choosing the Windows authentication and in the list of SQL administrators I've already tried to install as current user but also as System. Both with the same error result.
We have installation of Dbase Engine and SSIS that is PRODUCTION, and want to replace with newer hardware. In "the old days", we built "boxname_new" and installed SQL with "sqlname_new", took PROD users off-line, and quickly renamed original boxes/SQL and new boxes/SQL to original name, copied data and off we went with upgrade.
NOW, the "renaming" option for SQL tools is not supported, but with re-installation.
Has anyone developed game plan steps for accomplishing hardware upgrade, including SQL environment swap with MINIMAL downtime for PRODUCTION environment? Can you share?
Is it possible to force the use of a compound index in a query?create table Test (ColOne int, ColTwo int)The compound index is ColOne + ColTwo.I'm interested in searching on ColTwo, but I also know the value ofColOne will always be the number "1".How do you structure the SQL statement to concatenate the two INTs anduse the index? Note that I don't have any control over the creation ofthese indexes.
We are going to upgrade one of our SQL Server 2000/Win200 instance to SQL Server 2005/Win2003. Currently, the SQL Server 2000 which contains about 30 database. This is my plan:
Install SQL Server 2005/Win2003 on a new instance Backup SQL Server 2000 database instance (how do I back all 30 database at the same time?)
Then do a Restore from the SQL Server 2005 instanceIs this a good method or using the "Copy Database Wizard" is better method?
We've set up a report farm with two servers, both 64 bit with 4 CPUs each. One has 16Gig and the other 8Gig of memory. We're using Windows NLB and the load test software confirms that the NLB is working. When we run a number of concurrent reports, both servers get utilized, but they only work on a few at a time. The report server queue doesn't seem to be fully utilizing the hardware. From a prior post I've learned that the report server queue automatically runs 4 reports per CPU. This is not occuring for our setup. Has anyone else experienced the same? Are there any configurations that need to be set to open the queue up? The reports are heavy (300,000 records grouped and summed). Does this affect the queuing process?
I had some SQL queries which are using department ID for join , filter , Group By and Select so , i am having index on department ID of my table File Master scheme ..
The above index only working when there is condition or group by on department ID .and i when i am querying ..
SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward FROM dbo.FILE_MASTER AS CL GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE
and the plan is showing Index scan on index Indx_FM_department_ID .. Why it is not using Index seek , i guess i have both group by Columns in cover index included columns what is the use of cover index then ?
because if i am giving where condition before group by for specific Client ID , Loan Sanction Date it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..
We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs
Processors: 2x2674MHz processors Memory: 4GB RAID 10 disk config
When we run our process on this machine, in total it runs in 30 hours.
When this process is run on another 32 bit server with the following specs
Processors: 8x3658MHx processors Memory: 8 GB SAN w/ RAID 5 disk config
It runs 25% slower
But here is the real kicker. When this process is run on a 64 bit server with the following specs
Processors: 8x3658MHz processors Memory: 8 GB SAN w/ RAID 5 disk config
It runs 75% slower.
This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.
The default configuration settings in all three places.
Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
My SQL Server 2005 Database was down last night.From logs I can find out below details only.
Code Block Date 11/30/2007 1:01:34 AM Log SQL Server (Archive #1 - 11/30/2007 1:01:00 AM) Source spid4s Message SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
Now I want to find out root cause for this.Who has stopped this service.And If services was stopped automatically then which program/service is resposible?
So Please suggest me how to do root cause analysis for this downtime.
I am trying to create an alert on my new SQL 2005 box to email alert/notify when/if the service ever stops, etc. Is there an existing alert I can use or do I have to script one, and if I do - how would I do it?
I am trying to create an alert on my new SQL 2005 box to email alert/notify when/if the service ever stops, etc. Is there an existing alert I can use or do I have to script one, and if I do - how would I do it?
Hi, I just have a Dataset with my tables and thats it I have a grid view with several datas on it no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error Unable to find nongeneric method... I've try to create an Update query into my table adapters but still not working with this one Also, try to remove the original_{0} and got the same error... Please help if anyone has a solution
I'm preparing a checklist for myself before getting ready to migrate from 2005 to 2012. Our largest database is a nice one at over 250GB. I'm thinking my best bet to minimize any downtime would be to Restore the DB (NORECOVERY) on the new server and keep rolling it forward with the transactional logs. Eventually I'll need to bring the old DB offline and do one last backup and apply that one to the new server but that should be a small time frame given the whole process could take several hours.
1. I am looking data base solution for highly critical application. what type of HA/DR solution will suite for this.(always on, transactional , log shipping or mirroring). make sure all secondary nodes should be identical to production .
2. for any need to upgrade /patching /maintenance work, production should not be able to down. No downtime 100%.
