SQL Server Admin 2014 :: How To List The Target Servers Associated With A Master Server

Sep 9, 2015

I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.

I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Create A Master Key In The Database

Jun 15, 2015

in my environment I am running the SQL Server agent job.i am getting below message.

create a master key in the database or open a master key in the session before performing this operation” error 

View 5 Replies View Related

SQL Server Admin 2014 :: Partitioning Master And 4 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and need to be partitioned for better manageability.

Little info: Every day, 300 Million records are inserted and 300 million records are deleted in below 7 tables. we maintain only 8 days worth of data in below tables which is the reason records which are older than 8 days are continuously deleted.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table.
dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

I would like to partition the above child tables based on the IDs that are inserted every 4 hours. Meaning, All IDs that are inserted in 4 hours window should be in a partition.

View 1 Replies View Related

SQL Server Admin 2014 :: Master Table With Insert Trigger

Feb 4, 2015

I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..

Is there anyway to capture the record either in the master table or in a separate table.

View 6 Replies View Related

SQL Server Admin 2014 :: Inconsistent Data In Database And Master Files?

Apr 21, 2015

USE <database>

select * from sys.database_files

and

select * from sys.master_files where database_id= <db id>

give me different size of memory optimized file in <database>

Microsoft SQL Server 2014 - 12.0.2456.0 (X64)

View 1 Replies View Related

SQL Server Admin 2014 :: Query Multiple Servers With A Scheduled Job Using CMS?

Mar 13, 2014

I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.

tell me if they have been successful just using standard jobs and querying against multiple servers?

If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.

I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.

View 1 Replies View Related

SQL Server Admin 2014 :: Deny SSMS Connections From Remote Servers?

Nov 20, 2014

Is there any way I can deny connection from SSMS only from remote servers?

View 7 Replies View Related

SQL Server Admin 2014 :: List Of Users That Accessed A Table

Jul 22, 2015

I'm trying to find out what tables are being used in a Database.

I don't want the last User but the User and the Dates.

I have a script that return the last user but that is not going to work.

The following script returns the last user but not all users and the Login Name:

ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())

[Code] .....

View 2 Replies View Related

SQL Server Admin 2014 :: List All Databases In Instance That Are Not Accessed Before Given Date

Jan 10, 2015

Looking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?

View 6 Replies View Related

SQL Server Admin 2014 :: Huge Traffic Between Share Point Front End Servers To Content Database?

Feb 20, 2014

We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.

Configuration is

Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB

ROUTING IS VIA FIRE WALL.

SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.

Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...

View 2 Replies View Related

Master / Target Server

Apr 22, 2008

Silly question, but why can you not setup a multi-server job and have that same server as a target? What's the rational behind this?

View 3 Replies View Related

Cannot Make Server As Target Server Or Link From Master Server

Jul 20, 2005

Sql Server 7 Standard EditionMy Server was renamed and the normal steps of reinstallingand running sp_dropserver and sp_addserver were followed.Everything seems to work fine but jobs from the master server.I did defect the server as a target server since it appeared as offlineanyway.On trying to re establish the status of the server as a target serverFailed to make "serverName" a target serverEnlist into Master Server "MasterServerName" Failed(Error 14262)Your insight is appreciatedVincento

View 3 Replies View Related

Changes To Master/Target Server Configurations

Feb 22, 2006

I've installed SQL Server 2000 and applied SP3a.  I see SP4 is available and fixes a lot of things.  The only bit I am unsure about is section '5.3.2 Changes to Master/Target Server Configurations' 
 http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm#_5464_sql_server_agent_enhancements_705
"Before you apply SP4, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP4 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3 or later. This is a change from the original SQL Server 2000 functionality. "
As I'm just starting out I don't even know what 'Master/Target Server' is.  I'm only installing to my PC which is XP Pro SP2. Can I ignore this step and apply the patch or do I need to do as it states?
Thanks 
 
 

View 1 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL Server Admin 2014 :: Change IP Address Of Server Which Is Locally Hosted And Is Not On Cluster?

Sep 3, 2014

I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.

View 3 Replies View Related

SQL Server Admin 2014 :: Unable To Access Physical Server To Backup Transaction Log

Dec 5, 2014

I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.

Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."

I don't understand how backing up the transaction log could break the "transaction chain"?

View 9 Replies View Related

SQL Server Admin 2014 :: No Objects Listed For Server Performance Condition Alert

Jan 25, 2015

We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".

View 3 Replies View Related

SQL Server Admin 2014 :: Failed To Prepare Storage For Testing On Node Server Name

Feb 23, 2015

I Run All checks for Validation cluster.I get Error On Disk Lists And Validation failed.With This error : Failed to prepare storage for testing on node "server name" The security account manager (SAM) or local security authority (LSA) server was in the wrong state to perform the security operation.

View 2 Replies View Related

SQL Server Admin 2014 :: Database Mail Fails At Email Server Alert

Apr 23, 2015

