Synchronize 2 Databases On Different Servers
Apr 10, 2006
Hi,
I was wondering if the following is possible: I have 2 SQL Express servers on different machines. One holds the production and one the development database. On a request basis I'd like mirror all the data of the production database to the development database. Right now I have to stop the sql engine, zip the mdf, send it over, attach it to database, etc. and that's quite cumbersome. So, is there an easier way to do this? If not with SQL Express, is it possible with the full version?
Thanks,
Tom
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hi, anybody can help me.How can i synchronize 2 tables on 2 different sql servers 2000i mean TABLE1(col1, col2, col3, col4) andTABLE1(col1, col2, col3, col4, col5, col6)the first 3 colums are the same in rwo tables.Thanks--Message posted via http://www.sqlmonster.com
View 6 Replies
View Related
Mar 29, 2006
What is the easiest way to synchronize all data and database objects between my development machine and hosting server? Is there any SSIS free script I can use?
View 7 Replies
View Related
Feb 18, 2002
Hi,
I have tried to synchronize master..sysxlogins table from production server to
a testing server. All the databases in the testing server will be loaded from the production server. In order to save drop and recreate the database's users in every database restore, I have tried to find out a quick way to synchronize the sysxlogins table. However, when I load the sysxlogins from the production server onto the tempdb in the testing server, I get the following error :
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Any comment and suggestion is highly appreciated.
Thanks
The table structures (output of sp_help) of the temporary table and master..sysxlogins are as follows :
1. sp_help testjoe4
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
testjoe4 dbo user table 2002-02-19 15:27:31.930
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
srvid smallint no 2 5 0 yes (n/a) (n/a) NULL
sid varbinary no 85 yes no no NULL
xstatus smallint no 2 5 0 no (n/a) (n/a) NULL
xdate1 datetime no 8 no (n/a) (n/a) NULL
xdate2 datetime no 8 no (n/a) (n/a) NULL
name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
password varbinary no 256 yes no no NULL
dbid smallint no 2 5 0 no (n/a) (n/a) NULL
language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
isrpcinmap smallint no 2 5 0 yes (n/a) (n/a) NULL
ishqoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL
selfoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
2. sp_help master..sysxlogins
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
sysxlogins dbo system table 2000-08-06 01:29:12.500
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
srvid smallint no 2 5 0 yes (n/a) (n/a) NULL
sid varbinary no 85 yes no no NULL
xstatus smallint no 2 5 0 no (n/a) (n/a) NULL
xdate1 datetime no 8 no (n/a) (n/a) NULL
xdate2 datetime no 8 no (n/a) (n/a) NULL
name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
password varbinary no 256 yes no no NULL
dbid smallint no 2 5 0 no (n/a) (n/a) NULL
language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
isrpcinmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
ishqoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
selfoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ncsysxlogins nonclustered located on PRIMARY srvid, sid
sysxlogins clustered, unique located on PRIMARY srvid, name, sid
No constraints have been defined for this object.
No foreign keys reference this table.
Table is referenced by views
--------------------------------------------------------------------------------------------------------------------------------
View 1 Replies
View Related
Apr 17, 2008
Please can somebody help me with the following:
Our company has an enterprize site (a very large one). From time to time we need to make changes in it. In other words, we had to synchronize one database (local where we make all changes) and database on hosting. So, we did it manually (wrote scripts and inserted them in database on hosting). But as the databases grew it became impossible to synchronize everything manually. So we need some tool which could cope with this task. The problem is that, as I know, there are many tools which can do it, but we are tight in time and can't evaluate them all. Please share your ideas on what tool is worth evaluating?
Thanks in advance.
View 9 Replies
View Related
May 8, 2008
Hi,
I have the following scenario:
There are 3 web applications in a local network which all have a local copy of the same database. Each of them can alter their local database (just the data, not the structure), and every time that happens the databases of the other applications should be updated immediately.
Are there any solutions out there for that? I found that most tools like slony only mirror one master database to multiple slaves, but I want every database to be able to be the starting point.
There could also be a central sync server, like in subversion..
Greets,
Patrick
View 3 Replies
View Related
May 13, 2008
Hi,
I have a scenario which I need to implement at my work.
I need to maintain one Central SQL Server DB and multiple non central SQL Server DBs at different locations. Here the same DB schema is maintained at Center & non Central SQL Servers. The requirement is: each non central DB has to synchronize with the central DB and has to update the Central DB with the changes made locally.
Here the challenge is the intermittent connectivity between the DBs. The connection is not guaranteed to be alive 24 hours a day. It may go down frequently also. I would like to know the possible ways to handle my situation (i.e. synchronization). And also please suggest me if there is any support with the SQL Server DB itself.
Thanks,
Kishore
View 3 Replies
View Related
Apr 24, 2007
i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.
is this possible?
View 9 Replies
View Related
Jun 17, 2006
I'm struggling to find anywhere some recommended techniques for synchronizing two databases.
I am writing a smart-client app that will have its own database, and then connect and synchronize with the server when available.
Can somebody send me some links that discusses some of these ideas in detail?
Thanks
View 1 Replies
View Related
Sep 4, 2007
Hi, If you have 2 databases with the exact same table columns, and in the first database in a table column the indentidy seed starts at 1 and finishes at 32,000 can you attatch a second database so that the indentity starts at 32,001and carries on. What happens if you create a site and run out of disk space and need to attach another database which is located on another server??? I am really confused about this.Hope someone can give some links to some articles about this as I can't find any information about it.How can this be done? Thanks
View 1 Replies
View Related
May 12, 2006
Hello,This is my first post, and i am looking for help about Databases in remote servers.This is my problem:1) I have a free account in "HostBasket", with 50 mb of space, and 10 mb of SQL Server... with ASP.NET 2 support --> http://www.hostbasket.com/dotnet2.shtml?LinkFrom=100602) They gave me this info for the database:Your SQL Server: sql15.hostbasket.comYour database name: myDatabaseNameYour login for the database: myLoginYour password for the database: myPasswordConnection string for ASP.NET (using ADO.NET with System.Data.SqlClient namespace):Persist Security Info=True;User ID=myLogin;Password=myPassword;Initial Catalog=MyDatabaseName;Data Source=sql15.hostbasket.com 3) Actually, i don't know what to do with that info... I mean, i have read so much info, and all people saying "their way" to do things... oh, I am so confused...4) I read in a website, this: "the database can be uploaded as a FILE saved in the App_Data folder, under the .mdf extension"I read that from : http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/vwd.aspx5) In the url given above, there is a "step by step" example... i follow it, exactly (i start a asp.net website, i create the database and the table, i show the data with the grids, etc )6) All is OK until here, i mean, when i run the website with VWD, it shows me the info OK.7) But... ¿How can i do this works on the Remote Server i described in 1) and 2) ?I know how to upload my website, in fact i uploaded a very basic one, and all was OK, but when i work with databases starts my problem...So, i am almost sure, that i have an error in the "Conecction String"... connectionString="Data Source=.SQLExpress;AttachDbFilename=|DataDirectory|MiBaseDeDatos.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" ¿How should it be, to work with my free remote server?or,if you have done the "easy way" of uploading the .MDF file in the server (but a server different of mine) please, tell me the "how to..." (and the server you used :p )--------------------Ok guys, i am a Chilean young (i dont speak english very well) and i tried to write my ideas in an easy way, so I REALLY HOPE you can help me.I am a New (very new) on this (vwd , .net , asp.net ) in fact, i am using this just 3 days ago...THANK YOU SO MUCH,Carlos,La Serena, Chile.
View 7 Replies
View Related
Feb 15, 2002
I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?
Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.
View 2 Replies
View Related
Mar 27, 2001
I'm fairly new to a DBA role, I've been tasked with documenting all the SQL servers/databases. Is there anywhere on the Web where I could find a standard list of what needs to be documented. Any help/advice would be appreciated.
View 1 Replies
View Related
Jan 11, 2000
I tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.
The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.
How are the SQL Server logins transferred ?
Richard
View 2 Replies
View Related
Aug 23, 2013
We are wanting to know how to connect 2 databases residing on 2SQL servers. The workstation has access to both databases. Join on Cols 1 and 2.
Server1, Database1, Table1, Column1 (Char)
Server2, Database2, Table2, Column2 (VarChar)
View 2 Replies
View Related
May 17, 2006
Hello,I wish to see if the tables from database A server A1 exist in databaseA on server B1.I setup a linked server from my first server (robertcamarda) to atarget (cognos-dev)This works from robertcamarda:select count(*) from [cognos-dev].ds_v6_source.dbo.stdmasand this works:select * from ds_v6_source.information_schema.tables order bytable_namebut this wont work:select * from [cognos-dev].ds_v6_source.information_schema.tables orderby table_nameError:Msg 117, Level 15, State 1, Line 1The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'contains more than the maximum number of prefixes. The maximum is 3.I want to do something like:select * from ds_v6_source.information_schema.tableswhere table_name not in (select table_name from[cognos-dev].ds_v6_source.information_schema.tables order bytable_name)so I can see of the sql server (robertcamarda) has any missing tablesthat exist on the server (cognos-dev)TIARobSQL Server 2005 Enterprise
View 3 Replies
View Related
Jan 25, 2008
Hello,
We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example
Code Snippet
CRM database on Server1
Table Accounts
Columns
AccountID UniqueIdentifier Primary Key
Name NvarChar(50)
Application Database on Server2
Table Transaction
Columns
TransactionID UniqueIdentifier Primary Key
AccountID UniqueIdentifier
Amount Money
Would it be possible to create a T-SQL statement that will select
Code Snippet
Select
Server1.CRM.Accounts.Name,
Server2.Application.Transaction.TransactionID,
Server2.Application.Transaction.AccountID,
Server2.Application.Transaction.Amount
From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction
ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)
I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.
Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.
Many thanks for any information provided.
View 3 Replies
View Related
Feb 23, 2008
Code Snippet
SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1
FROM ReciptItems LEFT OUTER JOIN
Acc_mydbname.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1
How Replace Acc_mydbname with (SELECT AccountDBName FROM Config)
(SELECT AccountDBName FROM Config) ='Acc_mydbname_2008.dbo.'
View 6 Replies
View Related
May 29, 2007
Dear all
I have tow server on the same intranet. One server has a sql server 2000 database and the other one has SQL server 2005 databse.
The sql 2000 database has a table called employee. When ever a new employee is inserted in the database i would like the same values to be sent to the sql 2005 database. But this cant be done on the application level. It has to be done in the database. The application level can not be changed.
I was thinking a trigger but how to achieve the writing from one database to another. If they were on the same server then it would be easier but because they are on different servers i dont know how to do it.
Has anyone had similar issue before?
Any help is apreciated.
Sincerely
Dan
View 2 Replies
View Related
Apr 25, 2008
I'm working with the SQL Report Viewer in VS2k5. In the Data tab, where the Dataset drop down list is, I click the "..." to edit the dataset. I am currently using the following for the datasource...
Data Source=ServerName;Initial Catalog=DatabaseName
In one database, I store an employee ID number. I need to access a different database on a different server to reference the employee ID and pull the employee's name. Is there a way to specify two different databases on two different servers in the connection string above?
Thanks!
View 1 Replies
View Related
Oct 27, 1999
Hi there,
I was just wondering is it possible to select data from two different databases on two different servers?
ie Select * from
Server1.databasename..table, Server2.databasename..table
If anyone has any suggestions they would be appreciated,
Thanks,
Fin
View 2 Replies
View Related
Aug 15, 2005
Hello, I need a tool for management ms sql: compare list of databases on 2 servers (not structure and/or data of 2 databases), copy from one server to second new databases, delete from the second server ... Not to propose Copy Wizard.
Thanks
View 4 Replies
View Related
Dec 8, 2004
I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?
View 8 Replies
View Related
Nov 14, 2007
Is it possible to get an aggregate list of databases from multiple servers, without using SMO?
All target servers are running SQL Server 2005.
Ben Aminnia
View 2 Replies
View Related
Jan 17, 2006
Hi All,
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 I would simply do the following:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
What do I need to do to perform the same operation in SSIS world?
Thank you !
View 1 Replies
View Related
Jun 20, 2007
Hello:
I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.
If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.
Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?
Thanks,
Cyndi
View 1 Replies
View Related
Jul 3, 2006
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
View 12 Replies
View Related
Aug 12, 2015
I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
View 4 Replies
View Related
Apr 10, 2008
Hi,
Im trying to access data from a database on another server in a SQL 2005 query.
use Bury2k29.ServiceDeskForms
select .......
but I get the message
could not locate entry in sysdatabases for database 'Burky2k29'. No entry found with that name. Make sure that the name is entered correctly.
Bury2k29 is the name of the server, and ServiceDeskForms is the database I want to access.
When I open a blank query and enter only the code to access that database it runs fine.
Any ideas?
View 4 Replies
View Related
Oct 31, 2007
i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done
my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .
i am going to place my web application on local server (C# asp.net 2.0) .
thank you,
i tried this code
c# file
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Uupdatedata();
}
public void Uupdatedata()
{
string constr1=ConfigurationSettings.AppSettings["abccon1"];
string constr2 = ConfigurationSettings.AppSettings["abccon2"];
System.Data.SqlClient.SqlConnection dbcon1 = new System.Data.SqlClient.SqlConnection(constr1);
dbcon1.Open();
System.Data.SqlClient.SqlConnection dbcon2 = new System.Data.SqlClient.SqlConnection(constr2);
dbcon2.Open();
SqlDataReader dr;
SqlCommand cmd = new SqlCommand("select * from Unit_Master",dbcon1);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string Sql = "insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() + "," + dr.GetString(1) + ")";
SqlCommand cmd1=new SqlCommand (Sql,dbcon2);
//cmd1.EndExecuteNonQuery ();
}
//dr.Close();
//dbcon2.Close();
}
web.config<configuration>
<appSettings>
<add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/>
<add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/>
</appSettings>
<connectionStrings/>
this 2 databases are from single my loacl server
it wont work it gives exception
please healp me.
View 4 Replies
View Related
Jun 13, 2000
I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?
exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'
FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"
Thank you
View 1 Replies
View Related
Nov 20, 2007
Good morning all,
I need to create a new table on a SQL Server 2005 Database, but the data needed for the table is kept in different databases on different servers.
Half the data is on the SQL 2005 database, so that shouldn't be too much of a problem, but the other half is on a SQL Express database on a different server, is it possible to write a T-SQL query to retrieve this data on the other server?
Normally I use a create table + insert into kind of query to create custom tables but I don't know how to connect to the SQL Express server/database using T-SQL (if it's possible at all?) to do this.
Any tips, hints, ideas very welcome. Please use small words and short sentences.
Thanks,
Paul
View 3 Replies
View Related
