Comparing 2 Database Environments
Aug 27, 2013How can i compare 2 database environments (Dev vs Prod) without using third party software (IF IT IS POSSIBLE).
View 3 Replies
ADVERTISEMENT
DB Engine :: Replicate A Master Test Database To 100 Test Environments?
Oct 12, 2015We are setting up a test lab environment with 100 machines. Â We want one master testing db that gets replicated to each to run scripted application tests nightly. Â
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. Â I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Comparing One Database To Another For Changes..????
Sep 19, 2007Hi,
Lets suppose i am having 2 database that is
Database1 and Database2
Then both datbases are same having same number of tables, sp's, vies and etc.
Then wht i have done is i have added two tables in database1 tb23 and tb24, and at the same time i have added one table out of these two table that is tb23 in the database2,
Now i want a querry by using that querry i am able to find out that how many objects are there that are present in database1 but not in database2. Basically i want to do comparison in between two databases.
ANY QUERRY, OR ANY TOOL by using which we can acheive this.....
HOW????
Comparing Values Within Database
Sep 10, 2007Hi,
There are two tables in my Database, tb1 and tb2 which both have the same attribute ID. I would like to ensure that there is nothing in ID in tb1 which is not listed in ID in tb2, can anyone help?
Thanks for any info.
Albert.
Comparing Database Data
Oct 31, 2006Can anyone tell me how a program like this might work:http://www.red-gate.com/products/SQ...mpare/index.htmI want to backup databases into a central repository but I only wantthe records that have changed for that day. This program seems to do itefficiently. Does Sql Server, Oracle, etc offer any sort of built inway of doing this via metadata or a similar mechanism?I want to be able to mirror databases in such a way that I dont need tohave the admin password for them. I don't want to alter the databaseschema in any way.The only way I can think off the top of my head is to assign MD5checksum values to rows and then compare the checksums in the masterand copy, however, to do it efficiently, you'd have to save the MD5values as you go, which would involve altering the schema on the mastertable.
View 1 Replies View RelatedComparing GPS Lat/Lon Values From Database Within An Area
Apr 2, 2008Hi,
I would be very grateful if someone could help me. I have very little SQL knowledge and would like a push in the right direction:
I have a application that receives GPS lattitude and longitude values. I was originally using file base system where I would load all the points from the file into memory and do the calculations in the software but I now would like to use SQL Server Express.
I have created the tables:
[Table 1] GPS Points:
id int Primary key
latitude real
longitude real
desc nvarchar(128)
[Table 2] GPS Locations (comprises of one or more GPS Points from [1] above) to form a boundary:
GPS Locations database structure is:
locationId int primary key
gpspoint1 int
gpspoint2 int
gpspoint3 int
gpspoint4 int
desc nvarchar(128)
Table 2 contains up to 4 gps points ids from table 1 and GpsPoints1-4 can be null.
I have created the tables and I have inserted GPS values into both tables.
My problem:
I am trying to compare the current Lat/Lon (which are passed into the SQL fuction as two real datatype value) and I want to return all Table 2 locationId's where the current Lat/Lon are withing GPSPoints1-4 area.
gpspoint1, gpspoint2, gpspoint3, gpspoint4 reference an id from Table 1. I am not sure how I can do this?
I have implemetned a SQL function that returns all points from Table 1 that are within the predefined radius of 1.5 miles from the current Lat/Lon values passed in.
select desc,latitude,longitude, acos(SIN( PI()* @LatDec /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* @LatDec /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* @LonDec /180)
)* 3963.191 AS distance
FROM GpsPoints
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* @LatDec /180)*SIN(PI() * latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
) < = Radius
ORDER BY 3963.191 * ACOS(
(SIN(PI()* @LatDec /180)*SIN(PI()*latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
)
where 3963.191 is earths radius in miles,
radius is 1.5,
@LatDec is the current latitude and
@LonDec is the current longitude
Uses a lot of Trig and (for me) is quite complicated. This works very well and is very accurate. It also only uses a single table. I want to be able to now look for the current position within a boundary of 3 or more positions from Table 1 and this is where I am struggling?
Also, any suggestions on how I could do this better would be very much appreciated?
I look forward to your reply and help.
Thank you in advanced.
Error Comparing Textbox Variable To Database Value
Mar 11, 2005hi im trying to code a login page using asp.net, vb.net. i have 2 web-controlled textboxes whose values i want to compare to userID and password stored on a SQL server database called users. When button clicked, call function checklogin.
heres the code:
Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(userID.Text, False)
Else
error_log.Text = "Invalid Credentials: Please try again"
End If
End If
End Sub
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String
Dim dbConnection As New SqlConnection
Dim dbCommand As New SqlCommand
Dim dataAdapter As New SqlDataAdapter
Dim ds As New DataSet
connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users"
'server=localhost; database=users; integrated Security=SSPI;Â?@user id=sa; password=chaos
dbConnection.ConnectionString = connectionString
With dbCommand
.Connection = dbConnection
.CommandText = "SELECT COUNT (*)Â?@AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))"
End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)
Return ds
End Function
Heres the error i get:
Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near
line {''1
Source error:
Line 75:. CommandText = "declare@userID As varChar; declare @password As varChar; End With .."Line 76:.. line 77:of SELECT COUNT(*) AS pass FROM tbl_users WHERE((tbl_users.userId=@userId)AND(tbl_users.password=@password))
Line 78: DataAdapter.SelectCommand=dbCommand
Line 79: DataAdapter.Fill(ds)
ive just started using .net, im a lil lost...thanks for any help. cheers
Comparing Development, Test & Production Database
Aug 2, 2001Hi,
We're using SQL Server 2000 as back end in our web project. The problem is we've 3 different copies of same database - one each for Development, Test and Production sitting in 2 different machines.
My question is - is there any tool for comparing the objects (tables, stored procedures, etc) ?
Thanks,
Harish
Comparing Excel Data To Database Field
Aug 4, 2014I have an excel spreadsheet that only has email addresses in a single columnar format on it (318 emails). I want to check and see if any of those emails are in the database. Is there a easier way than having to enter 300+ "OR" statements?
SELECT "Name"."FIRST_NAME", "Name"."LAST_NAME", "Name"."EMAIL", "Name"."ID", "Name"."MEMBER_TYPE"
FROM "APSCU_PROD"."dbo"."Name" "Name"
WHERE Name.EMAIL='marie@bahoo.com' OR Name.EMAIL='markg@ts.com' OR Name.EMAIL='mare@t.edu'
Changing A Record After Comparing One Database Table To Another
May 26, 2007I'm using SQL Server 2005 Express.
In my main database table I have many fields but the two following fields are my main concern.
1) email_address
2) unsubscribe
In my secondary database table I have one record only.
1) email_address
What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)
I'd really appreciate any help I can get.
Thanks,
Bill
IIS & SQL Server Environments
Sep 15, 1999Hi,
Right now, we have the production and development databases and ASP pages on one server. This is causing a strain on the performance,
of course.
Well, the finally, the customer agreed to get 2 less powerful servers. This is what we are planning to do with the 2 extra servers.
Server 1 : Original Server (fast)
We will only have the Production Database (NO IIS)
Server 2: New Machine23 (faster box than Server 2)
Offload IIS away from the production Database so we can allow more memory for Database processing.
Server 3: New Machine 3 (slower box we just got)
Move the entire development environment to one server (SQL Server Database + IIS with all the ASP pages)
Does this sound OK?
or should we have IIS sitting on one box (holding pages for Development and production) and the other machine just holds the
Development database.
I would like to hear other opinions/stories on different IIS & SQL Server configurations to maximize performance.
Thanx!
Angel
SQL Server 2008 :: Comparing Integer Values Across Tables And Database Servers?
Mar 6, 2015how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
Variable Paths For Different Environments
May 22, 2007This has probably been covered in other posts. I have been working with SSIS for the past month and I am trying to follow best practices on various items. Having worked with a different ETL tool prior to this, I am wondering what is the best approach to use for Connections and File Paths.
What I would normally do with DataStage for this would be to assign a Job Variable (and eventually Sequence Variable) of the type: Path. So, if I was developing a job I would create SourceFilePath, ErrorFilePath, etc. I would use these variables in a FlatFile or Dataset Stage. For instance I would assign a filename for a source as: #SourceFilePath#SourceFile1.txt. During execution the job would load the variable and then the filename would be: C:MyDocumentsDatafilesSourceFile1.txt.
When its time to move to another environment, I don't have to worry about changing values for file connections because it is managed dynamically by a config file or whatever method.
What is the best practice that emulates this behaviour for SSIS? I've been thick and can't get my head around this. Any direction to blogs or user sites would be great. Examples, even better!
Thanks in advance.
SSIS Configuration In Different Environments
Jul 5, 2006Hello,
I want to store each SQL Server's (prod, test, etc) connection string in XML to make deployment & configuration easy. However, the connection string must be encrypted. Does anyone have any ideas or suggestions on how to accomplish both. I know how to encrypt it but I dont think SSIS can work with it.
Any help would be appreciated.
Creating Test SQL Server Environments
Nov 5, 2007Hi,
I was wondering if anyone has a neat (preferably automated) method of creating small testing databases from large production instances.
My requirement would be to copy the schema and a subset of configuration data from a production database into a test database. The subset of data would be a full copy of a subset of tables, rather than a subset of data within one or more tables. There is a mixture of SQL2000 and SQL2005 servers involved in this requirement. I'm familar with the scripting mechanisms of Enterprise Manager and Management studio and DTS packages, sufficent to perform a process like this manually, but want to productionise and schedule this process to be performed automatically.
I'm sure this must be a commonly performed task, so I'm interested to know if anyone has a "best practice" for this requirement.
Thanks,
Bill
Homogenous VS Heterogenous Enterprise Environments
Feb 21, 2008Another DBA on my team is trying to tell me that it is a widely accepted best practice to only have a single version of SQL server in the enterprise environment. This seems counter intuitive to me and I cannot find a confirming source for this assertion.
I would think that it largely depends on your environment but that once a new version of SQL server has been decided on for a given application, for whatever reasons, that new version should be used moving forward on all new development.
It also seems like that as other databases grow and hardware comes to its end of life that older databases be either migrated or ported to the new version as seems appropriate.
Is there any reason an enterprise should always be on X version and only X version?
Binary_checksum And Validated Software Environments
Jul 23, 2005Hi,It appears that binary_checksum can give the same checksum fordifferent strings, which is a bit worrying. (I guess the algorithm isthe problem in the context of a repeating pattern.)e.g.select binary_checksum('A'),binary_checksum('AAAAAAAAAAAAAAAAA'),binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A'),binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')My question...Is this approach to generating checksums adequate for managing theobject scripts in the SQL Server to ensure that they haven't changed. Iguess that the probability of somebody making a change to a script andending up with the same checksum is almost negligible. Has anybody usedthis approach in an FDA validated production environment, i.e. 'no ifs,no buts'? Would it stand up to scrutiny?Any experiences, thoughts?RegardsLiam
View 5 Replies View RelatedRS - Multiple Development Environments On One Machine
Jul 23, 2005We have multiple development environments on one machine for our VisualStudio .net projects.These are web applications so we have organized them by IP Address andHost Headers.When I installed Reporting Services, it installed into my default website (localhost) and that is fine.However, we want to be able to create our reports in our different .netprojects. The only projects that will allow me to create reports arethe projects using the default website.When I right click to create a new project, the Business Intelligenceprojects (reports) type is not there.Can I do this? If so, how do I configure it?
View 1 Replies View RelatedMixed Environments - SQL 2000 Standard && Enterprise??
Oct 6, 2005We are upgrading a production database server to new hardware. The server is currently running SQL Server 2000 Standard Edition. We are thinking about installing SQL Server 2000 Enterprise Edition, however that would mean the test server (2000 Standard) and production server (2000 Enterprise) have different edtions of SQL Server. How much of a risk does this present? Later in the year we would upgrade test to SQL Server 2000 EE, but for a couple of months the environments would be different.
Thanks, Dave
Changing Linked Server Names Between Environments
May 1, 2007If I have stored procedures that reference linked servers, is there any way to avoid changing the procedures when moving between environments (dev, qa, prod)?
Do i resort to dynamic sql? Use case statements to switch between environments?
Any experience or advice is appreciated.
Integration Services :: Deploying A Package In Different Environments
Apr 23, 2015I modified an existing package created by someone (who left the organization). Its a ftp bulk insert from flat files to the tables. The need was to updates 2 tables out of 9 with additional columns. I made the changes to the package and it runs successfully on my laptop.
After that I build the package and tested it on the development server, although the package runs successfully but the data is written to my DB tables in my laptop instead of development server DB tables!.I see "." in the place of the connection manager server name. The DB name is the same in all the three environment (local, development & production). There is a configuration table used in the package.
Hosting Multiple Environments On One Report Server
Sep 17, 2007
All,
Is there a way to simulate hosting three environments on one Report Server. Due to resource limitations, I would like to set up three environments DEV, TEST, STAGE on one report server such that the data source for each environment would be different. Can I use a different folder structure for each and then deploy the reports by providing server url accordingly?
e.g.
http://servername/DEV_Reports or should it be (http://servername/ReportServer/DEV_Reports)
http://servername/TEST_Reports
http://servername/STAGE_Reports
and then in each of the above define a datasource that has connections to appropriate database on db server. Can we do something like this?
Thanks.
SSIS Behaves Differently In Different Environments Even Though The Code Is Same
Oct 9, 2007Hi,
SSIS is behaving differently in different environments but the code is same.
One thing is nor working correctly that is I am converting a string data type column to float data type in data conversion. In our local environments the package is working fine but in production environments it is not working correclty. It is unable to convert the data it is throwing an error.
"The data value cannot be converted for reasons other than sign mismatch or data overflow"
Can anybody help me please?
Power Pivot :: How To Config Environments In VS Tabular
Jun 21, 2015Is it possible to config environments ( dev,tst,prd ) in  VS Tabular solution , like it is done in SSIS ?  i find Variable tab , but i dont find the way to set a connection (string) to be change dynamically between the environments.
View 4 Replies View RelatedDynamically Changing SQL ConnectionString For Live/test Environments
Sep 11, 2007Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.In my Web.Config file, I have this:<connectionStrings> <add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True" providerName="System.Data.SqlClient" /></connectionStrings>I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.I tried this in my Global.asax, but it told me the value was read_only: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True" Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site? Thanks.
Transact SQL :: Huge Performance Difference For Same Select Between Environments
Jun 22, 2015I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.
This is the selcet statement:
SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')
Multiple Environments And Configuration File Settings In Packages.
Aug 10, 2006
Suppose 2 environments on a single machine.
Each environment has different configuration settings....different
databases etc.
All the packages in the first environment have a hardcoded config files
referencing the local drive.
In order to create the second environment do I have to
go into each package and manually change the location of the
hardcoded config files. If I don't it will it not use the config files
from the first environment.
Thanks
Reporting Services :: SSRS Reports Migration And Management Between Environments
Aug 13, 2015How to manage the progression of SSRS reports from DEV > TEST > USER > PROD...I can see that you COULD manage this all from within VS however then all the control is in the developers hands and even though you can control access with permissions and stuff, its not very user friendly to provide a tester or business area manager or project manager with VS just so they can publish reports as they are approved.Are their any such tools to manage migration between ssrs servers or something that you could have a user test a report and then do something like "approve/promote" it to the next environment, or is this something we would need to create ourselves?
I can see that there are a few things that allow PROJECTS to be migrated (powershell scripts and something about octopus deploy) but these all seem to be too "big" for our organisation - we generally develop reports as a "logical group" (project) but migrate them individually or in VERY small groups (5 is probably the maximum), however keeping tabs on what report is at what version in what environment is causing our DBA massive migranes...Maybe there is something we can do from TFS or perhaps we need to build a utility app?
SQL 2012 :: Full Text Engine Service In Windows Clustered Environments
May 27, 2014In SQL Server 2005, the SQL Server full text engine (i.e., Microsoft Full Text Filter Daemon Launcher windows service) was installed as a Generic Service cluster resource in a Windows clustered environment.
However, in SQL Server 2012, the full text engine is not appearing as a "resource" within "Failover Cluster Manager" after installation. Do we need to manually configure the full text engine as a Failover Cluster Manager "resource" for SQL 2012? Or is this not necessary? If it needs to be configured, how do you go about doing it (i.e., what resource dependencies would you setup, etc.)
SQL Server 2012 :: Stored Proc Execution Time Diff Between Environments
Jul 3, 2015I have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.
I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.
Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.
SSRS Printing Gives Different Page Output In Visual Studio And Deployed Environments.
May 6, 2008
Hi,
I'm having a struggle with the printing of landscape reports in SSRS.
The reports appear fine when printed from the Visual Studio environment, but when I deploy to a web environment I get 1 or 2 blank pages before reporting continues (i'm using the SSRS printer icon, not the browser printer icon).
I've spent ages looking at margin and printer settings and can't find anything wrong - especially as it works under Visual Studio.
Has anyone experienced problems of this nature and how did you resolve them?
Cheers.
Elracorey
Data Source Deployment Best Practises Supporting Development, Test, And Production Environments
Feb 4, 2008We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
Thanks!
Oracle/Unix Environments --&&> SQL Server 2005/Windows 2003 Server
May 8, 2007I was a Oracle Developer / DBA on Unix Environments all along my career, Very recently iam starting to manage a SQL Server 2005/Windows 2003 Server setup.
Part of my new job is to automate to load Huge Data files/Flat Files (3/4 GB in size) into SQL Server 2005 DB.
Have these initial questions..
Since the files are too large to open at once... What sort of Command Line Interfaces people use on the Windows Boxes.. like doing a "wc" (Word Count) / GREP 'ng Files / Massaging Data Files one line at a time (Like using SED / AWK Commands).. Etc
Any Input / Direction is appreciated...