Hi Availability over WAN advice

Hi Team,
Not sure of the best solution. I have two SQL servers in different parts of
the country connected via a 3mb VPN WAN. Both are used for the same front end
application but use different databases.
I want each server to be able to take over the others database in the event
of a disaster at one site. The two sites are on different domains but I plan
to merge the two into one and have a standby computer in each location with
the site specific apps.
I have two domain controllers and two TS servers at my disposal for
reconfiguration.
Any ideas on the best way to go?
Thanks
Andrew
I would use bi-directional transactional replication or merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:C626429E-6082-454D-8BC0-970A1B51179C@.microsoft.com...
> Hi Team,
> Not sure of the best solution. I have two SQL servers in different parts
of
> the country connected via a 3mb VPN WAN. Both are used for the same front
end
> application but use different databases.
> I want each server to be able to take over the others database in the
event
> of a disaster at one site. The two sites are on different domains but I
plan
> to merge the two into one and have a standby computer in each location
with
> the site specific apps.
> I have two domain controllers and two TS servers at my disposal for
> reconfiguration.
> Any ideas on the best way to go?
> Thanks
> Andrew
>
sql

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

Hep! Printing Error from client (browser)

We have 2 servers with RS:

1.SQL RS 2000 SP3a

2.SQL RS 2005 SP1

When we try to use client side printing in SQL 2000 RS we getting error "An error occured during printing '0x8007F304' ".

When we try to use client side printing (from the same client PC) in SQL 2005 RS, it print fine without any problem

Thank you,

Lenn

Please check this posts

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=247869&SiteID=1

|||

That post refering to installing SQL2005 SP1 to fix printing problem. We have SQL2005 SP1 installed on 2005 server, but reports are not prints when RS running to SQL 2000 server!!

Thanks,

Lenn

Help:DTC-Firewall-SQL server clustering

Hello All
I am having trouble of DTC to get working thru firewall
The setup we have is
Two web servers load balanced. Componets run off the web servers. (OS:2000 Server
Two SQL server clusered.(OS: 2000 advanced server)
Web servers and SQL Servers are in different domain separated by firewall
Components do not work if I set "transaction required". If I changed it to "Transaction suported" or "not supported" some componets do work
I tried suggestions from kb article
DTCPing: http://support.microsoft.com/?id=30684
Initially DTCping had some problem but for testing purpose we opened all ports on firewall. It worked
I tried another config suggestion
http://support.microsoft.com/?id=25036
When I apply the registry entries for ports mentioned in this article, it disabled Clustering service. I was not able to get it back until I revert back registry settings
I tried DTCtester utility, it doesn't work
Does any one know why it must be disabling the clustering service? or Have any other suggestions on configuration for DTC thru Firewall
Thank
MilsThanks Jeff
I tried the procedure you mentioned. but it did not help
I have already tried the DTCping utility. At first it did not work but then I opened all the ports on firewall. now DTCping works.
DTCtester.exe utility still does not run, it gives error
Output of utility
****************
E:\netins>dtctester vssql01 uname Pw
Executed: dtcteste
DSN: vssql0
User Name: unam
Password: pw
tablename= #dtc1236
Creating Temp Table for Testing: #dtc1236
Warning: No Columns in Result Set From Executing: 'create table #dtc12365 (iva
int)
Initializing DT
Beginning DTC Transactio
Enlisting Connection in Transactio
Error
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver
Distributed transaction error
Error
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cu
sor stat
Typical Errors in DTC Output Whe
a. Firewall Has Ports Close
-OR
b. Bad WINS/DNS entrie
-OR
c. Misconfigured networ
-OR
d. Misconfigured SQL Server machine that has multiple netcards
Aborting DTC Transactio
Releasing DTC Interface Pointer
Successfully Released pTransaction Pointer.
*********************************
One of the error description refers to multiple netcards. Our SQL server has multiple netcards.
Any idea if there's any specific configuration for SQL Server using multiple netcards
Thanks
Milin|||1. Are the ports still open. If not then you need to open ports 445, 135,137-9, plus the ones you set in DCOMCNFG
2. Do you have an entry in each server of the cluster's host file with an entry to the destination server. and likewise on the destination for each of the Clustered servers
3. Does DTCPing work both ways now also
Jef
MCDBA, MCSE+I|||Yes. All ports are still open in both directions
I have entries in hosts files of both servers. I can access these servers using network names from each other
DTCping work in both directions
Thank
mil|||Did you actually set the ports with DCOMCNFG and reboot or did you just try the reg hack
If not then set DCOMCNFG on all servers to use the proper ports and reboot all servers
Other than that I cannot think of anything else
Good Luc
Jeff|||Mil
I am sorry then. In all my experience once I have had DTCping working I have had DTC working as well
Jeff|||"mils" wrote:
> When I apply the registry entries for ports mentioned in this article, it disabled Clustering service. I was not able to get it back until I revert back registry settings.
Hi,
I have the same problem.
when i set the port by dcomcfg (or by registry), the nodes cannot start clustering services.
WhY '
How can i do ?
thanks in advance !!
Regards, Dario

