SQL Server 2008 :: Lock Out Users Without Affecting Replication?

Sep 1, 2015

We are approaching the "go live" date of a big application upgrade, so while the application is being upgraded i was asked to lock out all users and backup all databases and make sure the data isnt changed during a period of time

It turns out, as one of my databases is being replicated to another server, i cant put that database in read_only mode, or restricted_mode, without removing the replication.

Removing the replication means i have to set it up again and that means another 3 hours added to an already cram-packed weekend without sleep!

How I can lock out the users to make sure there are no changes to a database other then read_only/restricted_mode/single_user ?

I really don't want to touch that replication...

View 0 Replies


ADVERTISEMENT

SQL Server 2008 :: How To Find Order Of Lock Taken And Released

Feb 16, 2015

I have express edition [advance] of sqlserver 2008 r2 , is it possible to trace every event with out using profiler as u know it does not ship with it.

Basically i want to see how locks are taken and released in each isolation level when query is executed. I am using

SELECT resource_database_id, t.resource_type , t.resource_associated_entity_id,partitions.index_id,blocking_session_id,
indexes.name as index_name,o.name,
t.request_status, t.request_mode,t.request_session_id,
t.resource_description

[code]....

But i am not sure of the order becuase total_elapsed_time is same for many locks

like

resource_typeresource_associated_entity_idrequest_session_idrequest_modeindex_namenameresource_description
DATABASE051SNULLNULL
OBJECT209427886651Sch-SNULLwt
OBJECT209427886652IXNULLwt
PAGE7205759715088793652IXPK_wtNULL1:204815
KEY7205759715088793652XPK_wtNULL(4fb98fd9bfad)
DATABASE052SNULLNULL

View 5 Replies View Related

SQL Server 2008 :: Read A Value From Table And Increment It By 1 - Row Lock

May 12, 2015

I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.

