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
ADVERTISEMENT
Nov 3, 2015
I'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA.
View 7 Replies
View Related
Jun 11, 2015
I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.
I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.
View 5 Replies
View Related
Jun 20, 2015
I should audit all access by dba (italian privacy law) in my sql server , what is the best way?
Using the build in audit i can't achieve this goal, do i miss something?
My sqlserver use windows auth.
View 9 Replies
View Related
Jun 16, 2015
I need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
View 9 Replies
View Related
Jun 19, 2015
I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
View 0 Replies
View Related
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
Jun 17, 2015
I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it?
View 4 Replies
View Related
Jul 16, 2015
How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
View 2 Replies
View Related
Jun 27, 2014
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
May 8, 2015
Is there any way to find Report server database name using T-SQL?
I have SQL server report server instance and authentication credentials.
I can use sys.databases but in case of user had changed ReportDb name at the time of configuration this approach will not work then How I can find?
View 2 Replies
View Related
Jun 23, 2015
I am playing around in a test environment with SQL Server 2014. I have a question about the default location of the report server databases when you have multiple report server instances installed on one server.
I did a very simple install of SQL Server 2014 with the database and Reporting Services in Native Mode (install only) features selected. Accepting the default locations, I ended up with the following locations as you would expect:
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVER
C:Program FilesMicrosoft SQL ServerMSRS12.MSSQLSERVER
Running the Reporting Services Configuration Manager, I created the Report Server database. After creating the Report Server database, the related files will be located below in the SQL folder as I would expect.
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA
Next I installed another instance, which I called Test, of SQL Server 2014 like I did above. I now have the following folder structure the Test instance as I expect.
C:Program FilesMicrosoft SQL ServerMSSQL12.TEST
C:Program FilesMicrosoft SQL ServerMSRS12.TEST
Running the Reporting Services Configuration Manager, I select the Test instance and create the Report Server Test instance database.
My first thought would be that the Test instance Report Server database would be in the following location:
C:Program FilesMicrosoft SQL ServerMSSQL12.TESTMSSQLDATA
Instead this database is located with the default instance Report Server database:
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA
View 2 Replies
View Related
Jul 16, 2015
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.
I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?
View 8 Replies
View Related
Mar 31, 2015
I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.
However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?
View 1 Replies
View Related
