Hi,
We have developed & hosted several reports on Development Server now We want to transfer those reports to production server. Do I need the Reports project in VS2005 or I can trf those reports directly from SQL Server 2005 by takiing backup of report database or by other way.
Please gyude ne. Which way I can proceed.
Nilkanth Desai
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedHi
Can we use BCP AND/OR BULK INSERT to do development server databse refresh from production server . I have to do this by following the rules below:
- no truncation of source table
- update of changed or new records only.
I know we can use replication, dts and other methods for this but I need information about this one.
Thanks
Hi everybody,
Can anyone explain to me, what is 'Test Server','Production Server' and 'Development Server'?.
tks in advance,
vasu.
we have a situation where modifications to the stored procs and views keep happening in the development databse environ while there is a live production database also up and running.
periodically we need to ensure that the development and the production are in synch where all changes made to dev are transferred to prod. however the entire prod database obviously cannot be overwritten as it contains production data. therefore what we need to do is to transfer all SPs and Views from the development to prodn. ( we do not prefer incremental transfers as there are many SPs and views and we can never really be sure that all changes have been transferred )
the obvious way is to script out all SPs and views in dev and run the scripts in prodn. but what we encountered was the enterprise manager does not script out the objects in heirarchical sequence - ie parent objects first and then the objects that refer the parent objects. this leads to errors when running the scripts as the objects required for the creation of a SP, say another SP run from within that SP , may not have been generated by then as the "contained" SP is lower in the alphabetical order in which SQL server scripted out the objects.
finally we had to look at each and every error and manually set the process right.
moreover in the way we did things the "sysdepends" table got all screwed up and therefore we can no longer depend on enterprise manager to show dependencies within the database.
is there any elegant way of going about this ? we also tried transfer manager in DTS but it also gave the same error. moreover can the sysdepends table be rebuilt from scratch ?
I have a student who has a 100GB database that we need to have a R/W copy of in a Development environment.
It would be okay for the development system to have yesterday's data.
What would be the easiest solution for replicating the data.
Replication?
Log Shipping?
Backup/Restore?
Other Ideas?
Thanks in adavnce for the help.
Cheers,
Casey
Hello everyone,
I have a question about how to efficiently develop on a development version of a database and get those changes over to the production database without loosing live data, etc.?
Here is how we are trying it:
1 Export production database to temp database(exact copy)
2 Delete production database
3 Export development database (definition only, no data) to new database with production database's name
4 Export production (temp) database (data only) over to new production shell
We are having truncate errors due to FK constraints.
Is there a better way?
Thanks for all help
Kevin
I have two servers: one production and one development. There is a third party query that runs on both servers. Yes, the query is poorly tuned - I cannot change it. In production, it runs in 54 minutes. In development, I have tried to let it run for days and it never completes.
Here's what I have tried so far:
Compared the settings of production vs. development. The settings are very similar - the development box is larger, 4 times more memory.
Max degree of parallelism is the same on both boxes.
No compression on both boxes.
The production server is fairly busy, the development server is empty - this is the only process running on it.
Plenty of free disk space.
Updated all statistics on all databases touched by the query on dev.
Indexing is the same on both boxes.
The development box is running MSSQL2012.
The production box is running MSSQL2008R2.
What I've noticed:
The query consumes a massive amount of CPU time.
HUGE number of reads (16 million reads for 10 writes according to sp_WhoIsActive)
Largest wait types are CXPACKET, SOS_SCHEDULER_YIELD and TRACEWRITE respectively.
wait_typesum_wait_time_mspct_wait_timesum_waiting_tasksavg_wait_time_ms
CXPACKET4142765580.7176307423.5
SOS_SCHEDULER_YIELD24146944.7532725530.0
TRACEWRITE19856343.914831338.9
Hi everyone,
I really need your help!! How you manage development server and production server?
My current situation:
Our programmers develop new Stored Procedures on development server. When all testing is completed, we then deploy them to production server.
Challenges:
Programmer (Eric) modified a Stored Procedure (SP_1) on development server. He did not deploy it on production server because SP_1 is not completed yet.
Programmer (John) got request and needed to modify SP_1 on development server
So now there is a problem in SP_1. if John deploy SP_1 on production server, it will overwrite the current version and cause errors.
Anyone can solve this problem? Will it be better if we have another testing server?
many thanks
I have a production database with a backup job that creates files with thenaming convention dbname_db_200503291800.bak. I want to schedule a restorejob that will retire yesterdays backup. How can I write my restore statementso that it will specify the backup file with yesterdays date.Thanks
View 1 Replies View RelatedHi all, I've been assigned a task of refreshing data from theproduction env to development env.what i got is a backup file of a db in the prod env, i now need tomake that into the development env.I can restore it to the dev env no problem, but the warnings i got arethat the tables owner in prod and dev env need to be different, thatis, owner is A in prod env and owner is B in dev env.So I need to:1) restore the db in dev env2) change table owner from A to B3) change related triggers4) change related viewsCan anyone suggest me an approach that is most efficient?Thanks a lot.
View 2 Replies View RelatedHello All,I have been searching for a published document for Best Practicesconcerning access levels based on roles. Should developers have morethan (if at all) select level access to production data? If Iunderstand (from multiple postings) that it is best to have:1. Development (developers have extensive access levels)2. Test (developers have restriced access levels)and3. Production (developers have none or select level access)Our environment and budget only allows for items 1 and 3.If any body could point me to a document from a 'reputable' source, Iwould greatly appreciate it.TIABill
View 3 Replies View RelatedWhat's the best way to move database from development to production environment?
View 1 Replies View Related
We have a system here where we develop SSIS packages on a development server. I am trying to figure out the cleanest way to promote these changes to a production server where stored procedures/tables that are used in the package are not deployed yet.
When I switch the connection in the package to the production server there are alot of objects in the package that are "invalidated" because they are trying to verify existence of tables/columns. One example is outputing the results of a query to a text file. The text file destination gets a red X on it because it cant grab the columns from the source query (because that stored procedure doesnt exist yet)
Is there a best practices or something on how to deploy packages to a production system? I have tried turning off "ValidateExternalMetaData" with no success.
Thanks!
1.) Can an aspnetdb.mdf database be configured and setup on one server and then be moved to a production server or is there something machine specific that keeps this from being possible? 2.) Is putting this file in the app_data folder something that is used only for SQL 2005 or SQL express? I had to set up a connection string in my SQL 2000 installation to get the connection to work.
Thanks for any input!
Colelaus
This is driving me nuts: On my development machine the code runs fine
but generates an error on the production server. Both are running SQL
Server 2000 and ASP.NET 1.1
The datatype of the field in question is datetime.
The webform has a calendar for a user to select and automatically
insert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@citation_date", CDate(txtDate.Text))
This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.
WHY?
Hi,
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
I have been looking for some documentation that would support or rejectmy opinion on Production -vs- Development naming conventions. Ibelieve that each environment should be housed on separate servers withidentical names, access, users, stored procs....... If you eitheragree or disagree with this methodology, I would appreciate your input.TIA,Bill
View 5 Replies View RelatedHello,
I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.
Any help would be appreciated,
David
I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.
I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.
Any reason why? I've never seen this happen.
Here's the code. Thanks in advance. Protected Sub BtnUpload2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpload2.Click
UploadTextDocument()
End Sub
Private Sub UploadTextDocument()
Dim location As String = FileUpload1.PostedFile.FileName.ToString
' Connection String to Excel Workbook
Try
Dim excelConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)
' Create Connection to Excel Workbook
Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(excelConnectionString)
Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection)
connection.Open()
' Create DbDataReader to Data Worksheet
Using dr As Data.Common.DbDataReader = command.ExecuteReader()
' SQL Server Connection String
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Bulk Copy to SQL Server
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "ExcelData"
bulkCopy.WriteToServer(dr)
End Using
End Using
connection.Close()
End Using
LBError.Text = "The spreadsheet was successfully uploaded."
Catch
LBError.Text = "There was an error. Check the spreadsheet for correct format."
End Try
End Sub
We 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!
Hi fellows,
I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.
can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.
This is Sql server 2000 Cluster environment.
thanks in advance for any help
rahman
Can someone provide a step by step tutorial for this? I'd like to safely update a database that is used for a website without much or any downtime.
View 1 Replies View RelatedHi all Pros I am transfering from windows2003 server and MS SQL 2000 to Windows Vista and SQL Express ...... Before I allways created a database manually and I could allways use as many databases I needed so in that way I could have several versions of a database for a certain software during development. Can you use separeted databasenames when developing in SQL Express? I am using a dotnetnuke installation file and inside the project there is a file /App_Data/Database.mdf should this file be renamed everytime I make a new testinstallation or setting up a separate dev environment /Johan
[Moderator Edit: unrelated links removed from post body.]
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1.
Both has SQL server 2005 running and has visual studio 2005
Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server.
Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this.
Thanks
George
Hi Everybody,
I have made one simple application in asp.net 2.0 using VS 2005. I got following error
Server Error in '/[applicationname]' Application. --------------------------------------------------------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
- When i got on my local machine i solved it by deleting sqlexpress directory from user's account and it was due to that I have installed VS 2005 on machine and by default it installs sql express with it.
- But I could not solved this error on my Development Server wherein there is no VS 2005. Only .Net framework 2.0 & 1.1 are there. My all other applications are running well.
So if any one have idea regarding the solution for this error then please reply me soon. It is very urgent.
Thanks to all of you.
Regards, Hardik B.
Hi,
Im fairly new to this so apologies if Ive put this in the wrong place.
I have just rented out server for a new asp.net site. Ive been developing the site on my local machine and the database in Sql Enterprise. Ive recently put a copy of the site on the live server and got everything configured.
However everytime I make a change to my local database, or add values into my look up tables I have to go do the same on the live server and its proving a bit of a pain in the ass, so Im guessing there has got to be a much smarter way of doing this. I dont want (if possible) to open up the live sql server to allow remote connections, but if thats what neccesary so be it.
So my question is basically what are my options for keeping my live server up to date with my development server. As I add new features on my local server, should i be saving sql scripts of all the changes I make?
All opinions much appreciated.
C
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedI 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 RelatedAny help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies View Related
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:MSSQLBACKUPDBPRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ????
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Hi,
I would like advice regarding what hardware to use for a SQL Server 2005 for use in a development environment. I am the only developer and user of the database.
I develop at home, and currently use one PC, to develop the software. My PC is Athlon XP 4000, 2 Gig RAM, 1 x 160gb HD . I am developing an asp.net website along with a variety of data interface applications in C# - all on top of SQL Server 2005. This environment works/worked fine until I started dealing with a lot more data.
I now have 2 databases, each about 30 gig in size (with most of the data in a a couple of tables in each database). When I am importing data or running the website or doing intensive queries or doing sql updates that change millions of rows, I am understandably hitting performance problems. I have had to throttle SQL Server 2005 to only use 400mb, as I have so many other development applications that are open...... Everything is slowing to a halt, and I dont think SQL Server is very happy.
So, I am looking to build or buy a cheap machine to run SQL Server 2005, and take the load off my current PC. However I only have a budget of about £300/$560 - maybe a bit more at a push (I could possibly upgrade in the future).
I could do something like below, if I am going to buy a machine
Acer T180
Sempron 3200
512mb (i would upgrade to 2GB)
80gb hard disk
Or I could build my own? If I do, what should I skimp on and where should I invest more. I.e. should I get the cheapest processor possible, and get a decent harddrive or 2? Should I raid 0 a couple of small drives? Or have one drive for data and one for the transaction logs? Or should I have one drive, and spend more on RAM?
Any help would be greatly appreciated....
Andrew