CREATE TABLE [dbo].[tblOnboardingSequence](
[OnboardingSequenceID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[NextNumber] [bigint] NOT NULL,
CONSTRAINT [PK_OnboardingSequence] PRIMARY KEY CLUSTERED(
[OnboardingSequenceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

My Stored Procedure has below logic

DECLARE @NextNumber BIGINT
----------- Acquire row lock here
SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO'
UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'
----------- Release row lock here

I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query.
What si the best way to deal with this?

View 2 Replies View Related

SQL Server 2008 :: No-lock Across Linked Server With Join?

Mar 9, 2015

I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).

I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.

update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id

From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.

Is there some way I can limit the content on sB by my temp table on sA but still use nolock?

View 9 Replies View Related

SQL Server 2008 :: Logins Or Users Without Any Permission?

Feb 13, 2015

How to find the list of logins/users who do not have any permission (except default Public) access.

View 4 Replies View Related

SQL Server 2008 :: Retrieving Same Identity Column Value By Multiple Users

Mar 11, 2015

I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.

DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR ​

From my queries,I am providing a available department information.Each user needs to get unique available department information.But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.

View 6 Replies View Related

Letting Users Who Visit A Website Add Records To A Sql Server Database Using Visual Studio Web Developer Express Edition 2008

May 5, 2008

in visual studio web developer express edition 2008 i added a new item this was a sql server database which i called test2. I added a table called comments, and the fields name, email, comments, IPAddress, and Date and in my comments.aspx page i added a name textbox, a texbox for email, a textbox for comments and the submit button i double clicked on the submit button and added the following code
 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim mydS As New SqlDataSource        mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString1").ToString         mydS.InsertCommandType = SqlDataSourceCommandType.Text         mydS.InsertCommand = "INSERT INTO comments(Name,Email,Comments,IPAddress,Date) VALUES (@Name,@Email,@Comments,@IPAddress,@Date)"         mydS.InsertParameters.Add("Name", TextBox1.Text)        mydS.InsertParameters.Add("Email", TextBox2.Text)        mydS.InsertParameters.Add("Comments", TextBox3.Text)        mydS.InsertParameters.Add("Date", Date.Now)        mydS.InsertParameters.Add("IPAddress", Request.Params("REMOTE_ADDR"))         Dim rowsaffected As Int16 = 0        Try            rowsaffected = mydS.Insert        Catch ex As Exception            Server.Transfer("errors.aspx")        Finally            mydS = Nothing        End Try        If rowsaffected <> 1 Then            Server.Transfer("errors.aspx")        Else            Server.Transfer("confirm.aspx")        End If    End Sub     Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged     End Sub but when i run it it gives me a problem with this line mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString2).ToString and says something abt unidentified object and I want to know how do i set the connection string or alternatively where?????? and my table does not update with the corresponding values i enter on the webpage. i also changed the authentication mode to forms instead of windows as i was advised but i'm still having a problem

View 12 Replies View Related

SQL Server 2014 :: CASE Condition Not Affecting CAST Output

Jun 10, 2014

I've a variable in my SP that I'd like to use to change the format of my output. Try this!

select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Then this:

select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):

select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2

Work fine.

View 8 Replies View Related

SQL Server 2008 :: Reporting From A Replication Slave?

Feb 11, 2015

We have a SQL Server 2008 that is being replicated transactionally (log shipping) onto another SQL Server 2008.

Can we report against the replication slave without disrupting the replication process? I have been informed that the slave server needs exclusive access to the db in order to apply the transaction log.

If this is the case, are there any configuration changes which would allow us to report from the slave without impacting replication?

View 7 Replies View Related

SQL Server 2008 :: Configure Replication Using A Script?

Feb 26, 2015

I am trying to configure replication using a script. I created the distribution database as well as the publisher. Now, I am running the stored procedure in one of the subscribers to create subscription. However, I keep getting the same error over and over again. The error reads:

msg 50000, Level 16, State 1, Procedure usp_create_AccountSubscriber, Line 43
Failed to execute SP usp_Create_AccountSubscriber: This database is not enabled for publication

what does it mean? how can i solve it?

View 6 Replies View Related

SQL Server 2008 :: Add Subscriber To Ongoing Replication?

Feb 27, 2015

i have replication setup and working, there are 3 subscribers and now i want to add a 4th one. is it ok if i just add the subscriber and reinitialize replication?

View 1 Replies View Related

SQL Server 2008 :: Snapshot Replication To Different Schema Name

Apr 25, 2015

I need to setup snapshot replication for 1 large table.

Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1

In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)

View 1 Replies View Related

SQL Server 2008 :: Archive Data Through Replication

May 5, 2015

I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data.

Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.

My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.

There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.

View 5 Replies View Related

SQL Server 2008 :: Replication For Read Only Database

May 18, 2015

I'm in a project to replicate a database from the production environment to another sql server.

The idea is use it for some biggest querys because in the production database we had experimenting various problems. What is the best way for do it? Transactional replication? Log Shipping? I'm reading absolutely everything but I can not get decide.

Both servers are SQL Server 2008 R2 and they are in the same LAN.

View 6 Replies View Related

SQL Server 2008 :: Schema Changes With Transactional Replication

Jun 15, 2015

How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?

View 2 Replies View Related

SQL Server 2008 :: Replication Backup And Restore

Aug 5, 2015

SQL Transaction replication, specifically SQL backup and restore Transaction replication. So Scenario,

S1 = Primary Server 1
R1 = T - Replication Server 1
R2 = T - Replication Server 2

So we have S1 replicating to R1, and we want to build another subscriber which is R2.

Can I take the Replicated Database from R1, backup it up, then restore it to R2, and create the publication/subscription?

Will that work? if not, is there an easier way to avoid the snapshot? the reason i ask this is because we do have replication snapshot, but takes long. One of my Colleagues stated he tried this, however replication made duplicate rows on R2, which is why he had to use replication snapshot.

View 0 Replies View Related

SQL Server 2008 :: Merge Replication - Replacing A Subscriber

Mar 4, 2015

We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.

The original server is an ancient Win2003 86x

The Server team will build a new Win2008r2 64x, and use the same name and IP address

And I'm tasked with the SQL part.

I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.

How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?

View 1 Replies View Related

SQL Server 2008 :: Transactional Replication (Snapshot Agent)

Mar 19, 2015

Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?

The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.

We have a post snapshot script that updates these additional columns on the subscriber.

I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.

Is this possible?

View 1 Replies View Related

SQL Server 2008 :: Transaction Replication Functionality Within Mixed SPs

Jul 1, 2015

Currently, we are on SQL2008R2 EE SP1 CU13 across the board.

We are planning on upgrading the primary SQL server to SP3.

Question: Will transactional replication continue to work properly even if the Publisher is upgraded to SP3 but the subscribers remain at SP1 CU13 ?

View 2 Replies View Related

SQL Server 2008 :: Indexes On Subscriber Database With Replication?

Jul 15, 2015

When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.

so reinitialized with new snapshot but all the indexes are gone from the subscriber database.

How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot

View 5 Replies View Related

SQL Server 2008 :: Snapshot Replication Not Copying Constraint Key?

Oct 27, 2015

We have a domain joined SQL 2008 R2 server performing a snapshot database replication to a non-domain joined SQL 2008 R2 server. The snapshot replication is working with one exception. Under one of the tables, there is a Key, Constraint and Index that are part of the database. The Key and Indexes is copying over. However, the constraint is not. Why would the Key and Index copy but not the Constraint?

View 3 Replies View Related

SQL Server 2008 :: Replication Subsystems Failed To Load

Sep 4, 2009

I am running SQL 2008 Enterprize Edition with SP1 on Windows 2008. I am trying to set up replication. I have completed the following:

1. Created distribution Database
2. Created publisher
3. Granted SQL Agent access to the ...MSSQL100Com folder to execute the agent_exe files
4. Granted SQL Agent access to ...MSSQLinn where the subsystem_dll files are located
5. Granted SQL Agent write permissions to ...MSSQL
epldata in order the write the bcp files

Each time I try to initialize the snapshot, I get the following errors in the SQL Agent Log

1. Log Step.......cannot be run because the LogReader subsystem failed to load. The job has been suspended.
2. Log Step.......cannot be run because the Snapshot subsystem failed to load. The job has been suspended.

I found posts where the records in the msdb.dbo.syssubsystems pointed to different folders than where the dll and exe files are located. So, I checked that, but they are correct.

The SQL Agent has sysadmin on the SQL Server and is using a windows service account.

I believe it is a security issue because I can run the executables from the command prompt to generate the snapshot for the publication. Have I missed the forest for the trees?

View 8 Replies View Related

SQL Server 2008 :: Replication Error - Field Size Too Large

Feb 1, 2011

I've got two databases on the same server and replicate some tables from one database to another.The replication is configured so not to drop the table if it exists, but to delete the data based on the filter if one exists. There are two tables on the subscriber that have some extra columns.

I get "field size too large" error when trying to replicate them. Is there a workaround without having to make the publisher and the subscriber tables identical by schema?

View 5 Replies View Related

SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication

Feb 20, 2015

Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....

View 0 Replies View Related

SQL Server 2008 :: Script To Return Replication Status As Small Int

Mar 26, 2015

I need a script that will return the replication status as small int.

View 1 Replies View Related

SQL Server 2008 :: Replication Articles Read-only AND Updateable At Same Time

Apr 21, 2015

We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.

We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.

Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.

We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.

It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.

I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)