We recently had a problem with DB Mail. SQL jobs that sends an email succeeded but the email in the job fail to sent. There was a problem with the email server. The error is included. We fixed the problem with the email server. How can I get an alert when a DB Mail email fails send?

Date4/23/2015 10:01:06 AM
LogDatabase Mail (Database Mail Log)

Log ID5907
Process ID13204
Mail Item ID5702
Last Modified4/23/2015 10:01:06 AM
Last Modified Bysa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-04-23T10:01:06). Exception Message: Cannot send mails to mail server. (Insufficient system storage. The server response was: 4.3.1 Unable to accept message because the server is out of disk space.).
)

View 2 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server Admin 2014 :: Analysis Services Tabular AND Multidimensional On Same Server

Jul 24, 2014

Is it possible to have Analysis Services in both modes or are they mutually exclusive?I have a machine setup with Multidimensional AS and would like to know if it's possible to add a Service in Tabular mode.

View 2 Replies View Related

SQL Server Admin 2014 :: Determine Which Server Is Currently Active For Availability Groups

Nov 25, 2014

I want two write a small script to determine which is the currently active (primary) server in the AG.

Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?

View 9 Replies View Related

SQL Server Admin 2014 :: Multiple Database-collation Running On One Server?

Mar 30, 2015

We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.

Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS

Our new server (2014) has the standard collation Latin1_General_CI_AS

Both collations have CI and AS

From experience I know different databases can reside next to eachother on the same Instance.

The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....

The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).

using different databases using a different collation running on the same instance?

View 5 Replies View Related

SQL Server Admin 2014 :: Using ReportSync To Migrate Reporting Services To New Server

Apr 14, 2015

I need to migrate Reports from a SQL Server 2008 R2 Server to SQL Server 2014 Reporting Services.

Reporting Services is installed on one Server and the Reporting Services is installed on another Server.

On the existing Server the Encryption was never backed up.

What do I do with the key?

The following article provides some good information but it does not identify what to do with the Destination URL.

It mentions to download the files but it does not specify how to identify the path and it does not state where to upload them.

Do you restore the existing Reporting Services Databases to the new Server?

[URL] .....

View 2 Replies View Related

SQL Server Admin 2014 :: Capturing Connection Coming From Outside In Server Using Profiler

May 14, 2015

I need to do testing on capturing connection coming from outside using Profiler. Basically I need to do something from other SQL servers connecting to this SQL server and test if it is captured by profiler.

View 4 Replies View Related

SQL Server Admin 2014 :: How To Add Datafile To Mirrored Database In Primary Server

Aug 5, 2015

I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.

In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.

As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.

View 3 Replies View Related

SQL Server Admin 2014 :: Correcting Server Connection In Maintenance Plans?

Aug 11, 2015

In SQL 2008 R2, if we clone an environment including SQL server, the maintenance plans retain a connection string to the source/original server they got cloned from and are not editable. But, I was able to use a work around by editing them in BIDS and saving them back on the server. But now with 2014, I am facing two issues:

1.I still can edit the package to correct the server connection, with SSDT; but the option to save back to the server is not available any longer!

2.I used to be able to see all my plans under SSIS in 2008 R2 but not in 2014 now. Although, they are listed in SSMS!

View 1 Replies View Related

SQL Server Admin 2014 :: Unable To Add Server In Multi Subnet To Cluster

Oct 6, 2015

I am setting up SQL 2014 always on. I was able to set up the replicas between 2 servers in the same subnet.Their IP addresses are say like this:

100.20.200.200
100.20.200.201

When I am trying to introduce another node into the cluster which has IP address like 100.10.101.102, I am getting an error that the server isn't reachable.

View 5 Replies View Related

SQL Server Admin 2014 :: Central Management Server Doesn't Support Multi Domains

Jun 3, 2015

As CM doesn't support SQL authentication, and if access SQL Server on another domain with no trust, we cannot use Windows authentication as well.

CM only supports Windows authentication, but for large organizations which will have multi domains.

I am thinking if there is no proper way/tool, we will develop one then.

View 0 Replies View Related

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

Sep 8, 2015

I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:

create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end

I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.

View 8 Replies View Related

SQL Server Admin 2014 :: Restore Encrypted Server Backup Via GUI

Feb 10, 2015

I have an issue to restore an encrypted backup via GUI.I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014. The message error is: No backupset selected to be restored.I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. I have the following versions:

On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

View 9 Replies View Related

SQL Server Admin 2014 :: Audit All Server DDL Level Events?

Mar 6, 2015

I have been tasked with auditing all DDL and selected DML events on a production server and logging them to a table. My solution is to use CDC for the DML and a Server-Level trigger for the DDL. Because there should never but much DDL activity on the server (except when performing update tasks) I don't need to worry about the trigger consuming too many resources.

My question is this: Is there any single specification such as DDL_LEVEL_EVENTS that can capture all DDL activity or do I need to specify each and every DDL action in the trigger?

View 1 Replies View Related

SQL Server Admin 2014 :: Patching Server Configured In AlwaysOn

Mar 12, 2015

How to update the windows patch on sql server where the server is configured in always on ?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved