SQL Server 2005 - Log Shipping - Missing Sp_change_primary_role And Sp_change_secondary_role
Dec 14, 2006
I have installed SQL Server 2005 and setup a cluster and log shipping. I decided to perform a planned failover and realized that there are no sp_change_primary_role and sp_change_secondary_role stored procedures. I checked on both the SQL Server 2005 installations and could not find them.
My question is :
"Is it safe to get these stored procedures from a SQL Server 2000 database and run them on the SQL Server 2005 database ??"
"Has anyone come across such missing stored procedures in SQL Server 2005 and know the reason why it is so (so that I can avoid it in the future if it due to a faulty installation) ?"
I've got SQL Server 2005 Ent Ed 9.0.1399 and want to change mysecondary Log Shipping server into a primary. I've tried locating thesp_change_secondary_role, but can't seem to find it. Has it beenreplaced with another stored procedure? If so, which are the proceduresto run?On SQL Server 2000, the following were the steps listed:1. Transfer logins2. Run sp_change_primary_role3. Run sp_change_secondary_role4. Run sp_change_monitor_role (this exists in SQL Server 2005)5. Run sp_resolve_logins3 & 5 are what I'd expect to perform if the primary isn't available.N.I.T.I.N.
I'm trying to configure log shipping on a 2005 sql server. I follow the wizard's instructions (see http://technet.microsoft.com/en-us/library/ms190640.aspx) and everything looks right except for the backup job that somehow is not being created on the primary server. Secondary server contains copy, restore and alert jobs.
primary server is unavailable, I couldn't able to demote theprimary server. I ran this query to promote secondary server. I'vetried with different options @terminate=0. I've ensured no userconnections exist. But still getting sqlmint.exe error. Any oneexperienced this problem? I've checked KB article, but still notworkingEXEC msdb.dbo.sp_change_secondary_role@db_name = 'test',@do_load = 1,@force_load = 1,@final_state = 1,@access_level = 1,@terminate = 1,@keep_replication = 0,@stopat = null
The program shortcut "Visual Studio 2005 Command Prompt" seems to be mising from the "Visual Studio 2005 Command Prompt" Start menu added by the current Microsoft SQL Server 2005 Express Edition Toolkit. Where is it? How to workaround?
I am trying to do Download details SQL Server 2005 Samples and Sample Databases (April 2006) -> GettingStartedWithSQLSamples.htm which says "a. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt." but I can find no such command prompt within "Visual Studio Tools", only "Visual Studio 2005 Remote Debugger{, Configuration Wizard}".
What's wrong? How to fix or workaround? I'd install .NET SDK 2.0 to get it's Command Prompt but that's about 570MB merely for a command prompt!
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
I am getting the following error " The specified servername does not exist" when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Hi all, This is my first message in this forum and I am a new bie to SQL server 2005.. I am trying to schedule the backup job for a database in 2005 and I am not able to figure out where it is. I browsed through few topics in the forums and found that I can do using sql server agent in the SQL server management studio. But I do not see the sql server agent in the management studio. Any help is appreciated.
When I install SQL server 2005 and select the option to have Integration Services installed, I cannot see the templates in VS 2005. Is there a way to tell if they have been installed on the harddrive? Is there any way to install them manually?
for the third time I am installing SQL-Server 2005 express and i have an error.
Cannot install sql because .NET 2.0 framework is missing allthough I have the .NET 2.0 framework allready installed and even re-installed it too. When trying again I get the message that there isn't an SSL-certificate present.
My question is, would this probably cause the error when installing?
I've just installed SQL Server 2005 Development Edition x64 on Windows XP x64bit. The actual server (as well as Integration, Reporting Services etc) installs fine, I can connect to it via Visual Studio 2005 fine. However, all of the software tools are missing including Management Studio, Business Intelligence Studio etc. They don't show up in the start menu at all and I can't find them in the Program Files directory.
Anyone know how I can get them back? I've tried reinstalling SQL Server 2005, reformatting Windows XP x64, I'm at my wits end!! Many thanks!
I've seen a few threads on this which seem to suppose the problem is on Express edition rather than Standard edition. Not so here. This is definitely Standard edition, SP3 as seen by running SELECT CONVERT(char(20), SERVERPROPERTY('Edition')) SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel')) In SSMS Under the Management folder there is no "Maintenance Plans" folder. There IS a "Maintenance Plans" folder under the Legacy Folder. I have another instance which is Standard and SP1 which looks just fine.
I have a query that uses the following fields from MSmerge_history:
MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration
Below is the query that I am using:
SELECT
MSmerge_agents.subscriber_name AS SubscriberName,
MSmerge_history.start_time AS SyncTime,
MSmerge_history.runstatus AS SyncStatusID,
MSmerge_history.comments AS Comments,
MSmerge_history.duration AS Duration
FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history
ON MSmerge_agents.id = MSmerge_history.agent_id
WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()
AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN
(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)
My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?
I have several servers that do not have SSMS installed. A couple of them are running as a clustered server. How do I install SSMS on these servers? they are running and have production databases on them. I have tried re-installing the management tools but that didn't work.
When I downloaded the files and after burning the DVD to create the DVD to setup the 7031A - Upgrading to Microsoft SQL 2005 course, all of the executable files (BCBase.exe, Upgrade.exe, InPlace.exe, SideBySide.exe and SQL7Upgrade.exe) are not in the Setup/Drives folder...the only item in that folder is a file called "placeholder.txt".
Please, if anyone knows how to get these exectuables let me know.
I just installed SQL Server 2005 64bit on a fresh system with no other applications loaded on running on the new server.
The only other component that was installed with the SS2K5 database services is SSIS. The base installation and the SP2 patch went on perfect.
I went looking for the Management Studio thru the Start menu options and it is NOT under the Microsoft SQL Server 2005 group. The only options under the SS2K5 group are "Configuration Tools" and "Docs and Tutorials".
Here are file names that I downloaded from the MS website and installed.
SW CD SQL Svr Enterprise Edtn 2005 64Bit X64 English #1 x64 MLF.x11-57796 (which is a zipped)
SQLServer2005SP2-KB921896-x64-ENU
There was also another zipped file that I tried to install but it seems to be a duplicate of the zipped file above:
SW CD SQL Svr Enterprise Edtn 2005 64Bit X64 English #2 x64 MLF.x11-57797
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
I'm new with SP v3 and the Reporting Services of SQL Server 2005.
I did a prototyp report and show it with "SQL Reporting Services Report"-WebPart, but I can't find the WebParts Report Viewer and Explorer in the WebPart Gallery.
Please, could somebody give me a clue where the problem could be, or how I could isolate the problem area.
After some weeks evaluating tools and platforms for developing an application, I decided to move to SQL Server 2005 Express Edition. Everything was fine till last night, when after creating my tables, I needed to populate them. I tried to find the Import and Export Data Wizard that SQL Server 7.0 and 2000 used to have, but great was my surprise when I found - in this forum - a post that said that it's not available in the Express Edition.
I'll have to move back in time (what I hate) to remember the way BCP worked. Can somebody post some examples to not start from zero ? Does anybody know a third party visual tool that can import/export data from text files to a SQL Server DB via ODBC ?
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
How/when should transaction logs be truncated without breaking Log Shipping?. I'm trying to deal with the scenario of my tlogs increasing their size as I configure log shipping to 5 min. -frequency backups.
Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
We have an environment where 3 SQL 2000 servers exist; one production servers and two standby servers that receive logs via log shipping. Everything works fine and it is stable. My client wants to get a new 64 bit server and run SQL 2005 64 bit version for the production box. Can we still do log shipping with the two remaining SQL 2000 boxes or do they all have to be on the same version and bit version?
Hi im trying to install Sql Server 2005 but it says i need to Increment the Counter Registry Key for Setup in SQL Server 2005 before doing so. Im following the guide at http://msdn2.microsoft.com/en-us/library/ms143215.aspx but in my registry the folder HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersionPerflibdoesnt contain any keys"Last Counter"=dword:00000ed4 (5276)"LastHelp"=dword:00000ed5 (5277) or any key for that matter.How can i fix this?
I am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
