Hi
Please help me sort out this issue,
Can Logshipping server be a primary server for any other server.
Or any other way to transfer the data [DTS] to third server once in a day so that third server will also update.
Thanx in Advance
Regards DinO
Dina Satam
I hope this hasn't been answered recently..
I have a primary SQL cluster, logshipping my SQL 2005 db to a secondary server used for the logshipping/recovery server only.
Say my primary server starts on fire, melts down, etc. How do I bring the database on the secondary server online? Do I need to roll in the transaction logs, etc.? I'm looking for a step-by-step, as I am very new to SQL.
I've seen some articles in the SQL Books Online, but things don't seem to work correctly when I follow the steps.
Please help,
THanks..
Hi,
I am doing a logshipping in sqlserver2000.
I have PrimaryServer database A and Secondaryserver A which is already existing. Now if i am trying to establish logshipping with this existing db in secondaryserver it says db A in destination is not in standy mode.
Please help me how to bring the db in standy mode that can be used to do logshipping.
Thanks
Md S.Hassan
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies View RelatedI 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.
Inside my sp on the Primary server are 3 commands:
exec msdb..sp_start_job @job_name = N'JobA'Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â --
Running on Primary ServerEXEC Server2.msdb.dbo.sp_start_job @job_name = N'JobB';Running on Secondary ServerSelect * from Server2.Table                                 --
Running on Secondary Server
So far the commands are working just fine when I kick them off one at a time. But when I kick off the sp as a whole I notice that the timing just isn't keeping up. For instance, Step 3 is running before Step 2 is finished. It appears that as the Primary Server kicks off Step 2, it doesn't seem to care about the outcome and moves right to Step 3 which in turn provides me erroneous data.How can I get Step 3 to wait until Step 2 is actually finished keeping in mind the Secondary server is part of this equation?
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies View RelatedHi Everyone
I had a table in SQL Server database with data.. By mistake I forgot to put the primary key (composite Key) in the table.. Now I want to create a composite key. But the duplicate values in those fields (say 2 fields) doesn't allow me to set the composite key in the corresponding table. Is there any way to do it?
Please help me...
Thanks in advance..
Hi Guys,
We have three servers.
Server A - Primary
Server B - Mirror
Server C - Stand Alone (also used for reporting, but, not using SQL Reporting).
All the three servers are interconnected using linked servers.
Here€™s the brief overview of my problem:
A certain procedure requires me to get live data from the 'Primary' to Server C on regular basis. This procedure will be scheduled to run once a day on Server C.
Here€™s the code:
create procedure sp_getDataFromPrimary
as
--First I check if Server A is primary
if exists( select * from [serverA].master.sys.database_mirroring where name = 'database1' and
mirroring_role_desc = 'primary')
begin
insert into [serverC].[database1].[dbo].[table1]
select * from [serverA].[database1].[dbo].[table1]
end
else
begin
insert into [serverC].[database1].[dbo].[table1]
select * from [serverB].[database1].[dbo].[table1]
end
go
When I try to create the above procedure, I get an error message similar to 'cannot access
[server B].[database1].[dbo].[table1] as the database is being mirrored. I tried to use variables for the server, database and tables, but, no avail -- I still get the same error.
My qn now is : How can I make this work. If you think my approach is faulty, pls, suggest any other.
Eagerly awaiting for your response.
Thank you.
p.s: Cannot use SSIS for it will complicate the other additional processing that needs to be done.
Hello,
I would like to know how do I carry out this two cases in efficient way:
1. I have two SQL 2005 servers running. One as primary, one as secondary. I would like to synchronize or replicate the transactions at real-time.
2. When primary goes down, I would like my secondary server to take place in needless of IP changes and application settings.
If you have a good architecture on this, please share with me. I am not sure whether my questions are clear enough or not. I also will be reading some articles about these. Thank you in advance.
Regards,
Paing
Hi,
I need a sequential primary key to store a document number.
Is IDENTITY(1,1) the best way to achieve this on SQL Server 2000?
If I have a great number of users on my application doing an insert on the DB at the same time, could this lead to any problem like it trying to insert the same PK for some of them? Can this happen, and if so what can I do to prevent this?
I've been reading about NEWSEQUENTIALID() but it's only avaiable on SQL Server 2005 and i'm still using 2000.
Another question.. should I avoid using NEWID() and use IDENTITY instead? I've been reading that newid() lowers the system performance because all values are non sequential.
Thanks!
In SQL Server 2000, I'm looking for input as to how to set up primary keys (PKs).
Is it safe to use the IDENTITY feature using AUTOINCREMENT as a setting for a PK, or is it best to generate my own and check for dups?
My concern is whether SQL Server at any point will taint the numbers used as the PK -- because as you know, these PKs are going to wind up linking into (relating into) other tables (e.g. client orders).
Certainly it's vital that the clientID not get lost/changed during any regular DB maintenance or use.
Any input would be appreciated.
Hi,
The way i generate my primary keys is to keep a table
PrimaryKeyTable
PrimaryKeyName : varchar (name of the primary key, for example UserID)
PrimaryKeyID : int (the next key should have the number stored in that
field, if it was 12 then next time i add a row the UserID will be 12)
After adding a row, i increment the value of primaryKeyID.
Is there a way to let SQL server to handle the primary key? To let SQL generate the key himself instead of me?
Thanks,
Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.
HELP!
Hallo,
Following scenario:
Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int
PK_table1 on Year,Quarter,Hits
If i do a "Select Year From table1 group by Year", the executionplan looks like
select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?
select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL
|--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Thank You!!
I have a table to store int primary keys. Where the value nextID is the value to use.I need to get the value and update it (nextID + 1) before the next person gets it.I thought using tran would work but it doesn't stop a select.How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.
BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'
WAITFOR DELAY '00:00:20'
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
COMMIT TRAN
I really want to do this in the reverse order but it doesn't work. But if I do this and have another query to just do a select:
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
This query never returns. Doesn't the COMMIT TRAN release the lock on the table?
I have tables in database where a VARCHAR(50) string is unique identifier. The database currently has an integer identity column as clustered primary key, and there's a non-clustered index on string column. The customer always queries based on a defined set of the identifier (string) column.
I wonder if someone sees an advantage of adding a persisted computed column to the table as the checksum of the string column, and then create a non-clustered index on the checksum and the string. When a customer requests data, we would compute the checksum of the customer provided identifier and add to the where clause or join, that the checksum and string must match.
Will SQL Server perform checksum check (integer) and only if it succeeds, perform the string check, in which case I see an advantage of added the checksum column? Or will SQL Server always check for both the checksum and string, in which case the additional column only adds unnecessary overhead? To note is the fact that the table(s) will have millions of rows, but the customer will request data for at at most, 100 or so identifiers.
Update: Changed original post as I figured it was not the problem I stated.
Hi all,
Heres the problem. I have a table in SQL Server with two fields, PartSubCategory and PartCatID. PartSubCategory is the primary key. When I import an excel sheet with identical columns into this table, the primary key column in the table gets sorted automatically (alphabetically). I don't want this column sorted but to import the rows from excel in the order as they were. Is this some indexing issue? Is there a fix?
Thanks a lot...Bullpit
Background:
* SQL Server 2008 R2
* Database was created from a third party product. The product writes to the 3 tables that I need to make changes to 24/7 and downtime is not an option. All changes must be done live.
* Database overall size is ~200 GB
* The 3 tables I must update make up ~190 GB of that space.
* Tables have no primary key or ID columns. Therefore, the data is highly fragmented.
* Of the ~190 GB of space allocated for the tables, there is roughly 70 GB of actual data.
* Rows of the table are not guaranteed to be unique. In fact, on one of the tables, tests were ran with a small sample of data and duplicates were very much evident.
What I'm trying to accomplish here is to get an ID column added to the 3 tables and set that ID field as the primary key. Doing so will force the data to become much less fragmented than it is currently and with purging and new inserts, eventually fragmentation will be nearly non-existent.
Problem:
Making table changes on tables this large while data is constantly being added poses many risks and can cause data loss. This was tried on a smaller table than these three and the entire table was lost in the process. Restore from backup was needed to get back to most recent log backup point.
Original Solution:
My original plan was to create a backup of each table and run the script below to migrate the majority of the data temporarily into the new table. I could then update the original table (which now would contain much less data) and then migrate the data back.
CREATE TABLE #temp
(
MsgDate varchar(10)
,MsgTime varchar(8)
,MsgPriority varchar(30)
,MsgHostname varchar(255)
[Code] ....
Original Solution Problem:
The problem with the solution above is that it calls the DELETE function on the original table using the values from the temporary table. When there are duplicate rows, which have not all been inserted into the backup table yet, they will all be removed from the original table because there is nothing unique to separate them out. In my testing, I had 10,000 rows in the original table and ended up with 9,959 rows in the backup table.
Question 1: Is my approach to making these table changes reasonable?
Question 2a: If so, how can I make sure I don't lose data as part of this temporary migration of the data to my backup tables?
Question 2b: If not, what would be a better approach that isn't going to cause disruption to the application that INSERTs data 24/7 and won't have any risk of data loss?
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
View 9 Replies View RelatedI have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies View RelatedIn a new instalation of SQL Server 2005. I've made a restore from SQL Server 2000 database.
Solve some problem with the user, and it looks ok.
I'm using a asp web aplication, with vb dll, and change the sql provider from "SQL Server" to "SQL Native Client". It works, but in some recordsets the value a recive for the table primary key is null?????
If execute the querie in the database, the result has values in the primary key.
the same appends when i connect to an SQL Server 2000 using "SQL Native Client".
Please help, what can i do.
Hi Everone,
I need help on how to produce a SQL Server Database Primary Data File, i have read the Visual C# 2006 book by Deitel and Deitel unfortunately the book does not say how to produce the forementioned data file. The software that i currently have to help me produce this data file is Visual C#, J#, Web Development, SQL Server 2005 and Office 2007.
I have a good understanding of databases from studying SQL and Oracle but i am not sure which is the best software to use to produce the Primary Data File, ideally i would like to use Access from my Office 2007 package.
Many thanks for your help in resolving this problem.
Hi,
I am trying to do logShipping from one server to another.
I created the logins and restored the DB(user) with the option -
"Leave DB ReadOnly and able to restore logs"
I was surprised to see that the DB got restored but there were no users in that DB.
Since this is a read-only DB I cannot run commands like Sp_change_users_login etc.....and neither can I create a user.
Any thoghts what might have gone wrong?
TIA.
Hi,
I have a question.
We have created a Logshipping (customized).
(We didn't use the logshipping wizard SQL 2000)
On Primary server
The backup strategy is :
Everyday we take a Fullbackup followed by every 15 mins we take transaction log backup.
Day 1:
Full Backup at 12:00 AM
Transaction log backup from 4:00 AM to 11:45 PM
Day 2:
Full Backup at 12:00 AM
Transaction log backup from 4:00 AM to 11:45 PM
Day 3:
.
.
.
On Secondary server
Everyday will restore the full backup daily and followed by the transaction log backup restore with no recovery mode.
Day 1:
Full Backup Restore at 2:00 AM
Transaction log backup Restore from 5:00 AM to 12:45 AM
Day 2:
Full Backup Restore at 2:00 AM
Transaction log backup Restore from 5:00 AM to 12:45 AM
Day 3:
.
.
.
The problem we have is
A third server which is remote need the full backup for the first time only and everyday they need a log backup alone and not the daily fullbackup to logship the server. Will that be possible
using the logs alone for logshipping but not considering the full backup file.
Third server
Like:
One time Full Backup Restore at 4:00 AM
Day 1:
Only
Transaction log backup Restore from 9:00 AM to 4:45 AM
Day 2:
Only
Transaction log backup Restore from 9:00 AM to 4:45 AM
Day 3:
.
.
.
Don't the server throws an error saying that
Msg 4305, Sev 16: The log in this backup set begins at LSN 13000001816200001, which is too late to apply to the database. An earlier log backup that includes LSN 13000001723100001 can be restored. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
We don't know how to fullfil the request. I don't think Logshipping wizard skips the full backup file for logshipping if it has been schedule for full backup everyday and logbackup every 15 mins.
Please help. is there anyway we can solve this problem.
Thanks,
Anu
i have configured logshipping how to change roles is
the question. By using sp_changeprimary role the database
has become suspected sp_change secondary role and sp_change monitor role is not working please guide on this issue
HiI have a problem in logshipping any one help would be appreciated.during logshipping when i am adding data (log.data,transactlog)to thedestination database when i select the destination server name(server2)itdidn't showing any drives present .instead showing local drives.if i select3rd server then it shows perfect all the available drives .If any one knowsthe error please let me know u help would be appreciated.Thanks,pardhi--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200509/1
View 4 Replies View RelatedHi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks
I want to created composite primary key using MS SQL Server Enterprise Manager Interface. I want to use Interface instead of writing it in CREATE Table statement. I was able to create foreign key using this Interface using the "Manage Relationship" option. But cannot find how to add primary key consisting of two fields. Any help regarding this is highly appreciated.
View 2 Replies View RelatedHeys
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table
- call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
thx a million in advance!
Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.
I am looking for the best practice. If I have 3 tables
1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)
I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).
Thanks for taking the time to answer this question.
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)
This Query returns the following result:
SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April
[code]....
which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.