Help: Replication stops with no error or warnings!

hi all,
I have sql server 2005 sp2 running transactional replication between 2
production servers on different domains. Every once in a while
transaction stop being delivered to the target with no warnings or
errors being generated by any of the servers involved. Using
replication monitor on the the publisher and viewing the tab "
Publisher to Distributor" shows transactions being delivered
successfully ("n transaction(s) with n command(s) were delivered
successfully") but the transactions do not show up on the target
server?
Any thoughts much appreciated as the only resolution i've found is to
wait until off hours and reinitialize the subscriptions. i'm going to
have to put a custom alert system in place to check table counts but i
really need to find the cause of this.
-Thanks! DF
Difficult to tell exactly what is getting replicated in repl monitor. Best
tool to troubleshoot is to run profiler on subscriber and see if you can find
anything. Best to do after hours if you can repeat problem w/ just you as
user.
What is your distribution agent profile? How did you initialize subscriber -
snapshot agent or backup/restore? If backup init - make sure you disable
triggers or set to NFR and drop FK's at subscriber. Perhaps your agent is
ignoring errors caused by FK constraints and not delivering rows changes...
ChrisB MCDBA
MSSQLConsulting.com
"dfateman@.yahoo.com" wrote:

> hi all,
> I have sql server 2005 sp2 running transactional replication between 2
> production servers on different domains. Every once in a while
> transaction stop being delivered to the target with no warnings or
> errors being generated by any of the servers involved. Using
> replication monitor on the the publisher and viewing the tab "
> Publisher to Distributor" shows transactions being delivered
> successfully ("n transaction(s) with n command(s) were delivered
> successfully") but the transactions do not show up on the target
> server?
> Any thoughts much appreciated as the only resolution i've found is to
> wait until off hours and reinitialize the subscriptions. i'm going to
> have to put a custom alert system in place to check table counts but i
> really need to find the cause of this.
> -Thanks! DF
>

HELP: Linked SQL servers issue - cannot call ServerName.Database.dbo.TableName

We recently upgraded our production SQL Server to SQL Server 64-bit
Yukon on Windows 2K3 however our development SQL server is still SQL
Server v7.
We have linked servers.
The problem is all the stored procedures fail as well as T-SQL
commands in SQL Query Analyzer because we may no longer call the
server with the 4-part naming convention (server.database.dbo.table)
while OPENQUERY still works such as:
FAILURE:
SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM SERVER2.DATABASE2.dbo.TABLE2
WHERE LRSN=34534534
OPENQUERY WORKS:
SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM
OPENQUERY(SERVER2, 'SELECT * FROM DATABASE2.dbo.TABLE2 WHERE
LRSN=34534534')
The failure message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not
give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IUnknown::QueryInterface returned 0x80004005: The provider did not
give any information about the error.].
Any help is appreciated!On your new server make sure the remote account is the
same and the password is he same. Also check your remote
query time out length
>--Original Message--
>
>We recently upgraded our production SQL Server to SQL
Server 64-bit
>Yukon on Windows 2K3 however our development SQL server
is still SQL
>Server v7.
>We have linked servers.
>The problem is all the stored procedures fail as well as
T-SQL
>commands in SQL Query Analyzer because we may no longer
call the
>server with the 4-part naming convention
(server.database.dbo.table)
>while OPENQUERY still works such as:
>
>FAILURE:
>SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
>FROM SERVER2.DATABASE2.dbo.TABLE2
>WHERE LRSN=34534534
>
>OPENQUERY WORKS:
>SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
>FROM
>OPENQUERY(SERVER2, 'SELECT * FROM DATABASE2.dbo.TABLE2
WHERE
>LRSN=34534534')
>
>The failure message is:
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'SQLOLEDB' reported an error. The
provider did not
>give any information about the error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>IUnknown::QueryInterface returned 0x80004005: The
provider did not
>give any information about the error.].
>
>Any help is appreciated!
>.
>