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?
Thanks,
~ Steve
View 5 Replies
ADVERTISEMENT
Feb 16, 2008
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
View 1 Replies
View Related
Apr 21, 2004
Hello -
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.
Please help me out.
Thanks.
View 2 Replies
View Related
Jan 28, 2015
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?
View 3 Replies
View Related
Dec 20, 2006
Hello,
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?
Mike
View 9 Replies
View Related
Jul 2, 2006
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
View 2 Replies
View Related
Nov 29, 2005
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).
View 4 Replies
View Related
Aug 29, 2007
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?
Thanks.
View 3 Replies
View Related
Mar 24, 2008
SQL Server 2005 sp2 Standard Edition.
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???
View 2 Replies
View Related
Apr 5, 2007
Hi,
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 look forward to some responses.
Kind regards,
Neeva
View 2 Replies
View Related
Mar 17, 2004
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.
Thanks
View 2 Replies
View Related
Jan 9, 2008
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??????
View 5 Replies
View Related
Aug 12, 2014
I restarted the sql server after c2 audit was enabled and now i can not start the instance getting this error below. how do i bring the sql server up?
Cannot start C2 audit trace. SQL Server is shutting down. Error = 0x80070003(The system cannot find the path specified.)
View 2 Replies
View Related
Sep 15, 2015
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) ?
View 0 Replies
View Related
Aug 21, 2007
HIiiiiiii all
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...
Thanking you in advance
View 7 Replies
View Related
Mar 30, 2015
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).
View 2 Replies
View Related
Jul 30, 2015
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?
View 5 Replies
View Related
Jun 22, 2007
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.
Any help will be greatly appreciated.
Tim Gordon-Jones
View 4 Replies
View Related
Oct 16, 2015
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.
View 3 Replies
View Related
Apr 23, 2008
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.
View 1 Replies
View Related
Oct 17, 2007
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?
View 4 Replies
View Related
Sep 10, 2007
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?
View 3 Replies
View Related
Jun 8, 2007
Dim strCON As String = _
"workstation id=DJI-MAIN;packet size=4096;user id=sa;integrated security=SSPI;data source=DJI-MAIN;persist security info=False;initial catalog=RacingSystem"
Dim cnn1 As New SqlConnection(strCON)
cnn1.Open()
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.
TIA
View 4 Replies
View Related
Dec 1, 2015
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.
View 5 Replies
View Related
Dec 27, 2005
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.
[0]: http://staff.develop.com/nielsb/code/ssbadmin.zip
View 2 Replies
View Related
Jun 19, 2005
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.
View 1 Replies
View Related
Jul 17, 2005
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
View 6 Replies
View Related
Jul 23, 2007
Does SQL SVR 2005 Service Broker use connection pooling?
We are building a laptop application that will use SB to send messsages
to one SQL SVR 2005 instance. SQL 2005 Express will be installed on
each laptop and will be the "sender" to one SQL SVR instance, the "receipient"
It will be deployed to over 1500+ laptops. Each laptop will have a different
IP address.
Our SQLDBA wants to know if each of the laptops will use a different
connection when sending messages?
I answered "Yes", but another team member said "No".
What is the correct answer?
View 7 Replies
View Related
Oct 23, 2006
helloi use Visual Web Developer and SQL server 2005 express , how can i enable service borker of my sql server ?thaks for any helpderin
View 1 Replies
View Related
Jun 29, 2006
Hi!
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?
Thanks
View 11 Replies
View Related
Jul 14, 2006
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;
DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;
-- 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
View 8 Replies
View Related
Sep 7, 2006
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?
Gary
View 2 Replies
View Related
Apr 26, 2006
Hi,
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.
Its giving syntax error
Thanks
View 1 Replies
View Related
