Hi,
I have created 1 sqls server as a remote server in another sql server, so I can execute backup jobs on the backup server from the production server.
This was all working ok, but now everytime the backup is called I get an error message saying login falied for 'sa'.
I haven't changed anything and the remote logins look like they are set up correctly.
Any advise would be appreciatedMy guess would be that you applied sp3, and when it "hardened" the sa password on your slave server, things went amok. Open Query Analyzer on your master, and attempt to log into your slave as sa. If that breaks, you'll need to find (or change) the sa password for your slave, then fix the problem(s) (there may be more than one) in the remote login definitions.
-PatP|||Hi,
Did not apply sp3? they were both applied to both the servers before this error occured.
this is the kind thing that has stopped working
Run on server 1 as a job
exec Server2.msdb.dbo.sp_start_job @.job_name='Restore = Load Alpha AM'
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Remote queries using sp_executesql run inconsistently
This one has stumped me!
I recently implemented a process to monitor database usage and growth
on our production servers. I use on server as the "master" that
collects data from all the other servers into one database. The
problem I'm having is that only every other day the process completes
after having successfully collected data from all servers. On the
alternate days, only data from the "master" server is collected. I've
used some debugging code to determine that my process is successfully
communicating with each server each day, but I can reliably plan on the
every-other-day behavior.
I apologize if this explanantion is too vague. Here is some code...
This is the SP on the "master" server that contacts all the other
servers and collects the data (this is within a cursor that loops
through the list of server names as stored in a local table):
-- Creates the generic linked server
select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
exec sp_serveroption 'MSSQL', 'rpc', 'true'
exec sp_serveroption 'MSSQL', 'rpc out', 'true'
set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
@.svr_nm + ''' where ServerName = ''new'''
execute sp_executesql @.sqlstr
set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
execute MSSQL.master.dbo.sp_executesql @.sqlstr
-- clean-up
exec sp_droplinkedsrvlogin 'MSSQL',null
exec sp_dropserver 'MSSQL'Still reviewing your code, but just wondering why are you adding and
dropping links to the remote servers each time you run the job? Why not
just permanently link the servers?
Will get back to you on the rest when I can review your code in more detail.
Thx
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
> This one has stumped me!
>
> I recently implemented a process to monitor database usage and growth
> on our production servers. I use on server as the "master" that
> collects data from all the other servers into one database. The
> problem I'm having is that only every other day the process completes
> after having successfully collected data from all servers. On the
> alternate days, only data from the "master" server is collected. I've
> used some debugging code to determine that my process is successfully
> communicating with each server each day, but I can reliably plan on the
> every-other-day behavior.
>
> I apologize if this explanantion is too vague. Here is some code...
>
> This is the SP on the "master" server that contacts all the other
> servers and collects the data (this is within a cursor that loops
> through the list of server names as stored in a local table):
>
> -- Creates the generic linked server
> select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
>
> exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
> exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
> exec sp_serveroption 'MSSQL', 'rpc', 'true'
> exec sp_serveroption 'MSSQL', 'rpc out', 'true'
>
> set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
> MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
> @.svr_nm + ''' where ServerName = ''new'''
>
> execute sp_executesql @.sqlstr
>
> set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
> execute MSSQL.master.dbo.sp_executesql @.sqlstr
>
> -- clean-up
> exec sp_droplinkedsrvlogin 'MSSQL',null
> exec sp_dropserver 'MSSQL'
>|||We would prefer not to leave permanent linked servers out our servers if not
for a specific database or purpose; we've found that developers can sometime
s
abuse them. Also, the dynamic nature of the script allows us to add and
remove servers from the process more easily.
Thanks for reviewing my code. I look forward to your feedback!
"Michael C#" wrote:
> Still reviewing your code, but just wondering why are you adding and
> dropping links to the remote servers each time you run the job? Why not
> just permanently link the servers?
> Will get back to you on the rest when I can review your code in more detai
l.
> Thx
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
>
>|||Nothing's jumping out at me, other than you're not fully-qualifying all of
the tables (i.e., temp_drives). Are you seeing anything in your Event Logs
on either the local computer or remote linked servers? My best guess would
be a security/login failure on the remote machine, but you'd have to check
the logs for that. Could be that the commands are timing out, for instance
if you're running intensive operations every other day like backups and
index rebuilds, etc. Look for any other activities that are occurring on
your server on days of failure. It might end up just being a case of
scheduling the job to run earlier or later in the day.
There might be additional info in the SQL Server Logs (under "Management" in
EM).
Let me know if you see anything in your logs.
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
> We would prefer not to leave permanent linked servers out our servers if
> not
> for a specific database or purpose; we've found that developers can
> sometimes
> abuse them. Also, the dynamic nature of the script allows us to add and
> remove servers from the process more easily.
> Thanks for reviewing my code. I look forward to your feedback!
>
> "Michael C#" wrote:
>|||No, nothing in the event logs. The security is set up correctly. It could
be a timeout issue, but I would assume that I'd receive an error message in
that case.
Also, there are no other long-running jobs during this time, and no
processes that run only every other day.
I'm going to be adding a bit more code to the process this w
, so
hopefully my additional testing will help to reveal the answer.
Thanks for your help
"Michael C#" wrote:
> Nothing's jumping out at me, other than you're not fully-qualifying all of
> the tables (i.e., temp_drives). Are you seeing anything in your Event Log
s
> on either the local computer or remote linked servers? My best guess woul
d
> be a security/login failure on the remote machine, but you'd have to check
> the logs for that. Could be that the commands are timing out, for instanc
e
> if you're running intensive operations every other day like backups and
> index rebuilds, etc. Look for any other activities that are occurring on
> your server on days of failure. It might end up just being a case of
> scheduling the job to run earlier or later in the day.
> There might be additional info in the SQL Server Logs (under "Management"
in
> EM).
> Let me know if you see anything in your logs.
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
>
>|||Did you find a resolution on this?
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
> No, nothing in the event logs. The security is set up correctly. It
> could
> be a timeout issue, but I would assume that I'd receive an error message
> in
> that case.
> Also, there are no other long-running jobs during this time, and no
> processes that run only every other day.
> I'm going to be adding a bit more code to the process this w, so
> hopefully my additional testing will help to reveal the answer.
> Thanks for your help
> "Michael C#" wrote:
>|||No, not yet. This is only something I can work on when I have all my other
"regular" work done. :-(
"Michael C#" wrote:
> Did you find a resolution on this?
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
>
>
I recently implemented a process to monitor database usage and growth
on our production servers. I use on server as the "master" that
collects data from all the other servers into one database. The
problem I'm having is that only every other day the process completes
after having successfully collected data from all servers. On the
alternate days, only data from the "master" server is collected. I've
used some debugging code to determine that my process is successfully
communicating with each server each day, but I can reliably plan on the
every-other-day behavior.
I apologize if this explanantion is too vague. Here is some code...
This is the SP on the "master" server that contacts all the other
servers and collects the data (this is within a cursor that loops
through the list of server names as stored in a local table):
-- Creates the generic linked server
select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
exec sp_serveroption 'MSSQL', 'rpc', 'true'
exec sp_serveroption 'MSSQL', 'rpc out', 'true'
set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
@.svr_nm + ''' where ServerName = ''new'''
execute sp_executesql @.sqlstr
set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
execute MSSQL.master.dbo.sp_executesql @.sqlstr
-- clean-up
exec sp_droplinkedsrvlogin 'MSSQL',null
exec sp_dropserver 'MSSQL'Still reviewing your code, but just wondering why are you adding and
dropping links to the remote servers each time you run the job? Why not
just permanently link the servers?
Will get back to you on the rest when I can review your code in more detail.
Thx
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
> This one has stumped me!
>
> I recently implemented a process to monitor database usage and growth
> on our production servers. I use on server as the "master" that
> collects data from all the other servers into one database. The
> problem I'm having is that only every other day the process completes
> after having successfully collected data from all servers. On the
> alternate days, only data from the "master" server is collected. I've
> used some debugging code to determine that my process is successfully
> communicating with each server each day, but I can reliably plan on the
> every-other-day behavior.
>
> I apologize if this explanantion is too vague. Here is some code...
>
> This is the SP on the "master" server that contacts all the other
> servers and collects the data (this is within a cursor that loops
> through the list of server names as stored in a local table):
>
> -- Creates the generic linked server
> select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
>
> exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
> exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
> exec sp_serveroption 'MSSQL', 'rpc', 'true'
> exec sp_serveroption 'MSSQL', 'rpc out', 'true'
>
> set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
> MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
> @.svr_nm + ''' where ServerName = ''new'''
>
> execute sp_executesql @.sqlstr
>
> set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
> execute MSSQL.master.dbo.sp_executesql @.sqlstr
>
> -- clean-up
> exec sp_droplinkedsrvlogin 'MSSQL',null
> exec sp_dropserver 'MSSQL'
>|||We would prefer not to leave permanent linked servers out our servers if not
for a specific database or purpose; we've found that developers can sometime
s
abuse them. Also, the dynamic nature of the script allows us to add and
remove servers from the process more easily.
Thanks for reviewing my code. I look forward to your feedback!
"Michael C#" wrote:
> Still reviewing your code, but just wondering why are you adding and
> dropping links to the remote servers each time you run the job? Why not
> just permanently link the servers?
> Will get back to you on the rest when I can review your code in more detai
l.
> Thx
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
>
>|||Nothing's jumping out at me, other than you're not fully-qualifying all of
the tables (i.e., temp_drives). Are you seeing anything in your Event Logs
on either the local computer or remote linked servers? My best guess would
be a security/login failure on the remote machine, but you'd have to check
the logs for that. Could be that the commands are timing out, for instance
if you're running intensive operations every other day like backups and
index rebuilds, etc. Look for any other activities that are occurring on
your server on days of failure. It might end up just being a case of
scheduling the job to run earlier or later in the day.
There might be additional info in the SQL Server Logs (under "Management" in
EM).
Let me know if you see anything in your logs.
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
> We would prefer not to leave permanent linked servers out our servers if
> not
> for a specific database or purpose; we've found that developers can
> sometimes
> abuse them. Also, the dynamic nature of the script allows us to add and
> remove servers from the process more easily.
> Thanks for reviewing my code. I look forward to your feedback!
>
> "Michael C#" wrote:
>|||No, nothing in the event logs. The security is set up correctly. It could
be a timeout issue, but I would assume that I'd receive an error message in
that case.
Also, there are no other long-running jobs during this time, and no
processes that run only every other day.
I'm going to be adding a bit more code to the process this w
, sohopefully my additional testing will help to reveal the answer.
Thanks for your help
"Michael C#" wrote:
> Nothing's jumping out at me, other than you're not fully-qualifying all of
> the tables (i.e., temp_drives). Are you seeing anything in your Event Log
s
> on either the local computer or remote linked servers? My best guess woul
d
> be a security/login failure on the remote machine, but you'd have to check
> the logs for that. Could be that the commands are timing out, for instanc
e
> if you're running intensive operations every other day like backups and
> index rebuilds, etc. Look for any other activities that are occurring on
> your server on days of failure. It might end up just being a case of
> scheduling the job to run earlier or later in the day.
> There might be additional info in the SQL Server Logs (under "Management"
in
> EM).
> Let me know if you see anything in your logs.
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
>
>|||Did you find a resolution on this?
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
> No, nothing in the event logs. The security is set up correctly. It
> could
> be a timeout issue, but I would assume that I'd receive an error message
> in
> that case.
> Also, there are no other long-running jobs during this time, and no
> processes that run only every other day.
> I'm going to be adding a bit more code to the process this w
, so> hopefully my additional testing will help to reveal the answer.
> Thanks for your help
> "Michael C#" wrote:
>|||No, not yet. This is only something I can work on when I have all my other
"regular" work done. :-(
"Michael C#" wrote:
> Did you find a resolution on this?
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
>
>
Remote Production Database Issue
Hello Everyone,
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
Scott
Hi Scott
You may want to check for any data protection issues before you try anything.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote production
> server. The only connection I have is a read only Linked Server Connection
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under my
> current limitations?
> Thanks
> Scott
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
Scott
Hi Scott
You may want to check for any data protection issues before you try anything.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote production
> server. The only connection I have is a read only Linked Server Connection
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under my
> current limitations?
> Thanks
> Scott
Remote Production Database Issue
Hello Everyone,
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
ScottHi Scott
You may want to check for any data protection issues before you try anything.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote production
> server. The only connection I have is a read only Linked Server Connection
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under my
> current limitations?
> Thanks
> Scott
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
ScottHi Scott
You may want to check for any data protection issues before you try anything.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote production
> server. The only connection I have is a read only Linked Server Connection
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under my
> current limitations?
> Thanks
> Scott
Remote Production Database Issue
Hello Everyone,
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
ScottHi Scott
You may want to check for any data protection issues before you try anything
.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on
a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote productio
n
> server. The only connection I have is a read only Linked Server Connectio
n
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under m
y
> current limitations?
> Thanks
> Scott
Can someone please give me some ideas on how I can maintain a database on a
development environment with data that was entered by users from a remote
production site.
I have no direct access and can not remote connect to the remote production
server. The only connection I have is a read only Linked Server Connection
over a VPN. The database is about 4 Gigs in size.
Does anyone here have any suggestions on the best approarch to recreating
the production database at our development site on a nightly basis under my
current limitations?
Thanks
ScottHi Scott
You may want to check for any data protection issues before you try anything
.
Have you tried connecting directly to the instance using the credentials for
the linked server? If you aren't wanting to do a full backup/restore you may
want to set up replication or log shipping to a separate databases and then
transfer the data from that to the other system.
John
"Scott Yu" wrote:
> Hello Everyone,
> Can someone please give me some ideas on how I can maintain a database on
a
> development environment with data that was entered by users from a remote
> production site.
> I have no direct access and can not remote connect to the remote productio
n
> server. The only connection I have is a read only Linked Server Connectio
n
> over a VPN. The database is about 4 Gigs in size.
> Does anyone here have any suggestions on the best approarch to recreating
> the production database at our development site on a nightly basis under m
y
> current limitations?
> Thanks
> Scott
Wednesday, March 7, 2012
Remote Distributor and Subscriber on same machine - is that OK?
Hi,
Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).
Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.
Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?
Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,
Smitha
This is actually a rather popular configuration (at least the ones that I have seen) inside Microsoft as this allows you to upgrade the Distributor and Subscriber to a new release together and then use the Subscriber as a test bed for the new release while receiving continuous updates from the production system. You do need good network connectivity between the publisher and the distributor\subscriber as pulling snapshot data (or backup\restore for that matter) can easily saturate a 100Mbit Ethernet connection. So to minimize impact on the production system, you may want to get a dedicated connection between the two.
-Raymond
|||Thanks Raymond. I shouldn't have to worry about the snapshot/backup+restore because I've used this second server as the backup files' location in the past (and still perform copy-only FULL production DB backups to this server). The restore takes a while since it reads from and writes to the same drive but this is OK for an operation that may happen 2-3 times a year.
Thanks again,
Smitha
Subscribe to:
Posts (Atom)