DB Engine :: Risk Of Enabling Change Tracking
Oct 6, 2015
We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an
ALTER
DATABASE [db_name] SET
CHANGE_TRACKING =
ON (CHANGE_RETENTION
= 2 DAYS,
AUTO_CLEANUP =
ON)
We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?
View 5 Replies
ADVERTISEMENT
Jul 30, 2015
In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.
View 3 Replies
View Related
Sep 4, 2015
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies
View Related
May 12, 2015
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies
View Related
Nov 5, 2015
I am using change tracking for tracking changes to an OLTP database for daily data warehouse load. I am tracking changes on 4 tables:
Site, Well, GasEngine, GasField,
I am using an SSIS package for our DW load and on completion, I store the results of the queries above into my own ChangeTracking table. I then use this as the last synced version for each table which I then use for the next DW load. I have the following change tracking settings:
CHANGE_TRACKING = ON
(
CHANGE_RETENTION = 10 DAYS,
AUTO_CLEANUP = ON
)
Now, the Site and Well tables may not have any changes for a few weeks or months but the GasEngine table will have changes every half hour and the GasField table will have changes every week. I have recently received a NULL value for Site changes query (below) and the SSIS package fails.
Is this due to there being no changes within the 10 days retention period?? What can I do in order to maintain the changes?? Could I maybe update the scripts above to check if the response from the query is NULL and if yes, set the current synced version to the last synced version from my ChangeTracking table?
Also, when the 10 days retention period is up, will the change tracking change version counter continue on from the last change version?
View 3 Replies
View Related
Jul 20, 2005
Has anyone come across a change tracking tool for SQL Server. Specificallythe scenario I want is the following :A Production DB needs some modifications to its content.This tool will copy the DB to a dev environment.The Dev environment will be 2 copies of the DB, 1 as a control set and theother the change DB.The developer makes their changes in the Change DB. They test them out andthen when they decide the changes are ready to pushto production they hitthe "go" button on the tool.The tool calculates the delta between the Control and the Change DB and thenpushes the changes to producitonAny ideasThankss
View 2 Replies
View Related
Jun 17, 2004
This one is giving me quite a bit more difficulty then I ever imagined it would...
Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.
I can't seem to find a good way to do this.
View 9 Replies
View Related
Dec 30, 2014
How to reset the value of the change tracking anchor id?
CHANGE_TRACKING_CURRENT_VERSION()
It's a bigint that increments on every DML operation and I just wondered how you could reset it back to zero. Turning change tracking off and on doesn't seem to do it.
View 2 Replies
View Related
Oct 7, 2005
Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this.
View 7 Replies
View Related
Jul 20, 2005
Does anyone know of software that tracks changes to a database? Forexample, it would track anytime an SP or view was recompiled, or ifyou added or deleted a column to a database?
View 1 Replies
View Related
Jun 8, 2015
best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
View 1 Replies
View Related
Jul 30, 2007
How can i enable my fulltex change-tracking and update-index in my table?
I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them.
Thanks in advance
View 3 Replies
View Related
Nov 18, 2015
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
View 7 Replies
View Related
Jul 21, 2015
SQL Server 2012R2 Express, I need to change db owner from Domain1User1 to Domain2User1.
Both SQL login Domain2User1 and db user Domain2User1 exist; db user Domain2User1 is mapped to SQL login
Domain2User1.
When I try to change db owner from Domain1User1 to Domain2User1
I get the error:
This article does not imply any restrictions on users that can be made db owners: [URL]....
"In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database."
Why can't I make the owner of a db an SQL login that have a mapped user in that db?
View 2 Replies
View Related
May 19, 2015
I have a problem upgrading a McAfee ePolicy Orchestrator which uses SQL Server 2008 R2 SP2. McAfee logs indicate that server names from SQL server and from McAfee installer does not match.It seems that the reason is that machine name was changed after SQL Server was installed.
MSDN instructions for sql server name changing give next commands to solve this problem:
SELECT @@SERVERNAME AS 'Server Name';
sp_dropserver <old_nameinstancename>;
GO
sp_addserver <new_nameinstancename>, local;
GO
But it didn't work for me, because "SELECT @@SERVERNAME AS 'Server Name'" shows correct (actual) name of sql server. So sp_dropserver says that there is now such instance.In the same time if I use this commands:
select serverproperty('MachineName')
select serverproperty('ServerName')
I get old (not actual) name.
epo-server is an actual computer name WIN-VUN5TSKAHG1 is an old name.
View 14 Replies
View Related
Sep 3, 2014
I am trying to change the default trace value from 2 to 6, to incorporate rollover and server shutdown. I am using this code to create a new trace (with id: 2) with the value of 6 and use that to overwrite the default trace (with id: 1):
DECLARE @new_trace_id INT;
EXECUTE master.dbo.sp_trace_create
@someinteger = @new_trace_id OUTPUT,
@someinteger = 6,
@someinteger = N'C: raceTestTrace';
Then I disable the default trace. I am then using this code to overwrite the default trace (with id: 1) and replace with new value default trace (with id: 2):
-- get trace status
SELECT * FROM ::fn_trace_getinfo(NULL)
-- stop trace
EXEC sp_trace_setstatus @traceid = 1
, @status = 0
go
-- delete trace
EXEC sp_trace_setstatus @traceid = 1
, @status = 2
Then I enable the default trace. It works perfectly (default trace with id 1 is showing value 6) until I restart. Upon restart no default traces are enabled, once I run the script to enable the default trace then the values for the default trace (with id: 1) are back, and my value for rollover/server restart is back to 2.
View 12 Replies
View Related
Nov 30, 2006
I have been trying to change the location of the SQL 2005 error log. It's logging to the default location at
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG
I want to move the log location to the E drive because it can get quite large. I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.
View 5 Replies
View Related
Jul 30, 2015
We are experiencing authentication/ Kerberos issues after a password for the MSA's has changed. We use MSA for our SQL 2012 and windows 2012 combination Servers. This errors creates issues while backing up and Service Broker connectivity.We had to restart sql services to fix it, but this not seem to be a resolution for me because the next time the password changes on these MSA's we may have to restart sql services.There seems to be a known issue for windows 2008 R2 servers and fix is available and we incorporated it. But the issue we are facing is on windows 2012 Server.
View 3 Replies
View Related
Sep 11, 2015
I found one database in availability group state change automatically. I found ERR started at
0000233c.00002848::2015/09/11-03:51:09.840 ERR [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
And I use google to search and found the link below. It indicated a network issue. Is it the source of the problem. By the way, My failure Condition Level is 1 and I have increased Health Check Timeout from 30,000ms to 150,000ms just now. [URL] ...
--- SQLDIAG ---
cluster log in UTC+8:
0000233c.00002848::2015/09/11-03:51:09.840 INFO [RES] SQL Server Availability Group: [hadrag] SQLMoreResults() returns -1 with following information
0000233c.00002848::2015/09/11-03:51:09.840 ERR [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
[Code] ...
View 3 Replies
View Related
Jun 5, 2015
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
View 4 Replies
View Related
Nov 9, 2015
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
View 3 Replies
View Related
Jun 29, 2006
We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.
We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.
View 1 Replies
View Related
Jun 10, 2015
Is it possible to change the default detection interval time to reduce to less than 5 seconds.
We have latency in trouble shooting the deadlocks and causing blockings more on our critical Production server.
View 10 Replies
View Related
Apr 8, 2008
Hi all,
I just read this article. The kind of select is called dirty read.
So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..
http://www.4guysfromrolla.com/webtech/101100-1.2.shtml
NOLOCK
Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.
View 9 Replies
View Related
Jul 20, 2005
I am sertainly no SQL expert so I am looking for a bit of help(actually quite desperate).The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduledto go live tomorrow with a MS SQL Server web app. Will potentiallyinsufficient hardware resources endanger the data of other appsrunning on the SQL Server or will the system simply be slow foreveryone?Basically, I need to know whether SQl Server bogs down gracefully(under 2x-3x an acceptable load) or will we be corrupting the databasewith a crash.Thanks a million,Bert
View 3 Replies
View Related
Mar 21, 2006
Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).
I have altered the sp_help_revlogin (from Microsoft article # 246133 )
PLEASE TEST/FIX before you use this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin_2005]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @logintype char(1)
DECLARE @logindisabled int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name <> 'sa' and type in ('S','U','G')
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin_2005 script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@logintype = 'G' OR @logintype = 'U')
BEGIN -- NT authenticated account/group
IF @logindisabled = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE IF (@logintype = 'S')
BEGIN -- SQL Server authentication
SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name
+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name
+ ''', @passwd = NULL, @sid = ' + @SID_string
END
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 4 Replies
View Related
Sep 24, 2015
I've read a bunch of articles saying you should always remove the guest user from the user databases and model. It seems to me that if a user only has public access then the user can't do anything on the database. If the guest user only has public access to a user database how is it a security threat? I must be missing something.
View 0 Replies
View Related
Oct 3, 2006
I was installing application software that was trying to install SQL Server 2005 and received the following error message.
"Setup failed because Service MSSQLServerADHelper is disabled for the current hardware profile. Services must be set with the current Hardware Profile logon property profile enabled."
Simple fix for most but I do not know how to do what it asks.
Thanks, Bruce
View 1 Replies
View Related
Jul 27, 2006
Hi All,
I'm new to SQL Server Express. But I need to find out how easy it is to create custom types with SQL Server Express because we have to represent house numbers as a custom type in the database. After enabling the CLR integration with the SQL Server Surface Area Configuration tool, I created an SQL Server Project with VS 2005 Pro., created a custom type called HouseNo, deployed it to a database and finally created a table with a column of that custom type. But when I tried to use the option 'Show Table Data' for that table, the following errors always appeared
SQL Execution Error
Executed SQL statement SELECT NP.ToString() AS NP From Table1
Error Source : .NET SqlClient Data Provider
Error Message : Execution of user code in the .NET Framework is disabled. Enable "CLR Enabled" configuration Option. -- I think I've already enabled it.
Does anyone know how to solve the problem? Just one more question, for a custom type implementing the IComparable interface, would SQL Server Express uses the CompareTo method to perform sorting?
Thanks!
Regards,
Nathan
----------------------------------------------------------------------------------------------
The SQL Server Epxress is newest and the following is code for the custom type.
---------------------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
public class HouseNo : INullable, IComparable, IBinarySerialize
{
// Private member
private String number;
private String letter;
private bool m_Null;
public HouseNo()
{
Number = "0";
Letter = "0";
}
public HouseNo(String number, String letter)
{
Number = number;
Letter = letter;
}
public string Number
{
get { return number; }
set { number = value; }
}
public string Letter
{
get { return letter; }
set { letter = value; }
}
public int CompareTo(Object obj)
{
if (obj is HouseNo)
{
HouseNo hno = (HouseNo) obj;
int numCompare = Number.CompareTo(hno.Number);
return (numCompare == 0 ? Letter.CompareTo(hno.Letter) : numCompare);
}
else throw new Exception("Obj is not a HouseNo");
}
public override string ToString()
{
// Replace the following code with your code
return Number+Letter;
}
public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}
public static HouseNo Null
{
get
{
HouseNo h = new HouseNo();
h.m_Null = true;
return h;
}
}
public static HouseNo Parse(SqlString s)
{
if (s.IsNull)
return Null;
HouseNo u = new HouseNo();
// Put your code here
return u;
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
Number = r.ReadString();
Letter = r.ReadString();
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(Number);
w.Write(Letter);
}
#endregion
}
View 4 Replies
View Related
Apr 7, 2004
I am using SQL Server 6.5. By default TCP/IP protocol is not enabled (1433 port is not opened by the server). How can I enable this so that I can use a JDBC driver to connect to it.
In SQL Server 2000, the "SQL Server Network Utility" helps us to enable the TCP/IP protocol. But not able to figure our how/where this can be done in 6.5.
Thanks for any help
View 1 Replies
View Related
May 30, 2006
I'm trying to enable the Service Broker for Sql Server 2005 because I want to be able to use a SqlDependency object.
I ran the following query to see if my local sql server service broker was enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname';
It came back with a value of 0 (which means it is not enabled).
I tried executing the following sql command to enable it:
ALTER DATABASE dbname SET ENABLE_BROKER;
The query has been running for over 5 mins and just keeps spinning (should it take this long to enable the Service brfoker), so I cancel it.
I even try to issue the command to see if the service broker is enabled after I cancel the query and it is not enabled.
How can I properly enable the Service Broker?
View 7 Replies
View Related
Oct 26, 2007
Hi all,
We have a remote server which runs SQL server 2000 DEV edition. We have 4 databases running in it. Our application needs the feature of Full Text Search in all the four databases. We have installed the necessary services. The problem is that we are able to create FTS on one database but when we try to run FTS on other databases the MSSearch service stops. on restarting it mssql stops. we are not able to do FTS on more than one database.
We had tried the same in our local server but it is working fine. The only difference between the two is that the local machine are member server. Has this to do anything with the FTS? Meanwhile the eventviewer doent help much, it just tells that the mssearch engine has stopped unexpectedly and it has done this 'n' time.
Please help
Thanks
Smitha
View 5 Replies
View Related
Apr 12, 2008
I have SQL 2005 (v9.0.3042) on Windows Server 2003. The sql server is running under LocalSystem account.
I am trying to enable SSL encryption as described in the article http://support.microsoft.com/kb/316898.
I have logged onto the machine as an administrator when creating a new certificate request in MMC. I have set "Force Encryption" to true on server and restarted the server.
However all my clients (.net code, SQL Server Management Studio) successfully connect to the server without "Encrypt=Yes". I expected to see a ssl error or some kind of error denying connection because the cliend did not request ssl ecnryption.
what am I missing? any help would be greatly appreciated.
thanks
View 8 Replies
View Related