Moving DB One Server To Another With Broker Enabled
Jun 22, 2006
I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup? Where are the BrokerID's Stored?
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
I have 2 databases on a remote server on which Replication and Replication Monitor is enabled. Now I want to move this databases from different remove location.
How do I transfer bboth databases with replication and replication monitor enabled on it with all the user logins.
I have a DB with TDE enabled on Enterprise edition sql server 2008r2. I am actually planning on moving the DB to sql server 2008r2 standard edition. How to accomplish it properly. I don't think SQL Server 2008r2 standard edition support TDE. I can only think of turning off the encryption, but is there anything else I should know?
I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?
I'm having a problem with memory and cpu increasing after I enable the broker on my database. Before I enable the broker, my memory will be at 70 mb, but after I enable broker, memory increases to 100, 200, and keeps rising even until 700 mb; plus the sql server process uses more of the CPU, ranging between 30-40 percent.
Any ideas what could be going on, and possible solutions? Thanks
I backed up a database "Broker", and restored it on the same instance as "BrokerQA" (Broker db still exists, I need them both running on the same instance).
For developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?
I have a bunch of databases to relocated. relocating them on the same server - I just want to split the data and log files onto their own Volumes. these DBs have Full Text Indexing enabled. This Server is a brand new build and NOT in production yet
How is the best way to move these Dbs and how do locate and move the full text catalog for each?? or do I even have to worry about that?? Is back-restore (with move) better for this?
When I look in BOL.. here is the syntax for an attach
USE master; GO --Detach the AdventureWorks database sp_detach_db AdventureWorks; GO -- Physically move the full text catalog to the new location. --Attach the AdventureWorks database and specify the new location of the full-text catalog. CREATE DATABASE AdventureWorks ON (FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf'), (FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_log.ldf'), (FILENAME = 'c:myFTCatalogsAdvWksFtCat') FOR ATTACH; GO
Do I have to move the Full text Catalog? If so, how do I find it's current location. If I do have to move it, where is the best place to locate the catalogs???
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I am unable to access the default port 1433 on my SQL 2003 server. There is no firewall. I run telnet <ip> <port> and get "Connection failed" which explains my inability to connect to the server for weeks. I have being unable to connect to the server after I uninstalled sql 2000 and reinstalled it on my 2003 server. Some of the things I have checked is Network Configuration in EM. Named Pipes and TCP/IP is installed with port set to 1433. HKLMSoftwareMicrosoftMSSQLServerClientSuperSocketNetLibTcpDefaultPort port is set to 1433. Is there anything I need to configure on the server side to have the default sql server port enabled.
Hello, I have to find out whether my servers have hyperthreading enabled or not???? I am running Windows server 2003 Standard edition on many of my machines. I have to configure the SQL Server, server configuration values according to the Hyperthreading. I know about the CPUcount.exe utility but is there anything else apart from it??????
UAT environment : SQL Server 2008 R2 SP3 Enterprise Edition
SANDBOX environment : SQL Server 2008 R2 SP3 Standard Edition
I have a database backup (.bak) that was taken from UAT environment that has CDC enabled on some tables. I want to restore that database into my SANDBOX environment which does not support CDC (because of standard edition). The restore process fails due to this incompatibility. Is there any way to restore without CDC (I dont CDC enabled on my SANDBOX; just my data from the backup) ?
I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
In Sql Statement then it gives me error Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78 The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:
I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables
master.dbo.spt_values master.dbo.sysconfigures and this table does not contains field like ''clr enabled'' so tht we can change the state
so plz plz help me to enable clr in the sql server 2005. I need it so muchhh...
Sql server 2008R2 (SP2) Ent, PROD DB myDB was encrypted. During Release mistakenly (Vendor created script blames some settings in ...- actually does not matter) Decryption started (ALTER .. SET ENCRYPTION OFF) as we got from ErrorLog.
For some reason initial encryption scan was aborted and then mentioned command: ALTER ... OFF was issued again. What we have now (after 60 h of decryption- encryption took only 2.5 h)- is_encrypted = 0 in sys.databases, encryption_state = 5 (decryption in progress) in sys.dm_database_encryption_keys (percent_complete= 0). But it seems myDB is still encrypted- I made a backup of myDB and tried to read it (restore filelistonly) from other server (with no encryption)- failed- asked for key. Seems metadata was changed when initial scan during decryption started but then stuck and (if I am correct) decryption was never completed. Question- any similar experience? How we can fix meta- data, i.e. assuming that myDB is still encrypted we should have is_encrypted = 1 and encryption_state = 3 (encrypted).
My index reorganise maintenance plan fails partly due to the disabled indexes
Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...
" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?
Using SQL Server 2005 SP2 std edition on Windows 2003 R2 SP2 x64 std edition I'm allowed to break the password policy.
To reproduce this behaviour as a system administrator run...
create login testuser with password='p'
This creates a login with the default of POLICY enabled ('Enforce Password Policy' check box is checked). OK, so may be system administrator is allowed to break the policy but now login as testuser and run
alter login testuser with password='t' old_password='p'
This succeeds despite obviously breaking the policy.
Surely this can't be right, I must be doing something wrong.
I am new to DBA activities. I have a database name Sample_DB in SQL Server 2014. Â
This database has below files
LogicalName FileType FileGroup Sample_DB_Rows  ROWS Data PRIMARY Sample_DB_C1 ROWS Data Country1 Sample_DB_C2 ROWS Data Country2 Sample_DB_Docs FILESTREAM Data FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem: I need to take backup of each file group separately. I need to restore the backed up file group separately. Is this possible, how to do it.
When I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?
My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.
Hi, I am trying to replicate some tables from an Sql Server database to an Oracle database. So the publisher is SQL Server and the Subscriber is Oracle.
Unfortunately I realised that I can't set the replicate_ddl parameter to 1 for an Oracle Subscriber. This is the error I got: "The property "replicate_ddl" cannot be modified for publications that are enabled for non-SQL Server subscriptions."
I looked on the Internet and I found that this parameter "@replicate_ddl" (used in the add_publication stored procedure) can't be set to 1 if the @enabled_for_het_sub parameter is set to 'true'.
So I thought if I just set the replicate_ddl parameter to 1 and leave the other parameter to false and then use the sp_changepublisher stored procedure to set the enabled_for_het_sub parameter to true that will work. But it didn't. I tried this in Enterprise Manager and although I had no error I realised that the replicate_ddl parameter was reset to false.
Is there any way I can replicate the ddl statements in Oracle automatically or I should to them manually?
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
This is the code I have been using to connect to my default instance for 18 months. A recent upgrade to Windows Live onecare now prevents me connecting to the server from the same machine. The following error message is generated:-
Unhandled Exception: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at TrainerReport001.Form1.Form1_Load(Object sender, EventArgs e) in F:#ProjectsStatsOwnerReport001Form1.vb:line 84
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)The program '[3336] TrainerReport001.exe' has exited with code 0 (0x0).
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at TrainerReport001.Form1.Main() in F:#ProjectsStatsOwnerReport001Form1.vb:line 2
If I turn off the onecare firewall I can connect as normal, but this is not a desired option. I have tried enabling port 1433 on the firewall, this does not work either. Any help greatly appreciated, onecare support seems reluctant to help.
During a newly set up on one of our SQL server 2012:
We had enable the trace flags 1117 and 1118 as a good practice using DBCC TRACEON(1117,-1) and similar for 1118.
We have been base lining the server and it came to notice that trace flags are no more enabled.
Property               Value                              CaptureDate DBCC_TRACESTATUS TF 1117: Status = 1, Global = 1, Session = 0    2015-10-20 00:00:00 DBCC_TRACESTATUS     TF 1118: Status = 1, Global = 1, Session = 0      2015-10-20 00:00:00
After reboot: Property                 Value                                  CaptureDate DBCC_TRACESTATUS      No trace flags enabled             2015-10-21 00:00:02.340
What can be the reason? What can be done to turn them on permanently, if its actually a good bet in enabling so.
Back in July I released the (then) latest version of SsbAdmin. For you who don'r know what it is; it's a tool which allows you to graphically administer SQL Server Service Broker.
I have now uploaded a new version which is compiled against the released versions of SQL Server 2005 and .NET 2.0.
You can download it from here: [0].
After download, un-zip to some directory and read the README.doc file.
Can't believe no one has commented on this in here yet. If you're into SQL Server Service Broker (and you are if you are reading this forum), you must go over and take Rushi's Service Broker Challange.
Recently there has been questions on some of the newsgroups about examples for SQL Server Service Broker. So, yesterday on a flight back to England I crofted up three different SQL Server projects as examples: LocalSampleRemoteSampleServer1RemoteSampleServer2 All three examples are jus your very basic €œHello World€? example, but they show: LocalSample €“ communication between two databases on the same SQL server instance. MasterKeys in a database and the database being TrustWorthyRemoteSampleServerX €“ These two samples are meant to run on two different machines/instances and in the samples you set up both transport security and dialog security by using Certificates. Download the zip file from here [0], unzip and read the README.txt file, and €“ Have Fun!! [0] : http://staff.develop.com/nielsb/code/servicebrokerexamples.zip
I have a legacy database in SQL 2000 which I want to submit transactions thru triggers to SQL Broker, until I eventually upgrade this db to 2005. Can you tell me if this is possible?
I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log: The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram AS SET NOCOUNT ON;
-- This procedure continues to process messages in the queue until the -- queue is empty.
WHILE (1 = 1) BEGIN BEGIN TRANSACTION; --BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message WAITFOR ( RECEIVE TOP(1) -- just handle one message at a time @MessageTypeName = message_type_name, @MessageBody = message_body, @Dialog = conversation_handle FROM mytableQueue ), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction -- and break out of the while loop, exiting the procedure. IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no, tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid, tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num, tbl.rows.value('@status', 'NVARCHAR(20)') AS status, tbl.rows.value('@creationtime', 'DATETIME') AS creationtime, tbl.rows.value('@note', 'NVARCHAR(250)') AS note, tbl.rows.value('@posted', 'NCHAR(1)') AS posted, tbl.rows.value('@kms', 'INT') AS kms, tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid, tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code, tbl.rows.value('@page_count', 'INT') AS page_count, tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number, tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback. IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; BREAK; END COMMIT TRANSACTION; END GO
I have an application that is reading a message from a Service Broker Queue. When I use integrated security with an NT account it works fine. When I use a SQL Server User through Management Studio I can select from the Queue however, when I use this same account through the web app with the SQL Server User, I cannot see the Queue. Is there a grant that I must do to this account to get it to see the Service Broker Objects?
I have installed sql server standard edition 2005.I want to enable service broker feature or services after intalling the sql server.Please let me known any tool or utiltiy to enable the feature
Alter datatabase set enable_broker is not working.