So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?

View 9 Replies View Related

SQL Server 2008 :: Snapshot Replication Of 1 Table To A Target DB With 100 Tables

Apr 25, 2015

Regarding Snapshot replication....

If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....

Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?

View 2 Replies View Related

SQL Server 2008 :: Find Out Type Of Replication Pull Or Push

Apr 28, 2015

SELECT
P.Publication
,P.Publication_type
,S.Subscriber_ID
,S.Update_Mode
FROM MSPublications P
INNER JOIN MSSubscriptions S
ON P.Publication_ID = S.Publication_ID

give me publication_type=0. So it is transactional replication but how do we know that is pull or push?

View 2 Replies View Related

SQL Server 2008 :: Estimate / Calculate Performance Overhead From Replication?

Jun 10, 2015

I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:

* Number of articles being replicated
* Types of articles being replicated
* Number of DML transactions that would result in delivery of replicated data

Any way to turn this into a meaningful metric?

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication And Subscriber Log Growth Errors

Jun 14, 2015

I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup

The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?

View 3 Replies View Related

SQL Server 2008 :: Replication Of Stored Procedure Execution Fails

Jul 29, 2015

I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:

IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name

[Code] ....

I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:

ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)

How can I change my code to workaround this? Perhaps some explicit transactions?

View 6 Replies View Related

SQL Server 2008 :: Temporarily Stop Replication / What To Do When Publication Is Dropped

Aug 12, 2015

I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.

I have two questions:

1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.

2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot In Multiple Files

Sep 15, 2015

We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:

MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp

With other tables, I'll get just one LARGE snapshot file, named:

MyOtherTable_4.bcp

In the latter case, the file can be very large (most recent is 38GB).

In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'

My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.

View 0 Replies View Related







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